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

[nabh4u]

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.

[debasisdas]

Try to use NVL function. else convert to number using TO_NUMBER.

[nabh4u]

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.

[debasisdas]

if using PLSQL then why not check the value using IF or CASE. You can use CASE in SQL also..

[r035198x]

decode should work then. How did you use it when you got the error?

[subashsavji]

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.......

[nabh4u]

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.

[nabh4u]

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.

[r035198x]

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?

[amitpatel66]

Yes REPLACE should work.
CHeck sample code below:

 
SQL> SET SERVEROUTPUT ON
SQL> declare
  2  a number;
  3  begin
  4  a:= REPLACE(SUBSTR('abcd                  ',6),CHR(32),0);
  5  dbms_output.put_line(a);
  6  end;
  7  /
 
 
PL/SQL procedure successfully completed.
 
SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  a number;
  3  begin
  4  a:= REPLACE(SUBSTR('abc  d',4,2),CHR(32),0);
  5  dbms_output.put_line(a);
  6* end;
SQL> /
 
 
PL/SQL procedure successfully completed.
 
SQL> 
 
SQL> 
 

[nabh4u]

Yes REPLACE should work.
CHeck sample code below:

 
SQL> SET SERVEROUTPUT ON
SQL> declare
  2  a number;
  3  begin
  4  a:= REPLACE(SUBSTR('abcd                  ',6),CHR(32),0);
  5  dbms_output.put_line(a);
  6  end;
  7  /
 
 
PL/SQL procedure successfully completed.
 
SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  a number;
  3  begin
  4  a:= REPLACE(SUBSTR('abc  d',4,2),CHR(32),0);
  5  dbms_output.put_line(a);
  6* end;
SQL> /
 
 
PL/SQL procedure successfully completed.
 
SQL> 
 
SQL> 
 

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.

[amitpatel66]

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:

 
SQL> select REPLACE(SUBSTR('abcaad',4,2),CHR(32),0) from dual
  2  /
 
RE
--
aa
 
SQL> 
 
 

[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:

 
SQL> select REPLACE(SUBSTR('abcaad',4,2),CHR(32),0) from dual
  2  /
 
RE
--
aa
 
SQL> 
 
 

Thank you all very much. I think REPLACE is the one I was looking for.


thanks,
Nabh4u.