Skip to Main Content

Field/Column names

ABC
expected: Aexpected: Cexpected: B

Not everybody likes pipelined table functions, but I do - when used in moderation of course, like most things :-)

And although I have been using pipelined table functions for years, it turns out it has a "feature" (illustrated in the example report above - see code below) that completely caught me by surprise the other day.
When you fill your table of records, Oracle ignores the field/column names completely. Instead, it just fills them in the same order as in the (cursor) select. So if, like me, you happen to swap B and C without noticing, you can spend half an hour debugging something so small it's almost invisible...

Cardinality hint

When using pipelined table functions, you may run into a situation where the optimizer doesn't choose the correct execution path, because it has no idea how many rows the pipelined table function will return.
If you know beforehand that the pipelined table function will return a limited number of rows, you can use the Cardinality hint to get the correct execution path.

A good example of how to use this, in this case for Apex (Interactive) Reports, is given here. I used this hint recently to reduce the execution time of an IR from over 5 minutes to under 5 seconds...
Some more detailed info on the hint itself can be found here.

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Package

Package
Name
Source

Package Body

Package Body
Name
Source