Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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