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 *2FROM 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.