Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Need way to test if value is numeric...

    I'm relatively new to Oracle so I'm sure that there must be a better or more efficient way to to do this but...

    I need a way to test if a value is numeric in 9i.

    I wrote the following function, but it won't even compile.

    function isNumber(str IN VARCHAR2)
    return number is
    isNumeric number(10);
    begin
    isNumeric := to_number(str);
    return 1;
    exception when others THEN
    return 0;
    end isNumber;

    Any help would be appreciated.

    Thank you.

  2. #2
    Join Date
    May 2006
    Posts
    132
    This is actually an affective method to test if a value is numeric.

    What error are you getting? I don't see anything wrong with your function.

  3. #3
    Join Date
    May 2004
    Posts
    184
    ERROR at line 2:
    ORA-06550: line 2, column 1:
    PLS-00201: identifier 'ISNUMERIC' must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 3, column 1:
    PLS-00372: In a procedure, RETURN statement cannot contain an expression
    ORA-06550: line 3, column 1:
    PL/SQL: Statement ignored
    ORA-06550: line 5, column 1:
    PLS-00372: In a procedure, RETURN statement cannot contain an expression
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored

  4. #4
    Join Date
    May 2006
    Posts
    132
    Looks like you are just missing the CREATE statement in front of the FUNCTION word.

    Code:
    create function isNumber(str IN VARCHAR2)
    return number is
    isNumeric number(10);
    begin
    isNumeric := to_number(str);
    return 1;
    exception when others THEN
    return 0;
    end isNumber;
    /

  5. #5
    Join Date
    May 2004
    Posts
    184
    Duh. You are so right. Thank you!

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You might make the variable "isnumeric" NUMBER instead of NUMBER(10), just in case a valid number with a value of 1e10 or greater happens to be passed in:

    Code:
    SQL> var num number
    SQL> exec :num := 1e10
    
    PL/SQL procedure successfully completed.
    
           NUM
    ----------
    1.0000E+10
    
    SQL> SELECT isnumber(:num) FROM dual;
    
    ISNUMBER(:NUM)
    --------------
                 0
    
    1 row selected.
    
    SQL> SELECT isnumber(:num -1) FROM dual;
    
    ISNUMBER(:NUM-1)
    ----------------
                   1
    
    1 row selected.
    btw if it was my code I would call it something like "v_numeric" because "isnumeric" sounds to me like a Boolean. It's up to you of course.

  7. #7
    Join Date
    May 2006
    Posts
    132
    Good point William.

  8. #8
    Join Date
    May 2004
    Posts
    184
    Thank you all for all of your suggestions and help, it works like a charm.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •