Section 3.6. Tips for Working with Table Functions

3.6. Tips for Working with Table Functions

I'll wrap up this chapter with a few tips to help you take full advantage of table functions.

3.6.1. The Case Against SYS_REFCURSOR

Oracle's SYS_REFCURSOR function provides a way to quickly declare a weakly typed REF cursor that can process almost any cursor. The fact that SYS_REFCURSOR can be specified as the datatype means that any old SELECT statement can be passed in, as long as the cursor isn't actually manipulated in the function. For example, this function is wide open to any SELECT.

     CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )                       RETURN number_t IS       v_ret_val number_t := number_t(  );     BEGIN       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := 99;       RETURN v_ret_val;     END;

Any valid SELECT will work.

     SQL> SELECT *       2    FROM TABLE(wide_open(CURSOR(SELECT NULL       3                                  FROM DUAL)));     COLUMN_VALUE     ------------               99     SQL> SELECT *       2    FROM TABLE(wide_open(CURSOR(SELECT *       3                                  FROM orders)));     COLUMN_VALUE     ------------               99

But things have to become more isolated because, presumably, records will be fetched within the function. Thus, local variables are required to fetch into.

     CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )                       RETURN number_t IS       v_ret_val   number_t := number_t(  );       v_order_rec orders%ROWTYPE;     BEGIN       FETCH p_curs INTO v_order_rec;       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := 99;       RETURN v_ret_val;     END;

