This article covers how to write PL/SQL object table functions. PL/SQL object table functions provide you with a powerful technique to solve complex query problems. You can use object table functions to return result sets that you can’t write as a query.

There is just one caveat on using object table functions. You must truly require complex logic that can’t be resolved in an ordinary query. For reference, this example really doesn’t need to be an object table function but it demonstrates tips and techniques related to them.

This article teaches you how to do the following:

  • Create an object type and collection
  • Create an object table function
  • Create co-dependent cursors and merge co-dependent cursors
  • Use native dynamic SQL (NDS) to query a result set of unknown values
  • Use bulk collection to populate a collection of object types
  • Construct instances and a collection of instances in a SELECT-list clause

You can access the results of a PL/SQL object table function from an ordinary query when you leverage Oracle’s built-in TABLE function. This means you can handle the results like columns from any table or view.

A PL/SQL object table function returns a collection of a SQL object type. A SQL object type can be simple or complex. A simple object type lists a record. A record, sometimes called a struct, is a collection of fields. As a rule, the fields of a record often have different scalar data types, like the definition of a table. The fields of a record are often called members to avoid confusing them with elements of a collection.

A complex SQL object type includes member methods. Complex SQL object types require special handling but object table functions return simple SQL object types.

While you can create a table with a CREATE TABLE statement, you create a collection of a SQL object type in two steps. Step one creates the SQL object type. Step two creates the SQL collection.

To build the PL/SQL object table function, you need to create the object type and the collection of the object type first. The following statement creates the table_struct object type:

SQL> CREATE OR REPLACE
  2    TYPE table_struct IS OBJECT
  3    ( table_name    VARCHAR2(30)
  4    , column_cnt    NUMBER
  5    , avg_col_len   NUMBER
  6    , row_cnt       NUMBER
  7    , avg_row_len   NUMBER
  8    , chain_cnt     NUMBER );
  9  /

The table_struct example separates the CREATE OR REPLACE syntax on line 1 from the object type definition on lines 2 through 8. That’s done to highlight the importance of the semicolon on line 8. The semicolon is a statement terminator. You need a statement terminator because the object type definition is a PL/SQL statement. After the PL/SQL statement, you need a forward slash on line 9 to execute the statement.

After you create the the table_struct object type, you create the table_list collection with the following statement:

SQL> CREATE OR REPLACE
  2    TYPE table_list IS TABLE OF table_struct;
  3  /

Having defined the object type and collection, you can now create an object type function. The difference between an ordinary function and an object type function is the return type. Ordinary functions return scalar data types. Pipelined table functions return a SQL result set in place of a PL/SQL record collection. Object table functions return a SQL collection. The difference between a pipelined table function and object table function is straightforward.

A pipelined table function converts a PL/SQL collection of a PL/SQL record type (another type of record structure) or an embedded SQL object type into an aggregate result set. You write pipelined table functions when working with legacy PL/SQL collections.

An object type function returns a SQL collection. SQL collections are aggregate result sets by default. This makes type casting the only difference between pipelined and object table functions. The SQL built-in TABLE function manages the result from the pipelined and object table functions the same way.

The listing function shows you how to write an object table function. The listing function returns a composite view of data dictionary values and the dynamic count of rows from physical tables. It uses explicit cursors and Native Dynamic SQL to create a dynamic data set, which you can return by a simple query.

You have two options when you define any PL/SQL function – a row-by-row or bulk processing. This article explores both because it is often easier for some developers to see the row-by-row approach before they explore the bulk processing option. As a rule, the bulk processing approach is always the more efficient solution.

The row-by-row version of the listing program is:

