Re: exception free to_number() subsitute?

Well, Mike, it looks like I owe you an apology. Yes, the function you supplied works correctly: CREATE OR REPLACE FUNCTION chr2nbr (colchr in VARCHAR2) RETURN NUMBER IS
  colnbr NUMBER;
BEGIN
    SELECT TO_NUMBER(colchr) INTO colnbr FROM dual; -- SQL version of function

    RETURN colnbr;
EXCEPTION
WHEN invalid_number THEN
  RETURN NULL;
END;
/

I then tried some variations:
CREATE OR REPLACE FUNCTION chr2nbr (colchr in VARCHAR2) RETURN NUMBER IS
  colnbr NUMBER;
BEGIN
    colnbr := TO_NUMBER(colchr); -- using the PL/SQL version of the same function

    RETURN colnbr;
EXCEPTION
WHEN invalid_number THEN
  RETURN (NULL);
END chr2nbr;
/

And got ORA-06502 PL/SQL numeric or value error, so I changed the exception handler to WHEN OTHERS instead of WHEN INVALID_NUMBER (ORA-01722) and it worked.

Further simplified it by tossing the colnbr variable and did the conversion in the RETURN statement:

CREATE OR REPLACE FUNCTION chr2nbr (colchr in VARCHAR2) RETURN NUMBER IS
BEGIN
  RETURN(TO_NUMBER(colchr));
EXCEPTION
WHEN OTHERS THEN
  RETURN (NULL);
END chr2nbr;
/

This works as well.

The problem I was reporting occurs when you try to stick this function inside a package. Then when you try to use it:   SELECT some_pkg.chr2nbr('20') FROM DUAL; you get ORA-06571 Function chr2nbr does not guarantee not to update database.
As long as the function above remains as a standalone stored function it works fine in SQL and PL/SQL statements.

• Dan Clamage dclamage_at_idcomm.com----------