5
Migrating Temporary Tables to Oracle

Temporary tables are available in Oracle9i and Oracle8i. However, because Oracle9i and Oracle8i temporary tables differ from Microsoft SQL Server temporary tables you should still replace or emulate temporary tables within Oracle to ease migrations from Microsoft SQL Server.

The emulation of temporary tables has been simplified by using temporary tables instead of permanent tables. See the Oracle9i and Oracle8i temporary table syntax for Example 2 in the Implementation of Temporary Tables as Permanent Tables section.

This chapter discusses temporary tables under the following headings:

Temporary Table Usage

In Microsoft SQL Server and Sybase Adaptive Server, temporary tables are used to:

Simplify Coding

Instead of writing complicated multi-table join queries, temporary tables allow a query to be broken into different queries, where result sets of one query are stored in a temporary table and subsequent queries join this temporary table with actual database tables.

This type of code can be converted to Oracle as follows:

Rewrite the queries to use multi-table joins
Create permanent temporary tables
Tune the complicated query using the parallel query option

Microsoft SQL Server and Sybase Adaptive Server:

        WHILE @cur_dt > @start_dt
        BEGIN
        INSERT #TEMP1
                SELECT @cur_dt
                SELECT @cur_dt = dateadd(dd, -7, @cur_dt)    
        END
        /******** create a temp table *****/
        INSERT #TEMP2
        SELECT t2.col1,
                        t4.col2,
                        " ", 
                        t5.col3,
                   t2.col4,
                        t3.col5, 
                        t2.col6,
                        t2.col7,
                        t4.col8,
                        t4.col9
        FROM 
                db1..TABLE1 t1,
                db2..TABLE2 t2,
                db2..TABLE3 t3,
                db2..TABLE4 t4,
                db1..TABLE5 t5
        WHERE   t1.col10 =@col10
        AND     t1.col11 = @flag1
        AND             t1.col2 = t4.col2
        AND             t1.col2 = t5.col2
        AND             t2.col4 between @start_col4 and @end_col4
        AND             t3.col5 between @start_col5 and @end_col5
        AND             t3.col12 = @flag2
        AND             t2.col13 = @flag1
        AND             t4.col2 like @col2
        AND             t4.col14 = @flag3
        AND             t4.col12 = @flag2
        AND             t2.col1 = t4.col1
        AND             t3.col1 = t2.col1
        AND             t4.col1 = t3.col1
        AND             t5.col2 like @col2
        AND             t4.col2 = t5.col2
        AND             t4.col15 = t5.col15
        AND             t5.col3 like @var1 
        AND             t2.col6 <= @end_dt 
        AND             (t2.col7 >= @start_dt OR t2.col7 = NULL)
        AND     t4.col8 <=@end_dt 
        UPDATE TABLE4
        SET     t4.col2 = col16
        FROM    #TEMP2 t1, db2..TABLE4 t4
        WHERE   t1.col1 = t4.col1
        AND             t4.col12 = @flag2
        AND             t4.col14 = @flag4

Oracle Pseudo Code:

        Use a PL/SQL table to simulate #TEMP1
        For the INSERT #TEMP2 statement
        Declare a cursor with the same SELECT statement 
                (as used in Microsoft SQL Server and Sybase Adaptive Server)
        For the UPDATE statement do the following:
        loop
                fetch the cursor
                if cursor not found 
                   then exit ;
                end if ;                        
                -- update TABLE4 for each row that matches the criteria
                -- Note : i_col17 and i_col1 are local PL/SQL variables
                                        which are populated by each fetch
                UPDATE TABLE4
                SET     col2 = i_col17
                WHERE   col1 = i_col1
                AND             col12 = @flag2
                AND             col14 = @flag4
        end loop

Simulate Cursors when Processing Data from Multiple Tables

Oracle supports cursors, so this type of code can be converted to Oracle using cursors.

The following code is part of a procedure written in Microsoft SQL Server. Compare it with the Oracle example (much simpler coding) that performs the same function.