SQL> CREATE OR REPLACE
2 FUNCTION listing RETURN table_list IS
  3    /* Variable list. */
  4    lv_column_cnt   NUMBER;
  5    lv_avg_col_len  NUMBER;
  6    lv_row_cnt     NUMBER;
  7
  8    /* Declare a statement variable. */
  9    stmt VARCHAR2(200);
 10
 11    /* Declare a system reference cursor variable. */
 12    lv_refcursor SYS_REFCURSOR;
 13    lv_table_cnt NUMBER;
 14
 15    /* Declare an output variable. */
 16    lv_list  TABLE_LIST := table_list();
 17
 18    /* Declare a table list cursor. */
 19    CURSOR c IS
 20      SELECT table_name
 21      ,    avg_row_len
 22      ,    chain_cnt
 23      FROM   user_tables
 24      WHERE  table_name NOT IN
 25            ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS'
 26            ,'APEX$_WS_ROWS','APEX$_WS_HISTORY','APEX$_WS_NOTES'
 27            ,'APEX$_WS_LINKS','APEX$_WS_TAGS','APEX$_WS_FILES'
 28            ,'APEX$_WS_WEBPG_SECTION_HISTORY','DEMO_USERS'
 29            ,'DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
 30            ,'DEMO_ORDER_ITEMS','DEMO_STATES');
 31
 32    /* Declare a column count. */
 33    CURSOR column_cnt
 34    ( cv_table_name  VARCHAR2 ) IS
 35      SELECT COUNT(column_id) AS cnt_columns
 36      ,      SUM(avg_col_len)/COUNT(column_id) AS avg_col_len
 37      FROM     user_tab_columns
 38      WHERE    table_name = cv_table_name;
 39  BEGIN
 40    /* Read through the data set of non-environment variables. */
 41    FOR i IN c LOOP
 42      /* Count the columns of a table. */
 43      FOR j IN column_cnt(i.table_name) LOOP
 44        lv_column_cnt := j.cnt_columns;
 45        lv_avg_col_len := j.avg_col_len;
 46      END LOOP;
 47
 48      /* Declare a statement. */
 49      stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
 50
 51      /* Open the cursor and write set to collection. */
 52      OPEN lv_refcursor FOR stmt;
 53      LOOP
 54        FETCH lv_refcursor INTO lv_table_cnt;
 55        EXIT WHEN lv_refcursor%NOTFOUND;
 56        lv_list.EXTEND;
 57        lv_list(lv_list.COUNT) := table_struct(
 58                                 table_name  => i.table_name
 59                               , avg_row_len => i.avg_row_len
 60                                , chain_cnt   => i.chain_cnt
 61                                , column_cnt  => lv_column_cnt
 62                                , avg_col_len => lv_avg_col_len
 63                                 , row_cnt     => lv_table_cnt);
 64      END LOOP;
 65    END LOOP;
 66    /* Return the collection. */
 67    RETURN lv_list;
 68  END;
 69  /

Line 2 returns the table_list collection. Lines 9, 12, and 13 support the NDS statement in the listing function. Line 16 creates an empty table_list collection by declaring the variable and instantiating an empty collection.

The column_cnt cursor is a parameterized cursor, and the SELECT-list on lines 35 and 36 calculates statistics for each table processed in the nested for loop on lines 43 thru 46.

The column_cnt cursor and nested loop are examples of poor coding technique. A simple join between the user_tables and user_tab_columns views eliminates both. While the row-by-row mechanic doesn’t highlight this type of inefficiency, the bulk processing approach does.

Line 49 uses concatenation because NDS doesn’t let you bind table names. Line 52 opens a PL/SQL reference cursor for the dynamic statement. Line 54 fetches each row into a scalar variable. Line 55 exits the dynamic reference cursor when all rows have been read. Line 56 extends the memory of the lv_list collection and line 57 assigns an instance of the table_struct object type as an element of the collection. Line 67 returns the lv_list collection.

The NDS element is also a poor coding technique because the num_rows column in the user_tables view holds the number of rows in any table. While the num_rows column is dependent on running statistics, most databases enable statistics as a background process. You should only use NDS when you truly need to do so.

You can query the result and format the results in SQL*Plus or simply run the following query in SQL*Developer or Toad:

SQL> SELECT *
  2  FROM   TABLE(listing);

Line 1 uses the asterisk to return all columns in the SELECT-list. Line 2 uses the TABLE built-in function to convert the collection to a SQL result set. The program would print something like the following in SQL*Plus with appropriate formatting:

                                Average
                                 Column    Row  Chain
