Oracle PL/SQL for DBAs

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.