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


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