Table Name           Column #    Length Length  Count  Row #
-------------------- -------- --------- ------ ------ ------
ITEM
                       14     14.21    197      0     93
SYSTEM_USER                11      4.36     48      0      5
ACCOUNT_LIST                8      5.13     38      0    200
RENTAL_ITEM                 9      4.56     41      0  4,703
STREET_ADDRESS              8      6.50     52      0     28
CALENDAR                    9      6.00     53      0    300
TELEPHONE                  11      5.00     55      0     18
AIRPORT                     9      6.89     61      0      6
CONTACT                    10      5.30     51      0     18
TRANSACTION                12      6.58     79      0  4,694
ADDRESS                    10      5.60     55      0     18
MEMBER                      9      7.22     65      0     10
PRICE                      11      4.36     48      0    558
RENTAL                      8      5.75     46      0  4,694
COMMON_LOOKUP              10     10.30    101      0     49

15 rows selected.

The refactored listing object table function uses bulk processing. As a result of the change, you remove the column_cnt cursor by joining the user_tables and user_tab_columns views. You also add the num_rows column to your base cursor.

The following creates the refactored listing object table function:

SQL> CREATE OR REPLACE
  2  FUNCTION listing RETURN table_list IS
  3    /* Variable list. */
  4    lv_column_cnt   NUMBER;
  5    lv_avg_col_len  NUMBER;
  6    lv_row_cnt         NUMBER;
  7
  8    /* Declare a statement variable. */
  9    stmt     VARCHAR2(200);
 10
 11    /* Declare a system reference cursor variable. */
 12    lv_refcursor     SYS_REFCURSOR;
 13    lv_table_cnt     NUMBER;
 14
 15    /* Declare an output variable. */
 16    lv_list  TABLE_LIST;
 17
 18    /* Declare a table list cursor. */
 19    CURSOR c IS
 20    SELECT  table_struct(
 21        table_name => ut.table_name
 22      , column_cnt => COUNT(utc.column_id)
 23      , avg_col_len =>SUM(avg_col_len)/COUNT(column_id)
 24      , avg_row_len => ut.avg_row_len
 25      , chain_cnt => ut.chain_cnt
 26      , row_cnt => num_rows )
 27    FROM    user_tables ut INNER JOIN user_tab_columns utc
 28    ON      ut.table_name = utc.table_name
 29    WHERE   ut.table_name NOT IN
 30            ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS'
 31             ,'APEX$_WS_ROWS','APEX$_WS_HISTORY','APEX$_WS_NOTES'
 32            ,'APEX$_WS_LINKS','APEX$_WS_TAGS','APEX$_WS_FILES'
 33             ,'APEX$_WS_WEBPG_SECTION_HISTORY','DEMO_USERS'
 34             ,'DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
 35               ,'DEMO_ORDER_ITEMS','DEMO_STATES')
 36      GROUP BY ut.table_name
 37      ,        ut.avg_row_len
 38      ,        ut.chain_cnt
 39      ,        ut.num_rows;
 40
 41  BEGIN
 42    /* Read set into collection. */
 43    OPEN     c;
 44    FETCH c BULK COLLECT INTO lv_list;
 45    CLOSE c;
 46
 47    /* Return the collection. */
 48    RETURN lv_list;
 49  END;
 50  /

Line 16 no longer initializes the lv_list collection because the BULK COLLECT assigns a complete collation to the variable. The BULK COLLECT effectively manages the memory allocation because you embed the constructor logic in the SELECT-list of the cursor on lines 20 thru 26.

The new cursor includes a join between the user_tables and user_tab_columns views. It also includes reference to the num_rows column of the user_tables view. This effectively means a single bulk cursor returns everything that’s needed in the listing object table function. Line 43 opens the cursor, line 44 fetches all the rows into a collection, and line 45 closes the cursor. Line 48 returns the collection of the object type.

The reality is that simple examples like this demonstrate tips and techniques but may not really benefit from those features. While you have learned the tips and techniques to write and leverage object table functions, don’t write them unless they add value to your solution space.