469,356 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

help with PL/SQL (ORA-06502 & PLS-00204)

nabh4u
62
Hello,
I am having two tables, 1st has just one column with 80 bytes and the 2nd table has many columns. I use substr function to get the specific value from the first table and insert it into the second table using PL/SQL. My problem here is, in the first table I might have blanks for some values which I have to insert into the second table fields defined as Number data type. Oracle treats blanks as a string and when i try to insert i get the following error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I tried to use DECODE but I cannot in the PL/SQL assignment statement. gives me the following error:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only

I cannot write select statements as I have many fields defined as number type. Is there any other alternative which i can use to check if the incoming value is blanks then replace it with 0?

Any advise is welcome.

Thanks,
Nabh4u.
Jun 25 '08 #1
12 8999
debasisdas
8,127 Expert 4TB
Try to use NVL function. else convert to number using TO_NUMBER.
Jun 25 '08 #2
nabh4u
62
Try to use NVL function. else convert to number using TO_NUMBER.
hi Debasisdas,

Thanks for a quick reply.

I cannot use NVL as the incoming value is not considered as null, instead it is considered as a string of blanks.

I also cannot use TO_NUMBER because what it does is converts a string into a number. If the string is blanks then what should be the conversion?

What I know about TO_NUMBER is it converts a string into a number.
Eg: TO_NUMBER('123.25') will give 123.25

But, if the string is ' ' then what should be the result of TO_NUMBER(' ') ?

May be oracle has something else to use.

Thanks,
Nabh4u.
Jun 25 '08 #3
debasisdas
8,127 Expert 4TB
if using PLSQL then why not check the value using IF or CASE. You can use CASE in SQL also..
Jun 26 '08 #4
r035198x
13,262 8TB
decode should work then. How did you use it when you got the error?
Jun 26 '08 #5
decode should work then. How did you use it when you got the error?
may be this is helpful .. how to use decode in plsql by using sql only

cursor c1 is
SELECT LORRY_NO, TCS_NO,tcs_date,branch_branch_code
FROM CT_TCS where ac_year_code=:ac_year_code
tcs_date between :vtcs1 and :vtcs2
and branch_branch_code=:vbran
and lorry_no=decode(:lry,'ALL',lorry_no,:lry)
ORDER BY LORRY_NO,TCS_NO
begin
open c1
fetch c1.......
Jun 26 '08 #6
nabh4u
62
if using PLSQL then why not check the value using IF or CASE. You can use CASE in SQL also..
The problem is I have many fields like that and if I write IF or CASE for all of them then it will be a big burden. I am looking for something which is simple and easy.
Jun 26 '08 #7
nabh4u
62
decode should work then. How did you use it when you got the error?
hi r035198x,

Decode will only work when you use it with a sql statement. Looks like if we use decode while assigning something then it wont work.

Eg:- abc := decode(xyz,null,0,xyz);

The problem is I have a lot of fields like that and if I use sql statements for all of them then it will be a big burden and a huge procedure.


Thanks,
Nabh4u.
Jun 26 '08 #8
r035198x
13,262 8TB
The problem is I have many fields like that and if I write IF or CASE for all of them then it will be a big burden. I am looking for something which is simple and easy.
... and about the decode?

EDIT:

How about the REPLACE function then?
Jun 26 '08 #9
amitpatel66
2,367 Expert 2GB
Yes REPLACE should work.
CHeck sample code below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3. SQL> declare
  4.   2  a number;
  5.   3  begin
  6.   4  a:= REPLACE(SUBSTR('abcd                  ',6),CHR(32),0);
  7.   5  dbms_output.put_line(a);
  8.   6  end;
  9.   7  /
  10.  
  11.  
  12. PL/SQL procedure successfully completed.
  13.  
  14. SQL> ed
  15. Wrote file afiedt.buf
  16.  
  17.   1  declare
  18.   2  a number;
  19.   3  begin
  20.   4  a:= REPLACE(SUBSTR('abc  d',4,2),CHR(32),0);
  21.   5  dbms_output.put_line(a);
  22.   6* end;
  23. SQL> /
  24.  
  25.  
  26. PL/SQL procedure successfully completed.
  27.  
  28. SQL> 
  29.  
  30. SQL> 
  31.  
Jun 26 '08 #10
nabh4u
62
Yes REPLACE should work.
CHeck sample code below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3. SQL> declare
  4.   2  a number;
  5.   3  begin
  6.   4  a:= REPLACE(SUBSTR('abcd                  ',6),CHR(32),0);
  7.   5  dbms_output.put_line(a);
  8.   6  end;
  9.   7  /
  10.  
  11.  
  12. PL/SQL procedure successfully completed.
  13.  
  14. SQL> ed
  15. Wrote file afiedt.buf
  16.  
  17.   1  declare
  18.   2  a number;
  19.   3  begin
  20.   4  a:= REPLACE(SUBSTR('abc  d',4,2),CHR(32),0);
  21.   5  dbms_output.put_line(a);
  22.   6* end;
  23. SQL> /
  24.  
  25.  
  26. PL/SQL procedure successfully completed.
  27.  
  28. SQL> 
  29.  
  30. SQL> 
  31.  
hi Amit,

Can you tell me what does CHR(32) mean in your code? Also, If suppose the incoming value i.e., if the substring has proper value then will it get the value?

For Example:

a:= REPLACE(SUBSTR('abc d',4,2),CHR(32),0); will return 0.

but if a:= REPLACE(SUBSTR('abcaad',4,2),CHR(32),0); then will it return 'aa'?

Thank you,
Nabh4u.
Jun 26 '08 #11
amitpatel66
2,367 Expert 2GB
hi Amit,

Can you tell me what does CHR(32) mean in your code? Also, If suppose the incoming value i.e., if the substring has proper value then will it get the value?

For Example:

a:= REPLACE(SUBSTR('abc d',4,2),CHR(32),0); will return 0.

but if a:= REPLACE(SUBSTR('abcaad',4,2),CHR(32),0); then will it return 'aa'?

Thank you,
Nabh4u.
CHR(32) is nothing but a ' ' (a single space). 32 is asciivalue for a space.

Yes definately. A simple SELECT statement would have got you answer:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select REPLACE(SUBSTR('abcaad',4,2),CHR(32),0) from dual
  3.   2  /
  4.  
  5. RE
  6. --
  7. aa
  8.  
  9. SQL> 
  10.  
  11.  
Jun 26 '08 #12
nabh4u
62
CHR(32) is nothing but a ' ' (a single space). 32 is asciivalue for a space.

Yes definately. A simple SELECT statement would have got you answer:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select REPLACE(SUBSTR('abcaad',4,2),CHR(32),0) from dual
  3.   2  /
  4.  
  5. RE
  6. --
  7. aa
  8.  
  9. SQL> 
  10.  
  11.  
Thank you all very much. I think REPLACE is the one I was looking for.


thanks,
Nabh4u.
Jun 26 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Adam Ruth | last post: by
2 posts views Thread by Gordon Keeler | last post: by
reply views Thread by basmgokul | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.