character to number conversion problem? 2

[SashaBuilder3]

Hi everybody,

I have a PL/SQL procedure which reads values from a text file. Each read value (either string or numeric) first comes into a temporary VARCHAR2 variable named tmpValue. Some numeric values in the file can be -99 (meaning missing) which in the database should be assigned NULL.

So I put a simple statement:

if tmpValue = -99 then
tmpValue := NULL;
end if;


The whole procedure compiles OK but when I'm trying to run it in SQL*Plus I'm getting a message:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I also tried to change the first line into this

if TO_NUMBER(tmpValue) = -99 then

but it didn't work either (same error message).

Can anyone help with a correct syntax?



Thanks,

Alexandre

 

[rcurva]

How about this?

if tmpValue = '-99' then
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[fmorel]

The problem seems that tmpValue can contain something else that numbers, for example spaces.

If you are sure that it can only contain number or spaces, you may test:

if trim(tmpValue)=-99
--> there is an implicit type conversion.

Anyway,
if trim(tmpValue)='-99'
is safer.

 

[SashaBuilder3]

Robbie, Fmorel,


Thank you! Putting apostrophes made it work. Definitely the strings can contain spaces so I used the Trim() function as well.


Alexandre