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
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
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
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:
Now when the pipelined function is queried with a restricted set of rows the
no_data_needed exception won’t be logged.