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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10

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