Catching NO_DATA_NEEDED Exception in Pipelined Functions

Pipelined Functions are functions whose output is referenced as a table in a SQL statement. Here's an example of a simple pipelined function:

-- Prefix: Create supporting objects
create or replace type emp_info as object (
  ename varchar2(255),
  job varchar2(255)
);
/

create or replace type emp_info_arr as table of emp_info;
/

-- Pipelined Function (with error handling)
create or replace function f_pipeline_demo
  return emp_info_arr pipelined
as
  l_scope logger_logs.scope%type := lower($$plsql_unit);
begin
  for x in (
    select ename, job
    from emp
  ) loop
    pipe row (emp_info(x.ename, x.job));
  end loop;

exception
  when others then
    logger.log_error('Unhandled Exception', l_scope); 
end;
/


-- Query First 5 rows of Pipelined Function (they're 14 ros in emp table)
select *
from table(f_pipeline_demo)
where rownum <= 5
;

   ENAME          JOB
________ ____________
KING     PRESIDENT
BLAKE    MANAGER
CLARK    MANAGER
JONES    MANAGER
SCOTT    ANALYST

The above demo serves two purposes. First to provide a basic example of a pipelined function and second to show a "hidden error" in its implementation. No errors were raised when querying the pipelined function, however looking at the logger output shows that an error was raised:

select logger_level, text
from logger_logs_5_min
where 1=1
  and scope like 'f_pipeline_demo'
;

 LOGGER_LEVEL  TEXT  
 ____________  ____
            2  Unhandled Exception ORA-06548: no more rows needed ...

Taking a step back, the emp table has 14 rows in it. Querying f_pipeline_demo was limited to the first 5 rows by adding where rownum <= 5. When a pipelined function is called and it realizes that it no longer needs to process additional rows it stops by raising the no_data_needed.

At first glance this may seem like a weird/bad outcome but it makes sense. Pipelined functions sometimes contain some costly computations in each loop. If only 5 rows are returned, doing the additional computations isn't required.

Since I log all exceptions in my code, I don't want to log no_data_needed exceptions as it's not a "bad" exception. To get around this I can simply change the exception block in the pipelined function to:

...
exception
  -- Make sure this is before the "when others then" block
  when no_data_needed then
    null;
  when others then
    logger.log_error('Unhandled Exception', l_scope); 
end;
/

Now when the pipelined function is queried with a restricted set of rows the no_data_needed exception won't be logged.