how to do number validation and date validation in oracle

[krithika@2001]

hi

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

2.check whether the field is numeric or not

Regards
krithika

[rajavu]

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.

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>

[zargon]

Not clear what do you mean by Date validation . Better you keep the date in Date/Timespamp field in Oracle.

And that is an admirable suggestion however it does no good for data being entered into an application interface. Take, for example:

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:

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.

[rajavu]

First of all , its not desirable to check the date field against a string directly. It should be done by using TO_DATE function .

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.

[zargon]

First of all , its not desirable to check the date field against a string directly. It should be done by using TO_DATE function .

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.

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:

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:

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).

[rajavu]

I agree with Zargon.

hi

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

2.check whether the field is numeric or not

Regards
krithika

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 .