Monday, April 19, 2010

NO_DATA_NEEDED - something I learned recently

I'll be writing about this in Oracle Magazine shortly as part of the asktom column - but thought I'd mention it here too.

Recently on asktom.oracle.com, I was asked a question about the pre-defined exception NO_DATA_NEEDED. At first I thought that is was a typo – they really meant NO_DATA_FOUND – since I hadn’t heard of or read about that exception. But in looking a little deeper, I discovered what it was.

If you ever write a pipelined function - there is a good chance you need to be aware of it. Don't go searching for it in the documentation (it will be there in the next dot release - but it isn't there yet), you won't find it. Don't google for it - you won't find much about it yet. But it is something we've needed, probably knew we needed, just never thought about it.

What if you have a pipelined function that does something like:

a) open file
b) read line - pipe row
c) when no more data, close file and return

It works perfectly - if you read all records from the file. However, if you call it from a query such as:

select * from table(pipelined_function( '/tmp/foo.dat' )) where rownum = 1;


What happens - what happens if there was zero records in /tmp/foo.dat to read? one record? More than one record?

Well, "it depends". In all likelihood - if there was one or more records - you would leave a file handle open - and if you called this function over and over, you would leak an open file each time and eventually run out of file handles. So, you would have an error that would "sometimes happen" and "sometimes not happen". In other words - one of those strange non-reproducible bugs that only happens when it rains on a Tuesday after midnight but before 8am.

Enter NO_DATA_NEEDED - an exception that doesn't behave like any other exception. An exception that is raised - but does not cause failure. An exception that can be caught, but if it isn't - everything is still OK.


Say you have code like this:


create or replace function
foo( inputs ... )
return some_type
PIPELINED
as
/* declaration */
begin
/* initialization */

/* process a loop */
pipe row(i);
end loop;

/* clean up */
return;
end;
/

The clean up code would execute and do the right thing if you exit the loop - but not so if you just stopped calling this function. The NO_DATA_NEEDED exception is there for just such a case. Here is a concrete example:


SQL> create or replace function
2 generate_data( n in number )
3 return sys.odciNumberList
4 PIPELINED
5 as
6 begin
7 dbms_output.put_line
8 ( '===>>> INITIALIZE' );
9 for i in 1..generate_data.n
10 loop
11 dbms_output.put_line
12 ( '===>>> PROCESS' );
13 pipe row(i);
14 end loop;
15 dbms_output.put_line
16 ( '===>>> CLEAN UP' );
17 return;
18 end;
19 /

Function created.


That is a pretty straightforward PL/SQL pipelined function – if we run it to completion – we would see this output:


SQL> select *
2 from table(generate_data(2));

COLUMN_VALUE
------------
1
2

===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> CLEAN UP
SQL>


Which is what we expect – but what if we don’t fetch two rows from that function, what if we only fetch one?


SQL> select *
2 from table(generate_data(2))
3 where rownum = 1;

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

===>>> INITIALIZE
===>>> PROCESS
SQL>


As you can see – we did the initialize and one process bit of our code, but the rest – it was just skipped over, because the invoking SQL statement did not need it. We didn’t see any error (we would expect an unhandled exception to raise an error!), it just appears to have worked.

There was however, an exception raised – an exception that does not have to be caught. It will be ignored entirely if it is not caught. It differs from every other exception in that regard – we would expect an unhandled exception to propagate to the client and appear as “an error”. Lets see what happens with out code if we implement this error handler:

SQL> create or replace function
2 generate_data( n in number )
3 return sys.odciNumberList
4 PIPELINED
5 as
6 begin
7 dbms_output.put_line
8 ( '===>>> INITIALIZE' );
9 for i in 1..generate_data.n
10 loop
11 dbms_output.put_line
12 ( '===>>> PROCESS' );
13 pipe row(i);
14 end loop;
15 dbms_output.put_line
16 ( '===>>> CLEAN UP' );
17 return;
18 exception
19 when no_data_needed
20 then
21 dbms_output.put_line
22 ( '***>>> CLEAN UP' );
23 return;
24 end;
25 /

Function created.

On line 19 we catch the predefined exception NO_DATA_NEEDED and on line 21 announce that we are cleaning up (releasing any resources that need be released). Now when we run this pipelined function without exhausting it we see:

