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.