You Asked

Hi TOM,
I would like to know if it is possible to handle an exception in some SQL expression without context switch. I.e. I want to have something like
select "try" to_date(T.TIMESTR, 'YYYYMMDD') "when exception" null "end" from MYTABLE T;

I know I can create PL/SQL function and handle an exception here, but context switch expense is very noticable -

create or replace function dt1(value varchar2, mask varchar2, defval date:=null) return date as
pragma UDF;
begin
return TO_DATE(value, mask);
exception when others then
return defval;
end;
/

select count(*) from MYTABLE T where rownum<100000 and to_date(TIMESTR,'yyyymmdd')>sysdate-10;
300ms

select count(*) from MYTABLE T where rownum<100000 and dt1(TIMESTR,'yyyymmdd')>sysdate-10;
2s

and Connor said...

You'll need 12.2 for that, where we've added native support for that operation, for example:

SQL> select to_date(created_date, 'dd-mon-yyyy')
  2  from   t
  3  where  validate_conversion(
  4           created_date as date, 'dd-MON-yyyy'
  5         ) = 1;

SQL> select SALARY,
  2         cast(SALARY as number
  3                 DEFAULT -1 ON CONVERSION ERROR) conv_sal
  4  from   t;

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good addition to cast, but buggy

Alexander Zemerov, March 22, 2018 - 8:04 pm UTC

Thank you Connor, I didn;t know about this very useful addition to CAST syntax.
Unfortunately it doesn't work in PL/SQL (tested on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production) - I cannot specify format string.

-- standalone select works fine
select cast('123234234' as timestamp with time zone default null on conversion error,
'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm') val from dual;

-- same query use in pl/sql fails
begin
for x in (
select cast('123234234' as timestamp with time zone default null on conversion error,
'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm') val from dual)
loop
null;
end loop;
end;
/
results in error
ORA-06550: line 4, column 5:
PL/SQL: ORA-43918: This argument must be a literal

March 23, 2018 - 12:30 am UTC

Interesting...looks to be related to timestamps

SQL> begin
  2  for x in (
  3    select cast('12323A4234' as number default null on conversion error) x from dual
  4  )
  5  loop
  6    null;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> begin
  2  for x in (
  3    select cast('12323A4234' as date default null on conversion error,'DD-MON-YYYY') x from dual
  4  )
  5  loop
  6    null;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Thanks for letting us know, I'll log a bug

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good addition to cast, but buggy

More to Explore

PL/SQL demos

PL/SQL docs

Handle exception without context switch

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good addition to cast, but buggy

More to Explore

PL/SQL demos

PL/SQL docs