Oracle PLSQL exception inserting dates

Question

I have an INSERT statement in a procedure with a date field in NUMBER format converted into DATE, but in some cases the conversion is not possible. I've tried to handle the exception with EXCEPTION WHEN OTHERS THEN but it doesn't insert any row.

I want to insert NULL only in the DATE field when conversion is not possible.

Do I have to use any cursor or collection?

I think you should make your own validation function: http://stackoverflow.com/questions/4486949/safe-to-number — vercelli, Aug 26 '16 at 16:07

score 4 · Accepted Answer · edited Aug 27 '16 at 04:58

4

Create your own function like this:

create or replace function safe_to_date(p number) return date DETERMINISTIC is
  begin
    return to_date(p, 'yyyymmdd');
  exception 
    when value_error
    then return null;
end;

OUTPUT

select safe_to_date(00000) as test from dual;
TEST   
--------


select safe_to_date(20160826) as test from dual;
TEST   
--------
26.08.16

edited Aug 27 '16 at 04:58

Jeffrey Kemp

59,135
14
106
158

answered Aug 26 '16 at 16:29

vercelli

4,717
2
13
15

1

`to_date` is also recognised by PL/SQL, so there's no need to add a pointless (and performance-affecting) SQL query in there. – Jeffrey Kemp Aug 27 '16 at 04:58
1

also, don't use `when others` without a raise - instead, use the specific exception you're expecting. – Jeffrey Kemp Aug 27 '16 at 04:59

Oracle PLSQL exception inserting dates

1 Answers1