SQL> select *
2 from table(generate_data(2))
3 where rownum = 1;

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

===>>> INITIALIZE
===>>> PROCESS
***>>> CLEAN UP
SQL>

As you can see – our special cleanup code (we used ***>>> to announce it) was executed and we could clean up any resources we allocated.
POST A COMMENT

22 Comments:

OpenID Venzi said....

Hi Tom,

Two questions here:

* In which version got this exception introduced?

* Does this exception get raised every time when a PL/SQL procedure/function is completed?

Thx,

Venzi

Mon Apr 19, 12:46:00 PM EDT  

Blogger Thomas Kyte said....

It was introduced with pipelined functions - back in 9i...


It gets raised when the pipelined function (and only a pipelined function) is terminated before it hits the return statement (the return that cannot return anything...)

Mon Apr 19, 01:19:00 PM EDT  

Blogger Dual said....

"Enter NO_DATA_NEEDED - an exception that doesn't behave like any other exception."

Interesting stuff!
But still: can I 'categorize' this 'exception' just like
ORA-00000: normal, successful completion
Cause: Normal exit.
Action: None.

Which is (imo) an 'error' that's not really an 'error'?

Thanks, and it's nice to see and read that you've 'revived' your blog by using the 'What did I learn today?'-mantra.

Regards,

Martijn ( considering a beard ;-) )


Martijn

Mon Apr 19, 01:36:00 PM EDT  

Blogger Thomas Kyte said....

@Martijn

Not really - what exception would you catch for that?

ops$tkyte%ORA11GR2> declare
2 no_error exception;
3 pragma exception_init( no_error, 0 );
4 begin
5 null;
6 end;
7 /
no_error exception;
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00701: illegal ORACLE error number 0 for PRAGMA EXCEPTION_INIT

Mon Apr 19, 01:39:00 PM EDT  

Blogger Dual said....

Aha, ofcourse, I see what you mean.
Pretty obvious in the end.
*it's monday over here ;-)*
What I thought was:
ora-00000 isn't really an 'error', so my first thought, after reading your post was: no_data_needed isn't really an 'exception'.
But they're both being handled 'from within Oracle in a different way since an error <> exception', so to speak.

Thanks and regards,
Martijn

Mon Apr 19, 02:09:00 PM EDT  

Anonymous Sokrates said....

Wouldn't make an Introduction of "finally" make "no_Data_needed" superfluous ? (and would Be useful Not only for Pipelined functions)

Mon Apr 19, 03:29:00 PM EDT  

Blogger Thomas Kyte said....

@Sokrates

not really - not in this case. This is a case where by the routine releases control and just never gets it back.

We are force throwing an exception in there. You are not having an "exit" from the routine really - it just isn't getting invoked anymore.

Mon Apr 19, 03:35:00 PM EDT  

Blogger Brian Tkatch said....

Good stuff Tom. And thanx for the so simple example.

Mon Apr 19, 06:10:00 PM EDT  

Blogger Gary Myers said....

In the rownum example, it fires when the SQL has returned all the rows from the query. But it also works when you don't pull back all the rows from an explicit cursor. The cleanup fires when the cursor is closed.

declare
cursor c_1 is select column_value from table(generate_data(10));
v_n number;
begin
open c_1;
dbms_output.put_line('looping');
for i in 1..5 loop
fetch c_1 into v_n;
end loop;
dbms_output.put_line('end looping');
close c_1;
end;
/

Mon Apr 19, 07:03:00 PM EDT  

Blogger Jeffrey Kemp said....

Looks like it's been documented since 9i:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96525/e4100.htm#39273

Mon Apr 19, 10:44:00 PM EDT  

Anonymous Sokrates said....

I noticed that exception no_data_needed is not always raised when
user requests cancel of current operation
during select:

create or replace function
generate_data( n in number )
return sys.odciNumberList
PIPELINED
as
begin
dbms_output.put_line
( '===>>> INITIALIZE' );
for i in 1..generate_data.n
loop
dbms_output.put_line
( '===>>> PROCESS' );
pipe row(i);
dbms_lock.sleep(1);
end loop;
dbms_output.put_line
( '===>>> CLEAN UP' );
return;
end;
/

(I added then line
dbms_lock.sleep(1)
to your code
)

then, from sqlplus:
SQL> set timi on feedb on serverout on
SQL> set arraysize 1
SQL> select * from table(generate_data(10));

