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 errorPlease 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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.