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