Only SELECT statements getting all columns from the ORDERS table can now be passed to this function. Others will raise the ORA-01007 error .

     SQL> SELECT *       2    FROM TABLE(wide_open(CURSOR(SELECT NULL       3                                  FROM DUAL)));       FROM TABLE(wide_open(CURSOR(SELECT NULL                  *     ERROR at line 2:     ORA-01007: variable not in select list     ORA-06512: at "SCOTT.WIDE_OPEN", line 6     SQL> SELECT *       2    FROM TABLE(wide_open(CURSOR(SELECT *       3                                  FROM orders)));     COLUMN_VALUE     ------------               99

At this point, the function can accept absolutely any SELECT statement, but it will fail if it doesn't query the ORDERS table. The flexibility of SYS_REFCURSOR and weakly typed REF cursors, in general, is rendered moot.

Because of this behavior, I prefer to completely remove the illusion of flexibility by using strongly typed REF cursors declared in a central package as follows:

     CREATE OR REPLACE PACKAGE cursors AS       TYPE order_curs IS REF CURSOR RETURN orders%ROWTYPE;     END;

and then use them in a table function.

     CREATE OR REPLACE FUNCTION wide_open ( p_curs cursors.order_curs )                       RETURN number_t IS       v_ret_val   number_t := number_t(  );       v_order_rec p_curs%ROWTYPE;     BEGIN       FETCH p_curs INTO v_order_rec;       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := 99;       RETURN v_ret_val;     END;

This has the added benefit of linking the datatype of the local variable I select into directly to the strongly typed cursor. This saves time during execution because Oracle will not have to waste time figuring out what the returned structure will be. It also allows me to coordinate the SELECT statements and cursor parameters via a single central package, should I ever want to change them. There is no need to change every function to stay in synch.

3.6.2. REF Cursors and Nesting

Because table functions return collections, there is no easy way to declare strongly typed REF cursors to handle them when nesting. Thus, I have to declare records with the same structure as the collection and then tie a REF cursor to them like this.

     CREATE OR REPLACE PACKAGE cursors       TYPE v_number_rec IS RECORD ( number_col NUMBER );      TYPE number_curs IS REF CURSOR RETURN v_number_rec;     END;

Now the strongly typed REF cursor can be used in table functions ready for nesting.

     CREATE OR REPLACE FUNCTION nested_number ( p_curs cursors.number_curs )...

3.6.3. Applying Criteria

When applying criteria to a function, be cognizant of performance, especially when deciding whether to pass parameter values into the function for use when assembling the result set or applying them to the returned result set. Here are two examples of what I mean. The first applies the criterion (col1 = 'A') to returned records after they are assembled by the function.

     SELECT *       FROM TABLE(a_function)      WHERE col1 = 'A';

This next example passes the criterion directly into the function so it can be used when assembling records.

     SELECT *       FROM TABLE(a_function('A');

Examine the complexity of the algorithm and the size of the data set to determine which approach will work best for you.

3.6.4. Standardizing Object and Collection Names

After implementing table functions in several applications, I was alarmed to see the number of duplicate object and collection types I had implemented. For example, I had created these two objects:

     SQL> DESC experiment_results_o      Name                                      Null?    Type      ----------------------------------------- -------- ------      SAMPLE_AMT                                         NUMBER     SQL> DESC research_tallies_o      Name                                      Null?    Type      ----------------------------------------- -------- ------      TALLY_TOTAL                                        NUMBER

And then rolled them up into collections with similar namesreplacing the "_o" (underscore o) suffix with "_t". This is just the simplest example of the clutter I created because I hadn't focused on the database as a whole. I've since gone back and replaced the two objects with a single one like this:

     SQL> DESC number_o      Name                                      Null?    Type      ----------------------------------------- -------- ------      COL1                                               NUMBER

I also have similar generic objects for other datatypes including several standard lengths of VARCHAR2 fields.

Another standard I follow is that my object names have "_o" appended while my collections (or tables) have "_t" appended. This allows me to discern quickly what type they are.

3.6.5. Beware of Unhandled Exceptions

Moving functions into the realm of SELECT statements makes handling exceptions a whole new ball game. It's no longer as simple as causing the function to fail and raising the error to the calling application. For example, how should we handle the situation when the following function raises the NO DATA FOUND exception?

     CREATE OR REPLACE FUNCTION unhandled                       RETURN number_t AS       v_ret_val number_t := number_t(  );       v_dummy   NUMBER;     BEGIN       SELECT 1         INTO v_dummy         FROM DUAL        WHERE 1 = 2;       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := 1;       RETURN(v_ret_val);     END;

Should the exception be returned from a SELECT statement like this?

     SQL> SELECT *        2   FROM TABLE(unhandled);     COLUMN_VALUE     ------------------------     ORA-01403: no data found

That would require Oracle to keep track of two possible result set structuresone for successful execution and one with a single VARCHAR2 column to hold a potential error message. That might be possible but would wreak havoc with nested table functions, because they too would have to handle two different return structures. That would get far too complicated far too fast.

Perhaps the SELECT should just fail outright?

     SQL> SELECT *       2    FROM TABLE(unhandled);     ORA-01403: no data found

That's better than returning an error message but could be a little confusing.

The answer is that Oracle takes the relatively easy way out by stating that the failing function simply returned no rows.

     SQL> SELECT *       2    FROM TABLE(unhandled);     no rows selected

You need to be very careful to handle all possible exceptions or your table functions may silently fail.

3.6.6. Passing Objects Instead Of Cursors

It's a little-known fact that table functions can accept collections as well as cursors as parameters . Here's a simple example.

     CREATE OR REPLACE FUNCTION give_me_a_collection ( p_col number_t )                       RETURN number_t IS       v_ret_val number_t := number_t(  );     BEGIN       v_ret_val.EXTEND(p_col.COUNT);       FOR counter IN v_ret_val.FIRST..v_ret_val.LAST LOOP         v_ret_val(counter) := p_col(counter);       END LOOP;       RETURN(v_ret_val);     END;

And here's one way to execute it in a SELECT.

     SQL> SELECT *       2    FROM TABLE(give_me_a_collection(number_t(1,2,3)));     COLUMN_VALUE     ------------                1                2                3

3.6.7. Not Read Committed

Even though table functions execute within the domain of a SELECT statement, they cannot take advantage of Oracle's read-committed architecture for their duration. Any queries performed within the table function can do so, but the table function itself works just like any other function in this regard. Consider this example table function.

     CREATE OR REPLACE FUNCTION not_committed                       RETURN number_t IS       v_ret_val NUMBER_T := NUMBER_T(  );       v_count   NUMBER;     BEGIN       SELECT COUNT(*)         INTO v_count         FROM orders;       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := v_count;       DBMS_LOCK.SLEEP(10);       SELECT COUNT(*)         INTO v_count         FROM orders;       v_ret_val.EXTEND;       v_ret_val(v_ret_val.LAST) := v_count;       RETURN(v_ret_val);     END;

It queries the number of records in the ORDERS table, waits 10 seconds, and then does it again, returning the two counts as a result set. If you execute the table function in one session and then delete (and commit) 5 orders in another session (presumably during the 10-second pause), you'll see the following results.

     SQL> SELECT *       2    FROM TABLE(not_committed);     COLUMN_VALUE     ------------            10000             9995

Be sure to factor this into your decision-making when considering the use of table functions versus queries. If you determine that read-committed access is pivotal through the operation, then table functions may not be the way to go.