NO_DATA_NEEDED

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.

scott@ORCL> create or replace function read_data(
  2             p_dir in varchar2,
  3             p_file in varchar2) return sys.odcivarchar2list
  4             pipelined as
  5     l_filetype utl_file.file_type;
  6     l_data varchar2(32767);
  7  begin
  8     dbms_output.put_line (' Begin process ');
  9     l_filetype := utl_file.fopen(p_dir,p_file,'r');
 10     loop
 11     dbms_output.put_line (' pipe rows ');
 12             utl_file.get_line(l_filetype,l_data,32767);
 13             pipe row(l_data);
 14     end loop;
 15
 16     dbms_output.put_line (' End process ');
 17     utl_file.fclose(l_filetype);
 18     return;
 19  exception
 20     when no_data_found then
 21     dbms_output.put_line (' End process ');
 22     utl_file.fclose(l_filetype);
 23     return;
 24  end read_data;
 25  /
Function created.

scott@ORCL> select *
  2  from
  3  table(read_data('DATA_PUMP_DIR','SAMPLE.TXT'))

COLUMN_VALUE
---------------------------------------------------
A
B
C
D
E

 Begin process
 pipe rows
 pipe rows
 pipe rows
 pipe rows
 pipe rows
 pipe rows
 End process 

However, if you call it from a query such as

SELECT * FROM TABLE(read_data('DATA_PUMP_DIR','SAMPLE.TXT')) where rownum <= 1;

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"

scott@ORCL> SELECT * FROM TABLE(read_data('DATA_PUMP_DIR','SAMPLE.TXT')) where rownum <= 1;

COLUMN_VALUE
------------------------------
A

 Begin process
 pipe rows

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.

scott@ORCL> create or replace function read_data(
  2             p_dir in varchar2,
  3             p_file in varchar2) return sys.odcivarchar2list
  4             pipelined as
  5     l_filetype utl_file.file_type;
  6     l_data varchar2(32767);
  7  begin
  8     dbms_output.put_line (' Begin process ');
  9     l_filetype := utl_file.fopen(p_dir,p_file,'r');
 10     loop
 11     dbms_output.put_line (' pipe rows ');
 12             utl_file.get_line(l_filetype,l_data,32767);
 13             pipe row(l_data);
 14     end loop;
 15
 16     dbms_output.put_line (' End process ');
 17
 18     utl_file.fclose(l_filetype);
 19     return;
 20  exception
 21             when no_data_needed then
 22             dbms_output.put_line (' End process in exception');
 23             utl_file.fclose(l_filetype);
 24             return;
 25  end read_data;
 26  /

Function created.

scott@ORCL> SELECT *
  2  FROM
  3  TABLE (read_data('DATA_PUMP_DIR','SAMPLE.TXT'))
  4  WHERE rownum <= 1;

COLUMN_VALUE
------------------------------------------------------
A

 Begin process
 pipe rows
 End process in exception

As you can see – our special cleanup code ( **End process in exception** ) was executed and we could clean up any resources we allocated.