- I will wait until I see 3 rows then press Ctrl-C to cancel select

1
2
3
ERROR:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 201
ORA-06512: at "SOKRATES.GENERATE_DATA", line 14



3 rows selected.

Elapsed: 00:00:02.33

SQL> exec null
===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> PROCESS

PL/SQL procedure successfully completed.


why is cleanup-code in exception-handler not called ?

Tue Apr 20, 04:33:00 AM EDT  

Blogger Jeffrey Kemp said....

Sokrates: ORA-01013 is raised, you just need to add a handler for it:

create or replace function
generate_data( n in number )
return sys.odciNumberList
PIPELINED
as
e_cancelled EXCEPTION;
PRAGMA EXCEPTION_INIT(e_cancelled, -1013);
begin
dbms_output.put_line
( '===>>> INITIALIZE' );
for i in 1..generate_data.n
loop
dbms_output.put_line
( '===>>> PROCESS' );
pipe row(i);
dbms_lock.sleep(1);
end loop;
dbms_output.put_line
( '===>>> CLEAN UP' );
return;
EXCEPTION
WHEN e_cancelled THEN
dbms_output.put_line('===>>> CLEAN UP (e_cancelled)');
RAISE;
WHEN NO_DATA_NEEDED THEN
dbms_output.put_line('===>>> CLEAN UP (NO_DATA_NEEDED)');
end;
/

Tue Apr 20, 05:14:00 AM EDT  

Anonymous Sokrates said....

@Jeffrey

thanks for that, however:
I have played with another function
*AND SOMETIMES NO_DATA_NEEDED IS RAISED WHEN I PRESS CTRL-C SOMETIMES NOT*.

It seems to be quite undeterministic when NO_DATA_NEEDED is raised and when not.

Consider the following example:

create or replace function
generate_data_unsafe
return sys.odciNumberList
PIPELINED
as
i int;
begin
dbms_output.put_line
( '===>>> INITIALIZE' );
i:=1;
loop
dbms_output.put_line
( '===>>> PROCESS' );
pipe row(i);
i:=i+1;
dbms_lock.sleep(1);
end loop;
dbms_output.put_line
( '===>>> CLEAN UP' );
return;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
dbms_output.put_line('===>>> CLEAN UP (NO_DATA_NEEDED)');
end;
/

SQL> select /* press CTRL-C after you see the third row */ * from table(generate_data_unsafe);
1
2
3
ERROR:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 201
ORA-06512: at "SOKRATES.GENERATE_DATA_UNSAFE", line 16



3 rows selected.

Elapsed: 00:00:02.73

SQL> exec null
===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> PROCESS

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


NO_DATA_NEEDED was not raised.


BUT:
when I comment out
dbms_lock.sleep(1);
in generate_data_unsafe and then

SQL> select /* press CTRL-C as fast as you can */ * from table(generate_data_unsafe);

I get the interesting result (also note the last two numbers of the result set):

....
1499
1500
1501
1545
ERROR:
ORA-01013: user requested cancel of current operation



1545 rows selected.

Elapsed: 00:00:00.08

SQL> exec null
...
===>>> PROCESS
===>>> PROCESS
===>>> PROCESS
===>>> PROCESS
===>>> PROCESS
===>>> CLEAN UP (NO_DATA_NEEDED)

PL/SQL procedure successfully completed.


which seems to show me that
ORA-01013 *AND* NO_DATA_NEEDED
both where raised !!!

Tue Apr 20, 05:52:00 AM EDT  

Blogger Jeffrey Kemp said....

Sokrates: it's probably to do with the fact that the context is switching between executing the SQL statement, and the PL/SQL function. You have to press Ctrl+C while it's inside the function :)

Tue Apr 20, 06:10:00 AM EDT  

Anonymous Kevan Gelling said....

If you want to use a common clean-up section then WHEN OTHERS (with RAISE of course) will handle NO_DATA_NEEDED too. The RAISE command does not re-raise NO_DATA_NEEDED.


