title: Simple PL/SQL function to test if a string is a number
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>declare
    str varchar2(1);
    n boolean;
  begin
    str := '0';
    select ca_stage.is_number(str) into n from dual;
  end;
  </code></pre>
  
  <p>In SQL Developer, trying to run this gives me the following error report:</p>
  
  <pre><code>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:
  </code></pre>
  
  <p>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.</p>
  
  <p>As I said, this is basic code, so I assume that I'm missing something fundamental. </p>