Database Reference
In-Depth Information
In the following examples, we execute from two sessions and show how
temporary tables function in a session-specific manner. These examples use
two sessions by opening up a second SQL*Plus Worksheet instance, which
is necessary to show how temporary tables function.
In the first SQL*Plus Worksheet, the following script is executed. (If
you already have a table called TEMP, drop that table first by typing the
command DROP TABLE TEMP;.) See the result of the script in Figure
18.8.
CREATE GLOBAL TEMPORARY TABLE temp (col1 number)
ON COMMIT PRESERVE ROWS;
INSERT INTO TEMP(col1) VALUES(1);
INSERT INTO TEMP(col1) VALUES(2);
INSERT INTO TEMP(col1) VALUES(3);
COMMIT;
SELECT * FROM TEMP;
We have created a temporary table named TEMP with one column
called COL1. By using the ON COMMIT PRESERVE ROWS clause
when creating the table, we tell the database that we want rows inserted or
updated to remain in place after issuing a COMMIT command. The
default setting for temporary tables is ON COMMIT DELETE ROWS,
which deletes all of a session's rows whenever a COMMIT command is exe-
cuted. Next we inserted three rows into the table with column values of 1,
2, and 3. Then the transaction was completed using the COMMIT com-
mand. Finally the TEMP table was queried displaying the three rows
inserted, as shown in Figure 18.8.
In the second SQL*Plus Worksheet instance, we execute the second
script as shown following . See the result in Figure 18.9.
INSERT INTO TEMP(col1) VALUES(1);
INSERT INTO TEMP(col1) VALUES(4);
INSERT INTO TEMP(col1) VALUES(5);
INSERT INTO TEMP(col1) VALUES(6);
COMMIT;
SELECT * FROM TEMP;
We do not create another temporary table in the second session. Instead,
we use the temporary table created in the first session. Because we are
