print Printer View e-mail E-mail this page Bookmark Bookmark
Purpose
To inform Migration Engineers of the correct procedure in creating dynamically-named temporary tables at runtime.
Scope & Application
The audience for this document is Oracle Customers, Oracle Partners and Migration Specialists doing MS SQL Server or Sybase Migrations.
Mapping Dynamically-Named Temporary Tables
When the name of a temporary table is constructed at runtime it is not possible to precreate the global temporary table DDL in advance.
Example:
        currenttablenumber = currenttablenumber+1;
        sql = “create table #tablename” + currenttablenumber

The solution is to create a global temporary table at runtime using the dynamic name.

The table below illustrates the required code changes.
Before After Comments
select * into
dynamicTableName from
fromTable

DROP dynamicTableName		 Create global temporary
table dynamicTableName
on commit preserve rows
as Select * from
fromTable where 1=0;

DELETE dynamicTableName

DROP TABLE dynamicTableName 		First we need to create
& auto-populate the new table




Drop the table
Note:
If it is determined that these dynamicaly-named temporary tables are being used in non-thread safe code or multiple people are executing the same code at the same time, then you must ensure that each thread or user receives its own temporary table name. This can be achieved by simply appending the session id (from the Oracle V$Session table) onto the table name.
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy