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

On Mon, 14 May 2001 15:07:12 +0200, John van Eck <JvanEck_at_ssc.dordrecht.nl> wrote:

>Try t_line := lpad(to_char(t_getal),8,'0') for your first example and for the
>second example consider that spaces are something different than the null value in
>Oracle, you might wanna check the SQL*Plus manual, because this is quite basic
>stuff.

Yup. But I'll add the following.

>> Example 2
>> ---------
>> t_line := ' ';
>> t_getal := nvl(to_number(t_line),0); -- hier zou uit
>> moeten komen het getal 0
>>
>> PL/SQL: numeric or value error: character to number conversion error
>>

If you wanna check if t_line is null or spaces and replace those by 0, then it could read something like this:

if (nvl(t_line,' ') = ' ') then

   t_line:='0';
end if;
t_getal := to_number(t_line);

That'll just about cover all the bases other than t_line being an alphanumeric.

But the *REALLY* sneaky way is to use in-line exceptions. They are indeed very fast, I timed them in a range of different machines and they are consistently faster than a complex combination of if statements and function calls and definitely worth exploring.

Inline this code:

begin
  t_getal := to_number(nvl(t_line,'0')); exception
when others then
  <do your checks, set flag to get out or just force 0>; end;

This will execute very fast assuming the vast majority of t_line are correct or null and will bump into exception only on the few that it doesn't like. In the exception code, you can trap/fix without incurring the overhead of lots of checks for every value of t_line. Ie, assume it is correct before you go checking/fixing and use the exception mechanism to detect the errors for you.

Thanks to John Bentley for the idea. Older than computers, but still valid! :-)

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Mon May 14 2001 - 08:58:35 CDT