Re: exception free to_number() subsitute?

Allard Siemelink <aj_at_knoware.nl> wrote in article <aj.32.0009BCEC_at_knoware.nl>...
> Within a select which returns multiple rows, I need to do a conversion
 from a
> varchar2 field to a number. However, it is essential that the select
 will not
> break when the varchar2 happens to *not* contain a valid number.
> Basically, what I need is a to_number() substitute that will return a
 NULL when
> the string cannot be converted. Also, it needs to be *fast*.
> Any suggestions? (short of writing a dedicated function that will parse
 the
> varchar2 field character by character)

Write a stored PL/SQL function that calls an external C function. How you write it depends on your platform. For an example of how to do this, take a look at function unique_session_name in package dbms_pipe. On NT, it contains a single statement:
  pragma restrict_references(unique_session_name,WNDS,RNDS,WNPS); On UNIX, function unique_session_name references function unique_session_id in package dbms_session, which calls a local function psduis which has a single statement:
  pragma interface (C, psduis); -- see psdicd.c

Then write a good C function using strtod() not atof(); the former has better parsing and recovery handling. You'll have to read up on interfacing user-supplied C functions to PL/SQL stored subprograms.

I tried once to write a stored PL/SQL function str_to_number() which would handle the exception raised by to_number() when the value was an alpha and return NULL, but SQL complained that the function might have side effects and therefore was not valid to call in that context. I removed the exception handler and SQL could call the function -- but now it operated just like to_number() and puked when it hit the alphanumeric value (terminating abruptly the SQL processing).

Maybe someone out there has experience writing C interface routines...

• Dan Clamage dclamage AT idcomm DOT com