Created Temporary Tables Dynamically

1
votes

I have scenario like, I need to create a stored procedure in which the columns will come dynamically. Based upon this columns temporary tables should be created. Actually, this scenario I did in SQL Server and I have migrated the SQL Server database to Oracle.

Thanks

2
plsqloracle11g
you mean temporary tables in Oracle or nested tables in plSQL? - Thiyagu ATR
Temporary tables in oracle, but the columns will come dynamically... - user1990383
ya that's nor matter at all!hoe many columns?column number also vary?or...it is fixed? - Thiyagu ATR
rule of thumb #34: do not create temporary tables on the fly in Oracle - Kirill Leontev
Columns names and number of columns will vary when calling a procedure - user1990383

2 Answers

8
votes

If you want to create a table dynamically, you'd need to use dynamic SQL

BEGIN
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE table_name( col1 number, col2 number )';
END;

That being said, it is almost certainly a mistake to directly port SQL Server code that creates temporary tables to Oracle. It will not work the same way that your SQL Server code does.

  • First off, temporary tables in Oracle are visible to every session unlike SQL Server where they are local to the session. That means that if you have two sessions calling your stored procedure, they'd both try to create the temporary table and the second one would fail (things get worse assuming that you are also dropping the temporary table at the end of the procedure rather than letting thousands of temporary tables accumulate).
  • Creating tables is an inefficient process in Oracle given the amount of latching that is required. Oracle's assumption is that you are not creating objects on the fly. If your application violates that assumption, you will encounter some scalability issues.
  • If you create tables on the fly, you'll have to refer to them on the fly. So any queries against the temporary tables will also need to use dynamic SQL. That is less efficient than static SQL and it makes your code harder to write, harder to debug, and harder to maintain.
2
votes

Maybe I'm bit of-topic but try to analyze what are you really want to achieve. I've been working with several people who used T-TSQL and the patterns used in SQL server are different from Oracle ones. Simply do not try to exactly rewrite your code one to one. It will not work.

For example usage of temporary table is considered to be a bad design in Oracle(in most cases). In cases where you use temp tables in T-SQL you will usually use cursors in Oracle.

Also keep in mind that SQL and PL/SQL are compiled languages(it's not real scripting). You can not (re)create a table in a PL/SQL package and then use it in the some (or other package). When you modify an object in Oracle all the depending code has to be recompiled in background. This is a source of many frustrations for the people who come from SQL Server onto Oracle.

It's not worse, it's just more different than it seems to be.