Microsoft SQL Server:


        ...
        SELECT * INTO #emp FROM emp WHERE emp.dept = 10
        SELECT @cnt = @@rowcount
        WHILE @cnt > 0
        BEGIN
                SELECT @name = name, @emp_id = emp_id
                FROM #emp
                WHERE emp_id = (SELECT MAX (emp_id) FROM #emp)
                            /* process this row */
                DELETE FROM #emp WHERE emp_id = @emp_id
                SELECT @cnt = @cnt -1
        END
        ...

Oracle:

        FOR emp_rec IN (SELECT name, emp_id FROM emp WHERE dept = 10)
                LOOP  /*process emp_rec.name and emp_rec.emp_id*/
                END LOOP

Improve Performance In a Situation Where Multi-Table Joins are Needed

In Microsoft SQL Server and Sybase Adaptive Server, you sometimes use temporary tables to avoid multi-table joins. These cases can be converted to Oracle, as Oracle performs complex multi-table queries more efficiently than Microsoft SQL Server and Sybase Adaptive Server.

See the sample code provided in the To Simplify Coding section for more information in this regard.

Associate Rows from Multiple Queries in One Result Set (UNION)

Oracle provides a UNION relational operator to achieve similar results.

Microsoft SQL Server and Sybase Adaptive Server:

        INSERT #EMPL_TEMP
        SELECT emp.empno
                        dept.dept_no
                        location.location_code
                        emp.start_date
                        emp.end_date
        FROM    emp,
                        dept ,
                        location        
        WHERE   emp.empno = location.empno
        AND             dept.deptno = emp.deptno
        AND             dept.deptno = location.deptno 
        AND     emp.start_date BETWEEN @start_date AND @end_date 
        INSERT INTO #EMPL_TEMP VALUES ( 10000, 10, 15,getdate(),NULL ) 
        ...

Oracle:

        SELECT  emp.empno
                        dept.dept_no
                        location.location_code
                        emp.start_date
                        emp.end_date
        FROM    emp,
                        dept ,
                        location        
        WHERE   emp.empno = location.empno
        AND     dept.deptno = emp.deptno
        AND     dept.deptno = location.deptno 
        AND     emp.start_date BETWEEN i_start_date AND i_end_date 
        UNION
        SELECT  10000,
                        10, 
                        15,
                        SYSDATE,
                        NULL
        FROM    DUAL

Eliminate Re-Querying Data Needed for Joins

Permanent tables can be created in Oracle to hold the data. The data in these tables can be deleted at the end of processing. If no COMMIT is performed and no DDL is issued, the records in these tables are not recorded in the database. If a COMMIT is performed, the records from these tables can be deleted at the end of the process. Records in these tables can be kept separate for different users by having an additional column that holds a SESSION_ID.

If it is not possible to create the tables ahead of time, tables can be created dynamically with Oracle, using the DBMS_SQL package. In dynamically-created tables, the extra SESSION_ID columns are no longer needed, and space management issues such as fragmentation are eliminated. Performance may be affected, but deleting a large number of rows from a permanent temporary table also affects performance. In dynamic SQL, tables can be truncated or dropped.

Microsoft SQL Server and Sybase Adaptive Server:

        INSERT #EMPL_TEMP
        SELECT  emp.empno
                        dept.dept_no
                        emp.start_date
                        emp.end_date
        FROM    emp,
                        dept ,
        WHERE   emp.empno = dept.deptno
        AND     emp.start_date BETWEEN @start_date AND @end_date 
        ....
        ....
        /* Later in the code, one needs to select from the temp table
           only, it is not necessary to do a join of EMP and DEPT */
        SELECT  * FROM  #EMPL_TEMP

Oracle:

        SELECT  emp.empno
                        dept.dept_no
                        emp.start_date
                        emp.end_date
        FROM    emp,
                        dept ,
        WHERE   emp.empno = dept.deptno
        AND     emp.start_date BETWEEN i_start_date AND i_end_date  ;
        /* The above join has to be performed every time one needs to get this result set    
*/

Consolidate the Data for Decision Support Data Requirements

You often need to consolidate data across servers in a distributed database environment. You can use predefined views to consolidate this type of data. Oracle snapshots can replicate the data from remote databases. In addition, you can create permanent tables for Microsoft SQL Server and Sybase Adaptive Server temporary tables if queries need to perform joins against these tables.

Replace Temporary Tables

You should replace temporary tables to give the best performance in Oracle. You should always try to replace temporary tables with standard Oracle SQL. To do this, you must first determine the function of the temporary table. The function of the temporary table is one of the following:

To store an intermediate result
To collect data

Emulate Temporary Tables

If it is not possible to replace temporary tables, you should emulate them as follows:

Use PL/SQL tables to emulate temporary tables
Create temporary tables as ordinary tables whenever they are needed.
Create permanent tables and maintain them for multiple users.

Implementation as PL/SQL Tables

Temporary tables can be implemented as a PL/SQL table of records. Although this concept is quite appealing, you cannot use SQL on a PL/SQL table. Therefore, this concept is limited to simple uses of temporary tables. However, for simple uses of temporary tables, you should always consider replacing these temporary tables completely with standard SQL.

Implications of Creating Temporary Tables Dynamically

Since temporary tables can be created by any session "on the fly", you may have multiple instances of the same temporary table within one schema. As this type of multiple instance is not possible in Oracle, you should attach the SESSION_ID to the table name to make it unique. The result is a variable table name, which requires that all accesses to that table must be created with dynamic SQL. This process would complicate all types of migration tools.

As all DDL operations have an implicit commit, the creation of a temporary table would disturb the transactional behavior of the migrated application. The programs would have to be changed so that the creation of a temporary table always occurs at the start of a transaction. This process would also complicate migration tools.

Implications of Creating Permanent Tables

Currently, several users can share one table. Therefore, you need to maintain an additional column in the table for the SESSION_ID. As the SESSION_ID is unique in the lifetime of a database, there are no access conflicts. The enforcement of the SESSION_ID can be accomplished with a view and a trigger. The cleanup in this option may be slower, as you must now delete rows and cannot do a simple DROP TABLE. You can execute this operation asynchronously with the JOBQUEUE package, or use the TRUNCATE TABLE command whenever you are the only user of the table. To avoid bottlenecks on the temporary tables, it is possible to create multiple incarnations of them and point the users via private synonyms. Also, the upcoming SQL3 Standard implements temporary tables as permanent tables, which have an incarnation per session.

These arguments show that the permanent table option is the best choice.

Implementation of Temporary Tables as Permanent Tables

The migration utility must first extract from the source database code all commands that create a temporary table.

The following Microsoft SQL Server and Sybase Adaptive Server T-SQL examples illustrate two types of commands that create temporary tables:

Example 1

CREATE TEMP TABLE tmpdate(
FromDt datetime year to minute,
ToDt datetime year to minute);

Example 2


SELECT aaufromdt date
from anforord aau, order ord, case cas, casetype ctp
where ctp.ctp_id  = CtpId
and ctp.ctpambukz = "N"
and cas.ctp_id    =  ctp.ctp_id
and ord.cas_id    = cas.cas_id
and aau.ord_id    = ord.ord_id
and cas.casgtg    = "Y"
and ordstozt is null
INTO temp tmpfromdate;

You should modify all commands that create temporary tables as follows:

Change the syntax to Oracle syntax.
Identify and substitute alternative values for Oracle reserved words.
Prefix the name of the temporary table with t_.

When you have completed these steps, Example 1 type statements may be executed.

For statements of the same type as Example 2, you must also perform the following steps:

Remove all bind variables, such as CtpId, and replace them with constants.

Embed the statement in the following wrapper and execute it:

create table t_<temptable>
as select * 
from (<original statement>)
where 1=0; -- or similar logic to create the table without any rows

The complete Oracle code for Example 2 is as follows:

create table t_tmpfromdate
as select * from
(
SELECT aaufromdt inf_date 
from anforord aau, order ord, case cas, casetype ctp
where ctp.ctp_id  = 'X' -- CtpId
and ctp.ctpambukz = 'N'
and cas.ctp_id    =  ctp.ctp_id
and ord.cas_id    = cas.cas_id
and aau.ord_id    = ord.ord_id
and cas.casgtg    = 'Y'
and ordstozt is null)
where 0=1;

Oracle9i and Oracle8i Temporary Tables

Oracle9i and Oracle8i temporary table data is not visible across sessions so the SESSION_ID column is not required.

The Oracle9i and Oracle8i temporary table syntax for Example 2 is as follows:

create table global temporary t_<temptable> on commit preserve rows
as select * from (<original statement>)
where 1=0

The Migration Workbench does the following when it encounters a temporary table in a stored procedure or trigger:

Generates the DDL to create the table
Renames the table to t_tmpfromdate
Checks the column names for reserved words
Adds the SESSION_ID column (if Oracle9i and Oracle8i temporary tables are not being used)

With this setup, you can use the table tmpfromdate as if it is available once per session.

Maintenance of Temporary Tables

To maintain the temporary tables, you need a dictionary table t_table_catalog (see Definition of t_table_catalog) and the supporting package t_t_table (see Package Body t_table). The t_table package performs all maintenance for temporary tables. To generate it, you need the following grants:


grant select on v_$session to <xxx>;
grant execute on dbms_sql to <xxx>;
grant execute on dbms_lock to <xxx>;
grant create public synonym to <xxx>;
grant create view to <xxx>;
grant create trigger to <xxx>;

The available functionality is explained in the comments of the package t_table as follows:

create or replace PACKAGE t_table IS

        procedure convert_to_temp (table_name in varchar2, 
                                   use_dbms_output in boolean default 
false);
--
--      Convert an ordinary table to a temporary table.
--

        procedure register (table_name in varchar2);
--      Register the usage of temporary table in t_table_catalog
--      This procedure is called out of the pre-insert trigger
--      on the temporary table.
        procedure drop_t_table (table_name in varchar2);
--      Check usage in t_table_catalog, delete the data of the
--      session and unregister the table

        procedure cleanup_session;
--      Find all temporary table usages of the session, delete or truncate 
--      the temporary table and unregister the usage.
--      This procedure commits!

END;

Definition of t_table_catalog


create table t_table_catalog
(session_id number,
 table_name varchar2(30),
 constraint t_table_catalog_pk
    primary key (session_id, table_name))

Package Body t_table

create or replace PACKAGE BODY t_table IS

                last_table      varchar2(30) := ;  -- Store the last used 
                                        -- object for the register procedure
--  The constant use_truncate enables the use of the truncate command on
--  temporary tables. Change it to false if that is not desired.
                use_truncate    constant boolean := true;

        procedure parse_sql (user_cursor in number, 
                                sql_text in varchar2) is
        begin
                dbms_sql.parse (user_cursor, sql_text, dbms_sql.v7);
        exception
           when others then
                raise_application_error (-20100, 'Parsing Error ' || 
                        to_char (sqlcode) || ' at ' || 
                        to_char (dbms_sql.last_error_position + 1) ||
                        ' starting with: ' || 
                        substr (sql_text, dbms_sql.last_error_position + 1, 30) 
||
                        '...', true);
        end;

        procedure execute_sql (sql_text in varchar2) is
                ignore  number;
                user_cursor     number;
        begin
                user_cursor := dbms_sql.open_cursor;
                parse_sql (user_cursor, sql_text);
                ignore := dbms_sql.execute (user_cursor);
                dbms_sql.close_cursor(user_cursor);
        exception
                when others then
                        if dbms_sql.is_open(user_cursor) then
                                dbms_sql.close_cursor(user_cursor);
                        end if;
                        raise;
        end;

        function get_lock_id (object_name in varchar2)
--
-- This function returns the lock_id for a specific object.
-- It is calculated as the object_id from oracle + 1000000
--
        return number is
                object_number   number;
        begin
                select object_id
                into   object_number
                from   user_objects uo
                where  uo.object_name = get_lock_id.object_name
                and    uo.object_type = 'VIEW';
                return object_number + 1000000;
        exception
--  Object not found ==> Raise error
           when no_data_found then
                raise_application_error (-20100, 'Object ' || 
                                        object_name || ' does not exists');
        end;


        procedure convert_to_temp (table_name in varchar2, 
                                   use_dbms_output in boolean default false) is
--
--      Convert an ordinary table to a temporary table.
--
                sql_stmt        varchar2 (32000);
                col_sep         varchar2 (2) := null;
                con_list        varchar2 (100) := 'session_id';
                sel_table       varchar2 (30);
                procedure add (s in varchar2)
                is
--  Print one line of SQL code on sql_stmt or dbms_output
                begin
                        if use_dbms_output then
                                dbms_output.put_line (chr (9) || s);
                        else
                                sql_stmt := sql_stmt || chr (10) || s;
                        end if;
                end add;
                procedure execute_immediate
                as
                begin
                    if ( use_dbms_output ) then
                        dbms_output.put_line( '/' );
                    else
                        execute_sql (sql_stmt);
                        dbms_output.put_line(
                            substr( sql_stmt, 2, instr( sql_stmt,chr(10),2)-2 ) 
);
                        sql_stmt := NULL;
                    end if;
                end;
        begin
                if ( use_dbms_output ) then
                        sel_table := upper (table_name);
                else
                        sel_table := 't_' || upper (table_name);
                end if;
-- Rename the table to t_XXX


                add ('rename ' || table_name);
                add ('to t_' || table_name);
                execute_immediate;
-- In the next step we need to add the support for the sessionid column. 
-- The column is added with the following statement:
                add ('alter table t_' || table_name);
                add ('add session_id number not null');
                execute_immediate;
-- Create a view for the original table
                add ('create view ' || table_name);
                add ('as select ');
                for col_rec in 
                        (select column_name, table_name
                        from user_tab_columns
                        where table_name = sel_table
                        and   column_name != 'SESSION_ID'
                        order by column_id) loop
                        add (col_sep || col_rec.column_name);
                        col_sep := ', ';
                end loop;
                add (' from t_' || table_name);
                add ('where session_id = userenv (''sessionid'')');
                execute_immediate;
-- To allow public access we need to create a public synonym and 
-- grant public access.
                add ('create public synonym ' || table_name);
                add ('for ' || table_name);
                execute_immediate;
                add ('grant select, insert, update, delete');
                add ('on ' || table_name);
                add ('to public');
                execute_immediate;
-- To maintain the session_id information a pre-insert - per row trigger 
-- is created.
                add ('create trigger t_' || table_name || '_bir');
                add ('before insert');
                add ('on t_' || table_name);
                add ('for each row');
                add ('begin');
                add ('  :new.session_id := userenv (''sessionid'');');
                add ('end;');
                execute_immediate;
-- To register the usage of a temporary table for a specific session. 
-- The procedure register has to be called in a pre-insert - 
-- per statement trigger.
                add ('create trigger t_' || table_name || '_bis');
                add ('before insert');
                add ('on t_' || table_name);
                add ('begin');
                add ('  t_table.register (''' || upper (table_name) || 
                        ''');');
                add ('end;');
                execute_immediate;
        end;

        procedure register (table_name in varchar2)is
--
--      Register the usage of temporary table in t_table_catalog
--      This procedure may be called out of the pre-insert trigger
--      on the temporary table.
--
                dummy           varchar2(1);
                return_value    number;
                lock_id         number;
        begin
--  Check if we just registered the table
                if last_table = table_name then
                   return;
                end if;
                last_table := table_name;
-- Check if we have ever registered the table for our session
                begin
                        select 'x' into dummy
                        from   t_table_catalog ttc
                        where  ttc.table_name = register.table_name
                        and    session_id = userenv ('sessionid');
                exception
                   when no_data_found then
-- If it is not registered, register the usage 

                        insert into t_table_catalog
                        values (userenv ('sessionid'), table_name);
-- and put out the share lock with a timeout of 5 seconds
                        if use_truncate then
                                lock_id := get_lock_id (table_name);
                                return_value := 
                                dbms_lock.request (lock_id, 
                                                        dbms_lock.s_mode, 5, 
FALSE);
                                if return_value not in (0, 4) then
                                        raise_application_error (-20100, 
                                        'Unknown Error in DBMS_LOCK: ' || 
                                        to_char (return_value));
                                end if;
                        end if;
                end;
        end;

5 Migrating Temporary Tables to Oracle

Temporary Table Usage

Simplify Coding

Microsoft SQL Server and Sybase Adaptive Server:

Oracle Pseudo Code:

Simulate Cursors when Processing Data from Multiple Tables

Microsoft SQL Server:

Oracle:

Improve Performance In a Situation Where Multi-Table Joins are Needed

Associate Rows from Multiple Queries in One Result Set (UNION)

Microsoft SQL Server and Sybase Adaptive Server:

Oracle:

Eliminate Re-Querying Data Needed for Joins

Microsoft SQL Server and Sybase Adaptive Server:

Oracle:

Consolidate the Data for Decision Support Data Requirements

Replace Temporary Tables

Emulate Temporary Tables

Implementation as PL/SQL Tables

Implications of Creating Temporary Tables Dynamically

Implications of Creating Permanent Tables

Implementation of Temporary Tables as Permanent Tables

Maintenance of Temporary Tables

Definition of t_table_catalog

Package Body t_table

5
Migrating Temporary Tables to Oracle