|Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations
Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT
Part Number A97248-01
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:
In Microsoft SQL Server and Sybase Adaptive Server, temporary tables are used to:
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:
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
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
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.
... 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 ...
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
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.
Oracle provides a UNION relational operator to achieve similar results.
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 ) ...
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
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.
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
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 */
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.
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:
If it is not possible to replace temporary tables, you should emulate them as follows:
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.
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.
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.
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:
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:
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:
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:
With this setup, you can use the table
tmpfromdate as if it is available once per session.
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;
create table t_table_catalog (session_id number, table_name varchar2(30), constraint t_table_catalog_pk primary key (session_id, table_name))
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;