Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page