Sokrates on Oracle

Archive for November, 2014

Why is it not possible to raise an exception when handling no_data_needed ?

Posted by Matthias Rogel on 8. November 2014

It seems impossible to raise an exception when handling no_data_needed.

See

create or replace function demo return sys.odciNumberList pipelined
as
begin
  pipe row(1);
  pipe row(1/0);
exception when others then
  dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  raise program_error;
end;
/

sokrates@11.2 > select * from table(demo());
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero



no rows selected

in exception handler, sqlcode: -1476

As expected, we handle exception raised by division by zero, there we dbms_output some info and then raise program error.

But now:

sokrates@11.2 > select * from table(demo()) where rownum=1;

COLUMN_VALUE
------------
           1

in exception handler, sqlcode: -6548
sokrates@11.2 >

oops – not expected.
“where rownum=1” causes ORA-06548 been raised and thus we again handle that. We dbms_output some info and then – no error is raised !

Why ?
I have no idea and couldn’t find any in docs either.

Update 2014/11/09:

I found the answer in Bug 13088409 – RAISE_APPLICATION_ERROR ignored in a WHEN OTHERS / WHEN NO_DATA_FOUND exception handler of a pipe-lined table function (Doc ID 13088409.8)

The point of the bug is that an exception handler

procedure h;

just behaves like

begin
   h;
exception when others then null;
end;

, when it is handling NO_DATA_NEEDED raised by a calling SQL.
h might be called either from a WHEN NO_DATA_NEEDED or a WHEN OTHERS – block.

The note says
… Versions confirmed as being affected
11.2.0.2

The fix for 13088409 is first included in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)

My test case was on 11.2.0.3

Advertisements

Posted in PL/SQL | 7 Comments »