CODEWITHSUNDEEP
oracle-databaseexceptionplsqloracle11g
monkey_painter
monkey_painter

Reputation: 11

Oracle PLSQL exception inserting dates

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?

Upvotes: 0

Views: 251

Answers (1)

vercelli
vercelli

Reputation: 4767

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

Upvotes: 4

Related Questions