DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Validating Variable Datatype

  1. #1
    Join Date
    Mar 2002
    Posts
    25

    Question

    I am trying to check the datatype of a variable, it is usually passed in a Varchar2, buthow do I check (if its a Number OR Date Or String).
    But I want to validate the content of the variable (IS Date, IS Number or IS String -- returning Boolean) before moving next to the next logic.

    This how is done in VB & SQL Server

    IF IsNumber(v_var) <> 1 THEN
    -- True
    Do something
    Else
    --False
    Do something else
    End IF

    OR
    IF IsDate(v_var) <> 1 THEN
    -- True
    Do something
    Else
    --False
    Do something else
    End IF



    How can I do this validation check in Oracle PL/SQL?

    Suggestions?


  2. #2
    Join Date
    Dec 2001
    Posts
    221
    in pl/sql you have to declare a variable. without declaring you cannot use that variable. and ofcourse you have to use the datatypes while declaring. may be it oracle datatype or user defined.
    Santosh Jadhav
    8i OCP DBA

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    create or replace function is_number(p_vc varchar2) return boolean is
    n number;
    begin
    n := to_number(p_vc);
    return true;
    exception when others then return false;
    end;
    /

    create or replace function is_date(p_vc varchar2, p_format default 'mm/dd/yyy')
    return boolean is
    d number;
    begin
    d := to_date(p_vc, p_format);
    return true;
    exception when others then return false;
    end;
    /

    ---------------------------------------------
    In program code u can write -->
    ---------------------------------------------
    if is_number(v_char) then
    ....
    elsif is_date(v_char, 'dd-mon-yyyy') then
    ...
    else

    end if;




Posting Permissions

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


Click Here to Expand Forum to Full Width