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     )

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;
/

numeric or value error: character to number conversion error

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.