CREATE OR REPLACE FUNCTION generate_data( n in number )
RETURN sys.odciNumberList
PIPELINED
AS
BEGIN
DBMS_OUTPUT.PUT_LINE( '===>>> INITIALIZE' );
FOR i in 1..generate_data.n
LOOP
DBMS_OUTPUT.PUT_LINE( '===>>> PROCESS' );
PIPE ROW(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE( '===>>> CLEAN UP' );
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( '+++>>> CLEAN UP' );
RAISE;
END;
/

Tue Apr 20, 06:29:00 AM EDT  

Blogger Flado said....

There it is, in the error stack:

select (select * from table(dbms_xplan.display_cursor('the Answer?',42))) from dual
*
ERROR at line 1:
ORA-06548: no more rows needed
ORA-06512: at line 1
ORA-01427: single-row subquery returns more than one row

Tue Apr 20, 07:41:00 AM EDT  

Blogger Thomas Kyte said....

@Jeff Kemp -

documented as in "how this works, what it is for, how to use it, where to use it".... An error message to me just does not count :)

@Sokrates -

for the same reason no_data_needed would not be thrown if there was a PROGRAM_ERROR exception, or something threw a NO_DATA_FOUND and didn't have any way to handle it and so on. One exception - one error in the procedure. Your later example is rather deterministic - just you couldn't see it. If you get the error in the procedure - 1031 is raised in the procedure. If you get the error at a higher level - not when you are in the procedure - the code path will be different.

Tue Apr 20, 08:14:00 AM EDT  

Anonymous Alberto Dell'Era said....

I have noticed that NO_DATA_NEEDED is raised by "pipe row" :

SQL> create or replace function
2 generate_data( n in number )
3 return sys.odciNumberList
4 PIPELINED
5 as
6 begin
7 dbms_output.put_line
8 ( '===>>> INITIALIZE' );
9 for i in 1..generate_data.n
10 loop
11 dbms_output.put_line
12 ( '===>>> PROCESS' );
13 pipe row(i);
14 end loop;
15 dbms_output.put_line
16 ( '===>>> CLEAN UP' );
17 return;
18 exception
19 when no_data_needed
20 then
21 dbms_output.put_line
22 ( '***>>> CLEAN UP' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
23 return;
24 end;
25 /

Function created.

SQL> select * from table (generate_data(100)) where rownum = 1;

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

===>>> INITIALIZE
===>>> PROCESS
***>>> CLEAN UPORA-06512: at "NCSADM.GENERATE_DATA", line 13

Hence, in production code that could be leveraged to avoid duplicating the clean-up code:

create or replace function
generate_data( n in number )
return sys.odciNumberList
PIPELINED
as
begin
dbms_output.put_line
( '===>>> INITIALIZE' );

begin
for i in 1..generate_data.n
loop
dbms_output.put_line
( '===>>> PROCESS' );
pipe row(i);
end loop;
exception
when no_data_needed then
null;
end;

dbms_output.put_line
( '===>>> CLEAN UP ' );
return;
end;
/

It is also more expressive IMHO: jump out of the loop when no more rows exist, or no more rows are requested by the client.

BTW - welcome back here :)

Wed Apr 21, 07:41:00 AM EDT  

Blogger Douglass said....

Thanks for this article. I ran into this by doing a NOT EXISTS (SELECT FROM table(... )). Before I saw the article I was really getting concerned that my table functions would be useless.

I don't open any resources so I will just catch NO_DATA_NEEDED in my pipelined table functions, and do a no-op (null;).

Thu May 20, 06:48:00 PM EDT  

Blogger Douglass said....

1 more thing, could you go into what you mean a little more w/

"An exception that is raised - but does not cause failure. An exception that can be caught, but if it isn't - everything is still OK."

In oci8 as well as SQL Developer, a query that raises this exception seems to fail. Thanks.

Thu May 20, 11:07:00 PM EDT  

Blogger Thomas Kyte said....

@Douglass

I'm not sure what you mean, can you give me step by steps to reproduce what you mean in sqlplus (which is written in OCI)???

I ran my code above in sqlplus, it does not "fail" with any unhandled exception - please provide step by step methods to reproduce the issue.

Fri May 21, 03:29:00 AM EDT  

Blogger Douglass said....

Ok. Nevermind previous comment, I get it now.

I had a procedure that raised a
ORA-01422: exact fetch returns more than requested number of rows
which CAUSED a ORA-06548: no more rows needed.

So, lesson here is, when I get a query that fails with a ORA-06548, look in the error stack for another cause.

Fri May 21, 08:50:00 AM EDT  

POST A COMMENT

<< Home