Simple PL/SQL function to test if a string is a number

Question

I'm experienced with T-SQL from SQL Server, but have recently begun working on a project utilizing an Oracle database (11g) and am having some issues writing what seems to be basic code.

I need to test whether a set of values are numeric, and only insert them into a table if they are. PL/SQL doesn't seem to have an is_number function, so I wrote my own based on an AskTom question.

create or replace 
function          IS_NUMBER(str in varchar2) return boolean
IS
  n number;
BEGIN
  select to_number(str) into n from dual;
  return (true);
EXCEPTION WHEN OTHERS THEN
  return (false);
END;

Eventually, I'd like to use this function in a WHERE clause, but for now I'm just trying to get it to run at all:

declare
  str varchar2(1);
  n boolean;
begin
  str := '0';
  select ca_stage.is_number(str) into n from dual;
end;

In SQL Developer, trying to run this gives me the following error report:

Error report:
ORA-06550: line 6, column 39:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 19:
PLS-00382: expression is of wrong type
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The error report is straight-forward, but doesn't make sense. The function accepts a varchar2, and that's what I'm using as an input variable. It returns a boolean and again, that's what I'm using.

As I said, this is basic code, so I assume that I'm missing something fundamental.

Answer 1

Return a SQL datatype, e.g. VARCHAR2. Also, I'd recommend against using WHEN OTHERS. Also, you don't need a query on dual:

create or replace 
function IS_NUMBER(str in varchar2) return varchar2
IS
  n number;
BEGIN
  n := to_number(str);
  return 'Y';
EXCEPTION WHEN VALUE_ERROR THEN
  return 'N';
END;