October 2005
Intermediate to advanced
454 pages
14h 44m
English
Most DBAs know that Oracle allows functions to be called in queries like the following:
SELECT SYSDATE
FROM DUAL;Oracle permits this type of query because the structure of its return set is defined: it will return a single column of type DATE in a single record.
Most DBAs also know that for any object (e.g., a table, a view, or another type of object) to be part of a SELECT statement, it must have a defined result set structure. Otherwise, the database will have no idea in what format the results will be returned. But you may be wondering how functions that have historically returned single scalar values can possibly return a multi-column, multi-record result set like this:
SQL> SELECTorder_number,
2 creation_date,
3 assigned_date,
4 closed_date
5 FROM TABLE(order_history_function(region_id => 22))
6 WHERE region = 11;
ORDER_NUMBER CREATION_DATE ASSIGNED_DATE CLOSED_DATE
------------ ------------- ------------- -----------
10987 10-JAN-05 11-JAN-05 22-JAN-05
10989 12-JAN-05 15-JAN-05 20-JAN-05
10993 20-JAN-05 21-JAN-05 28-JAN-05