Q
Problem solve Get help with specific problems with your technologies, process and projects.

Character to number conversion error

I have a table called test with the columns formula,code1,code2,code3,code4, code5, with one record:


formula code1 code2 code3 code4 code5
------- ----- ----- ----- ----- -----
DA      (      100   +     2     )
I have a PL/SQL statement for computing the above expression as follows:

declare
f1 varchar(20);
c1 varchar(20);
c2 varchar(20);
c3 varchar(20);
c4 varchar(20);
c5 varchar(20);
f varchar(30);
b1 number;
b2 number;
b3 number;
b4 number;
b5 number;
res number;
begin
 select formula,code1,code2,code3,code4,code5
 into f1,c1,c2,c3,c4,c5  from test where
 formula ='DA';
	if c1 = '(' or c1 = ')' or c1 = '+' then
		f:=c1;
	else
		b1:=to_number(c1);
                f:=b1;
	end if;
        if c2 <> '(' or c2 <> ')' or c2 <> '+' then
		b2:=to_number(c2);
		f:=f||b2;
	else
		f:=f||c2;
	end if;
       if c3 = '(' or c3 = ')' or c3 = '+'  then
		f:=f||c3;
	else
		b3:=to_number(c3);
		f:=f||b3;
	end if;
       if c4 <> '(' or c4 <> ')'  or c4 <> '+' then
		b4:=to_number(c4);
		f:=f||b4;
	else
		f:=f||c4;
	end if;
       if c5 = '(' or c5 = ')' or c5 = '+' then
		f:=f||c5;
	else
		b5:=to_number(c5);   
		f:=f||b5;
	end if;
  select  f into res from dual;
 dbms_output.put_line(res);
end;
/
I get the following error:
numeric or value error: character to number conversion error
Please give me a solution for this problem.

The reason you're getting a numeric/value error is that you're trying to convert a character to a number. For example: your code has b1 := to_number(c1); . If c1 is a '(', you'll get this error. You can't convert a true character to a number. Simply add logic to your code to only convert numbers, not the mathematical symbols and operators.

For More Information


This was last published in December 2002

Dig Deeper on Oracle error messages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close