Sokrates on Oracle

«
»

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

This entry was posted on 8. November 2014 at 22:00 and is filed under PL/SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

7 Responses to “Why is it not possible to raise an exception when handling no_data_needed ?”

  1. Sayan Malakshinov said

    8. November 2014 at 22:41

    In my humble opinion, it’s just because NO_DATA_NEEDED is a special control exception like NO_DATA_FOUND for SQL.
    But we can use “raise_application_error” for such purposes:

    SQL> ;
  1  create or replace function demo(p int:=0) return sys.odciNumberList pipelined
  2  as
  3  begin
  4    pipe row(1);
  5    pipe row(1/0);
  6  exception when others then
  7    dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  8    raise_application_error(-20000,'Exception.',case p when 0 then false else true end);
  9* end;
SQL> /

Function created.

SQL> select * from table(demo);
ERROR:
ORA-20000: Exception.
ORA-06512: at "XTENDER.DEMO", line 8
ORA-06512: at line 1

no rows selected

in exception handler, sqlcode: -1476

SQL> select * from table(demo) where rownum=1;
ERROR:
ORA-20000: Exception.
ORA-06512: at "XTENDER.DEMO", line 8
ORA-06512: at line 1

no rows selected

in exception handler, sqlcode: -6548

SQL> select * from table(demo(1)) where rownum=1;
ERROR:
ORA-20000: Exception.
ORA-06512: at "XTENDER.DEMO", line 8
ORA-06548: no more rows needed
ORA-06512: at line 1

no rows selected

in exception handler, sqlcode: -6548
    Reply

    • Matthias Rogel said

      9. November 2014 at 04:15

      Hi Sayan,

      thanks, reproduces for me on 12.1, but not on 11.2:

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

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

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

      So, they have changed the implementation in 12.1.

      For me, it doesn’t matter, if I use raise_application_error or my original funtion on 12.1 :

      sokrates@12.1 > select * from table(sayans_demo()) where rownum=1;
ERROR:
ORA-20000: Exception.
ORA-06512: at "SOKRATES.SAYANS_DEMO", line 8
ORA-06512: at line 1



no rows selected

sokrates@12.1 > select * from table(demo()) where rownum=1;
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-06548: no more rows needed
ORA-06512: at line 1



no rows selected
      Reply

  2. Gokhan Atil said

    8. November 2014 at 23:14

    In chapter 12 of Database PL/SQL Language Reference, it says:

    “If the exception-handling part of a block that includes a PIPE ROW statement includes an OTHERS exception handler to handle unexpected exceptions, then it must also include an exception handler for the expected NO_DATA_NEEDED exception. Otherwise, the OTHERS exception handler handles the NO_DATA_NEEDED exception, treating it as an unexpected error.”

    So it’s an expected error?

    Reply

    • Matthias Rogel said

      9. November 2014 at 04:25

      my point is, NO_DATA_NEEDED is handled by the OTHERS exception handler, but not in the same way as “normal” exceptions like ZERO_DIVIDE for example. ( at least in 11.2 )

      Reply

  3. Jeffrey Kemp said

    9. November 2014 at 09:11

    NO_DATA_NEEDED is a special exception that allows your pipelined function to clean up any resources it used, in the event that the calling SQL doesn’t fetch all the rows. If it is propogated out of the function, the SQL will ignore the exception. http://tkyte.blogspot.com.au/2010/04/nodataneeded-something-i-learned.html

    Reply

Leave a comment

«
»