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

Advertisements

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

  1. 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
    
    • 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
      
  2. 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?

    • 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 )

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: