SAS/ACCESS Interface to Oracle

Temporary Table Support for Oracle

For general information about this feature, see Temporary Table Support for SAS/ACCESS.

A temporary table in Oracle persists just like a regular table, but contains either session-specific or transaction-specific data. Whether the data is session- or transaction-specific is determined by what is specified with the ON COMMIT keyword when you create the temporary table.

In the SAS context, you must use the LIBNAME option, CONNECTION=SHARED, before data in a temporary table persists over procedure and DATA step boundaries. Without this option, the temporary table persists but the data within it does not.

For data to persist between explicit SQL pass-through boundaries, you must use the LIBNAME option, CONNECTION=GLOBAL.

If you have a SAS data set and you want to join it with an Oracle table to generate a report, the join normally occurs in SAS. However, using a temporary table you can also have the join occur on the Oracle server.

Syntax

Here is the syntax to create a temporary table for which the data is transaction-specific (default):

CREATE GLOBAL TEMPORARY TABLE table name ON COMMIT DELETE ROWS

Here is the syntax to create a temporary table for which the data is session-specific:

CREATE GLOBAL TEMPORARY TABLE table name ON COMMIT PRESERVE ROWS

Terminating a Temporary Table

You can drop a temporary table at any time, or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a singe connection.

Example

In the following example, a temporary table, TEMPTRANS, is created in Oracle to match the TRANS SAS data set, using the SQL pass-through facility:

proc sql;
   connect to oracle (user=scott pw=tiger path=oraclev9);
   execute (create global temporary table TEMPTRANS 
           (empid number, salary number)) by oracle;
quit;

libname ora oracle user=scott pw=tiger path=oracle9 connection=shared;

/* load the data from the TRANS table into the Oracle temporary table */
proc append base=ora.TEMPTRANS data=TRANS;
run;

proc sql;
/* do the join on the DBMS server */
   select lastname, firstname, salary from ora.EMPLOYEES T1, ora.TEMPTRANS  T2
          where  T1.empno=T2.empno;
quit;

Top of Page