1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to do number validation and date validation in oracle

Discussion in 'SQL PL/SQL' started by krithika@2001, Jul 31, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    hi

    1.for example i need to check whether the date is valid

    2.check whether the field is numeric or not

    Regards
    krithika
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Not clear what do you mean by Date validation . Better you keep the date in Date/Timespamp field in Oracle.

    For second question, there is no direct ISNUMBER function in Oracle. Anyway you can do this by a trick.

    Code (SQL):

    SQL> WITH REC AS ( SELECT &val X FROM DUAL)
      2  SELECT DECODE(TRANSLATE(X,'1234567890','0000000000'),
      3                LPAD('0',LENGTH(X),'0'),
      4                'NUMBER','NOT NUMBER') FLAG
      5  FROM REC;
    Enter VALUE FOR val: 'xxx123'
    OLD   1: WITH REC AS ( SELECT &val X FROM DUAL)
    NEW   1: WITH REC AS ( SELECT 'xxx123' X FROM DUAL)

    FLAG
    ----------
    NOT NUMBER

    SQL> /
    Enter VALUE FOR val: '123456'
    OLD   1: WITH REC AS ( SELECT &val X FROM DUAL)
    NEW   1: WITH REC AS ( SELECT '123456' X FROM DUAL)

    FLAG
    ------
    NUMBER

    SQL> /
    Enter VALUE FOR val: '$%^123'
    OLD   1: WITH REC AS ( SELECT &val X FROM DUAL)
    NEW   1: WITH REC AS ( SELECT '$%^123' X FROM DUAL)

    FLAG
    ----------
    NOT NUMBER

    SQL>
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And that is an admirable suggestion however it does no good for data being entered into an application interface. Take, for example:

    Code (Text):
    SQL> alter session set nls_date_Format = 'DD-MON-RR';

    Session altered.

    SQL>
    SQL> select empno, ename, hiredate
      2  from emp
      3  where hiredate <= '17-NOV-81'
      4  /

         EMPNO ENAME      HIREDATE
    ---------- ---------- ---------
          7369 SMITH      17-DEC-80
          7499 ALLEN      20-FEB-81
          7521 WARD       22-FEB-81
          7566 JONES      02-APR-81
          7654 MARTIN     28-SEP-81
          7698 BLAKE      01-MAY-81
          7782 CLARK      09-JUN-81
          7839 KING       17-NOV-81
          7844 TURNER     08-SEP-81

    9 rows selected.

    SQL>
    SQL> alter session set nls_date_format = 'MON-DD-YYYY';

    Session altered.

    SQL>
    SQL> select empno, ename, hiredate
      2  from emp
      3  where hiredate <= '17-NOV-81'
      4  /
    where hiredate <= '17-NOV-81'
                      *
    ERROR at line 3:
    ORA-01843: not a valid month


    SQL>
     
    Suddenly the supplied string can't be converted (implicitly) because the NLS_DATE_FORMAT changed. This is the problem described in the original post: how to validate that the supplied date string will correctly convert to a DATE value. One could check for conversion errors such as ORA-01841, ORA-01843, ORA-01863, ORA-01864 or ORA-01876:
    Code (Text):

    SQL> declare
      2          cursor get_emp_info is
      3          select empno, ename, hiredate
      4          from emp
      5          where hiredate <= '17-NOV-81';
      6
      7          bad_mth exception;
      8          pragma exception_init(bad_mth, -1843);
      9          bad_yr  exception;
     10          pragma exception_init(bad_yr, -1841);
     11          unsupp_yr       exception;
     12          pragma exception_init(unsupp_yr, -1863);
     13          out_rng exception;
     14          pragma exception_init(out_rng, -1864);
     15          bad_era exception;
     16          pragma exception_init(bad_era, -1865);
     17  begin
     18          for i in get_emp_info loop
     19                  dbms_output.put_line(i.empno||' '||i.ename||' '||i.hiredate);
     20          end loop;
     21  exception
     22          when bad_mth then
     23                  raise_application_error(-20901, 'Month not between 1 and 12', true);
     24          when bad_yr then
     25                  raise_application_error(-20902, 'Year out of range', true);
     26          when unsupp_yr then
     27                  raise_application_error(-20903, 'Unsupported year', true);
     28          when out_rng then
     29                  raise_application_error(-20904, 'Date out of range', true);
     30          when bad_era then
     31                  raise_application_error(-20905, 'Unknown era', true);
     32          when others then
     33                  -- Output desired error message
     34                  dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
     35                  -- Output actual line number of error source
     36                  dbms_output.put(dbms_utility.format_error_backtrace);
     37                  -- Output the actual error number and message
     38                  dbms_output.put_line(dbms_utility.format_error_stack);
     39  end;
     40  /
    declare
    *
    ERROR at line 1:
    ORA-20901: Month not between 1 and 12
    ORA-06512: at line 23
    ORA-01843: not a valid month


    SQL>
     
    One could also attempt to parse the string using the desired separator and verify 3 values are the result. Other approaches are available and can be found searching google.com.

    Keeping dates in DATE fields is proper, however those dates are entered as strings into applications and, without validation, 'bad' (invalid for the desired format specifier) values can be stored, which is what the original poster wants to avoid.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    First of all , its not desirable to check the date field against a string directly. It should be done by using TO_DATE function .

    Code (SQL):

    SQL> ALTER SESSION SET nls_date_Format = 'DD-MON-RR';

    SESSION altered.

    SQL> SELECT empno, ename, hiredate
      2  FROM EMP
      3  WHERE hiredate <= TO_DATE('17-NOV-81','DD-MON-YY');

         EMPNO ENAME      HIREDATE
    ---------- ---------- ---------
          7369 SMITH      17-DEC-80
          7499 ALLEN      20-FEB-81
          7521 WARD       22-FEB-81
          7566 JONES      02-APR-81
          7654 MARTIN     28-SEP-81
          7698 BLAKE      01-MAY-81
          7782 CLARK      09-JUN-81
          7788 SCOTT      19-APR-87
          7839 KING       17-NOV-81
          7844 TURNER     08-SEP-81
          7876 ADAMS      23-MAY-87
          7900 JAMES      03-DEC-81
          7902 FORD       03-DEC-81
          7934 MILLER     23-JAN-82

    14 ROWS selected.

    SQL> ALTER SESSION SET nls_date_format = 'MON-DD-YYYY';

    SESSION altered.

    SQL> SELECT empno, ename, hiredate
      2  FROM EMP
      3  WHERE hiredate <= TO_DATE('17-NOV-81','DD-MON-YY');

         EMPNO ENAME      HIREDATE
    ---------- ---------- -----------
          7369 SMITH      DEC-17-1980
          7499 ALLEN      FEB-20-1981
          7521 WARD       FEB-22-1981
          7566 JONES      APR-02-1981
          7654 MARTIN     SEP-28-1981
          7698 BLAKE      MAY-01-1981
          7782 CLARK      JUN-09-1981
          7788 SCOTT      APR-19-1987
          7839 KING       NOV-17-1981
          7844 TURNER     SEP-08-1981
          7876 ADAMS      MAY-23-1987
          7900 JAMES      DEC-03-1981
          7902 FORD       DEC-03-1981
          7934 MILLER     JAN-23-1982

    14 ROWS selected.

    SQL>

     
    And for validation , create a fuction to accept the input string and desired format (from application) and convert string into date using TO_DATE function . Return the output base of the result of conversion.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The date string is not yet a date and is NOT checked against a DATE field, directly or indirectly, contrary to your assumption. The issue here is matching the input with the desired conversion format, a concept you fail to recognize. Yes, one could write a function to perform such checking:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION dt_validation(p_dtstrg IN varchar2, p_dtfmt IN varchar2)
      2  RETURN DATE
      3  IS
      4          v_dtval DATE:=NULL;
      5          v_invdt DATE:=NULL;
      6  BEGIN
      7          v_dtval := to_date(p_dtstrg, p_dtfmt);
      8
      9          RETURN v_dtval;
     10
     11  exception
     12          WHEN others THEN
     13                  RETURN v_invdt;
     14  END;
     15  /

    FUNCTION created.

    SQL>
    SQL>
    SQL> SELECT dt_validation('11/11/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('11/11
    --------------------
    11-NOV-2011 00:00:00

    SQL> select dt_validation('
    12/11/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    12/11
    --------------------
    12-NOV-2011 00:00:00

    SQL> SELECT dt_validation('13/11/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('13/11
    --------------------
    13-NOV-2011 00:00:00

    SQL> select dt_validation('
    11/12/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    11/12
    --------------------
    11-DEC-2011 00:00:00

    SQL> SELECT dt_validation('11/13/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('11/13
    --------------------


    SQL> select dt_validation('
    31/11/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    31/11
    --------------------


    SQL> SELECT dt_validation('11/21/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('11/21
    --------------------


    SQL> select dt_validation('
    11/31/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    11/31
    --------------------


    SQL>
    and I suppose one could check for a NULL return value (as illustrated here) but how does one know WHY a NULL date value was returned from that particular function? What, exactly, was wrong with the date string that caused it to not match the supplied format? Absent any error message describing the problem the end-user (and developer) are left wondering which does not make for acceptable customer service. The goal is to inform the end-user of his/her mistake so it can be corrected and the data validated. If the function is rewritten to not return a value on failure then the errors can be trapped, the error messages displayed and dealt with properly:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION dt_validation(p_dtstrg IN varchar2, p_dtfmt IN varchar2)
      2  RETURN DATE
      3  IS
      4          v_dtval DATE:=NULL;
      5  BEGIN
      6          v_dtval := to_date(p_dtstrg, p_dtfmt);
      7
      8          RETURN v_dtval;
      9
     10  exception
     11          WHEN others THEN
     12                  -- Output desired error message
     13                  dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
     14                  -- Output actual line number of error source
     15                  dbms_output.put(dbms_utility.format_error_backtrace);
     16                  -- Output the actual error number and message
     17                  dbms_output.put_line(dbms_utility.format_error_stack);
     18
     19  END;
     20  /

    FUNCTION created.

    SQL>
    SQL>
    SQL> SELECT dt_validation('11/11/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('11/11
    --------------------
    11-NOV-2011 00:00:00

    SQL> select dt_validation('
    12/11/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    12/11
    --------------------
    12-NOV-2011 00:00:00

    SQL> SELECT dt_validation('13/11/2011', 'DD/MM/RRRR') FROM dual;

    DT_VALIDATION('13/11
    --------------------
    13-NOV-2011 00:00:00

    SQL> select dt_validation('
    11/12/2011', 'DD/MM/RRRR') from dual;

    DT_VALIDATION('
    11/12
    --------------------
    11-DEC-2011 00:00:00

    SQL> SELECT dt_validation('11/13/2011', 'DD/MM/RRRR') FROM dual;
    SELECT dt_validation('11/13/2011', 'DD/MM/RRRR') FROM dual
           *
    ERROR at line 1:
    ORA-06503: PL/SQL: FUNCTION returned WITHOUT VALUE
    ORA-06512: at "BING.DT_VALIDATION", line 19


    -20999: Sumthin' bad happened -- error stack follows
    ORA-06512: at "BING.DT_VALIDATION", line 6
    ORA-01843: not a valid month

    SQL> select dt_validation('
    31/11/2011', 'DD/MM/RRRR') from dual;
    select dt_validation('
    31/11/2011', 'DD/MM/RRRR') from dual
           *
    ERROR at line 1:
    ORA-06503: PL/SQL: Function returned without value
    ORA-06512: at "BING.DT_VALIDATION", line 19


    -20999: Sumthin'
    bad happened -- error stack follows
    ORA-06512: at "BING.DT_VALIDATION", line 6
    ORA-01839: DATE NOT valid FOR MONTH
    specified

    SQL> SELECT dt_validation('11/21/2011', 'DD/MM/RRRR') FROM dual;
    SELECT dt_validation('11/21/2011', 'DD/MM/RRRR') FROM dual
           *
    ERROR at line 1:
    ORA-06503: PL/SQL: FUNCTION returned WITHOUT VALUE
    ORA-06512: at "BING.DT_VALIDATION", line 19


    -20999: Sumthin' bad happened -- error stack follows
    ORA-06512: at "BING.DT_VALIDATION", line 6
    ORA-01843: not a valid month

    SQL> select dt_validation('
    11/31/2011', 'DD/MM/RRRR') from dual;
    select dt_validation('
    11/31/2011', 'DD/MM/RRRR') from dual
           *
    ERROR at line 1:
    ORA-06503: PL/SQL: Function returned without value
    ORA-06512: at "BING.DT_VALIDATION", line 19


    -20999: Sumthin'
    bad happened -- error stack follows
    ORA-06512: at "BING.DT_VALIDATION", line 6
    ORA-01843: NOT a valid MONTH

    SQL>
    Now the issue is presented in a way that can be addressed, either by the user (by formatting the date string in the 'proper' manner) or by the developer (for assuming a date format not in use by the client).
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I agree with Zargon.
    I wonder why solution becomes so complicated when OP just want to check whether date is valid. Even One 'Y'/'N' could be sufficient for OP . :)