Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    30

    Unanswered: Procedure to check for string not number

    What is the correct syntax to be contained in a procedure that checks if an entry is string, not number?
    I've tried the following but it is obviously wrong:

    IF pUserName NOT IN VARCHAR THEN
    RAISE INCORRECT_DATA;
    END IF;

    Thanks for any replies.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    I think the best way would be to try to convert it to a number in a block, and then handle the exception like so:
    Code:
    DECLARE
    v_user number;
    
    BEGIN
    v_user := to_number(p_user_name);
    
    EXCEPTION
    when others then raise incorrect_data;
    If you try this, only numbers will convert to numbers. Anything containing a character will raise a conversion to character error.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > if an entry is string, not number?
    define "entry"
    define "string"
    Neither of these are Oracle datatypes.
    what should be done with non-numeric & non-alphabetic characters?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Posts
    492

    Wink

    Quote Originally Posted by anacedent
    > if an entry is string, not number?
    define "entry"
    define "string"
    Neither of these are Oracle datatypes.
    what should be done with non-numeric & non-alphabetic characters?
    Define "string" - String is commonly agreed to mean a character string. Now you are just being really picky. And since he already mentioned he is using a procedure and even gave a parameter name (pUserName), you can put 2 and 2 together and guess "entry" is the parameter being passed in.

    I feel sorry for anybody who needs your approval on documentation - they will be working for weeks because you get off on nitpicking
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Nov 2004
    Posts
    7
    I posted the first message in a bit of a rush.Let me explain:The "entry" in my original post is a username coming from a java input box which needs to be validated to make sure it is indeed text (It's a stored procedure I am using).
    This username is the parameter being passed in.
    I will try ss659's suggestion (thanks for that) but I am surprised there is not an easier way of doing it.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Well with pl/sql no theres not - maybe javascript or something could do it easier. Check out this link from the OTN forums, and you will see some different solutions.

    http://forums.oracle.com/forums/thre...&thread=279809

    I think the best way would be to make it a Boolean function as suggested in the thread - if you can convert the string into a number using to_number, its all numbers. You could then return it false b/c that does not meet your validation needs. In your exception handler, return it true, because you cannot convert a string to a number.

    I think my first post was backwards but it is still the way you should do it.
    Last edited by ss659; 02-25-05 at 16:37.
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Feb 2005
    Posts
    30
    The post by 'catalyst' is actually by me ('alonzo').
    Got logged in as someone else,long story,sorry.
    Will check out that link,thanks.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I use a function to determine this ...

    create or replace function IS_NUMERIC(ps_value in varchar2) return number is
    Result number;
    begin

    Result := to_number(ps_value);
    return(Result);

    exception
    when invalid_number then
    Result := NULL;
    when others then
    Result := NULL;

    return(Result);

    end IS_NUMERIC;
    /

    HTH
    Gregg

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by catalyst
    The "entry" in my original post is a username coming from a java input box which needs to be validated to make sure it is indeed text (It's a stored procedure I am using).
    This doesn't look like a good design decision... User input validation should be performed as close to the user as possible, unless it indeed requires a database lookup. User interface tools (be it Javascript, Java, perl, PHP) are better suited for that kind of validation, the response will be faster, and the database will not be spending precious CPU cycles doing something it's not intended for...

Posting Permissions

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