Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.
In Oracle a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specfic or session-specific basis. It is not considered normal to create and drop GTTs on the fly. With the introduction of private temporary tables, Oracle has an option similar to that seen in other engines, where the table object itself is temporary, not just the data.
Oracle support two types of temporary tables.
The PRIVATE_TEMP_TABLE_PREFIX
initialisation parameter, which defaults to "ORA$PTT_"
, defines the prefix that must be used in the name when creating the private temporary table. In the following example we create a private temporary table without using the correct prefix in the name, which results in an error.
CREATE PRIVATE TEMPORARY TABLE ptt_temp_table ( id NUMBER, description VARCHAR2(20) ); CREATE PRIVATE TEMPORARY TABLE ptt_temp_table ( * ERROR at line 1: ORA-00903: invalid table name SQL>
The syntax for creating a private temporary is similar to the global temporary tables.
The ON COMMIT DROP DEFINITION
clause, the default, indicates the table should be dropped at the end of the transaction, or the end of the session.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT DROP DEFINITION; -- Insert, but don't commit, then check contents of PTT. INSERT INTO ora$ptt_temp_table VALUES (1, 'ONE'); SELECT COUNT(*) FROM ora$ptt_temp_table; COUNT(*) ---------- 1 SQL> -- Commit and check contents. COMMIT; SELECT COUNT(*) FROM ora$ptt_temp_table; SELECT COUNT(*) FROM ora$ptt_temp_table; * ERROR at line 1: ORA-00942: table or view does not exist SQL>
In contrast, the ON COMMIT PRESERVE DEFINITION
clause indicates the table and any data should persist beyond the end of the transaction. The table will be dropped at the end of the session.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT PRESERVE DEFINITION; -- Insert, but don't commit, then check contents of PTT. INSERT INTO ora$ptt_temp_table VALUES (1, 'ONE'); SELECT COUNT(*) FROM ora$ptt_temp_table; COUNT(*) ---------- 1 SQL> -- Commit and check contents. COMMIT; SELECT COUNT(*) FROM ora$ptt_temp_table; COUNT(*) ---------- 1 SQL> -- Reconnect and check contents of PTT. CONN scott/tiger@oradb SELECT COUNT(*) FROM ora$ptt_temp_table; SELECT COUNT(*) FROM ora$ptt_temp_table * ERROR at line 1: ORA-00942: table or view does not exist SQL>
The above output shows the table persists beyond the commit, but is dropped when we disconnect and create a new session.
It doesn't make sense for a permanent PL/SQL object to directly reference a temporary object, as it would not exist at compile time. If you want to use a private temporary table from a permanent object it would have to be done using dynamic SQL. The following example creates a stored function which uses a private temporary table.
CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER) RETURN VARCHAR2 AS l_sql VARCHAR2(32767); l_return VARCHAR2(30); BEGIN l_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT DROP DEFINITION'; EXECUTE IMMEDIATE l_sql; EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_temp_table VALUES (1, 'ONE')]'; EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_temp_table WHERE id = :id' INTO l_return USING p_id; RETURN l_return; END; /
The function works as expected.
SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.put_line('ptt_test(1) = ' || ptt_test(1)); END; / ptt_test(1) = ONE PL/SQL procedure successfully completed. SQL>
Private temporary tables are memory-based, so there is no metadata recorded in the data dictionary. As a result you can't use the USER_TABLES
view to display the list of private temporary tables in the current session. The following views are available to display information about private temporary tables.
Private temporary tables share the limitations of global temporary tables (see here), but there are also additional restrictions.
PRIVATE_TEMP_TABLE_PREFIX
initialisation parameter. The default is "ORA$PTT_".If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.