RE: Temporary tables inside procedure

1- if you use temp tables in Oracle then defining the global temporary tables once and re-using the definition is the best way. If the data ceases to exist on commit you do not need the truncate otherwise the truncate is fine if the session may reuse the procedure.

2- you usually do not need temporary tables in Oracle. Many times #tables are used in SQL Server just because of how poorly it handles joins of 5 - 10 tables. In Oracle just do the join. Alternate features that can sometime replace a temporary table include reference cursors, piped row functions, and pl/sql collection structures.

Processes designed for one database often do not work well when ported as is, but rather should be re-examined and designed for the new database.

HTH -- Mark D Powell --  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Friday, July 28, 2006 12:04 PM
To: ORACLE-L
Subject: Temporary tables inside procedure

Hi,

We have to port sql server procedures to pl/sql, In some SQL Server procedures the temp table is created inside the procedure and populated with data and then truncated at the end, in oracle we can create global temporary tables outside procedure and use them but we are wondering if there is any other better way to do this in oracle.
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 28 2006 - 11:54:40 CDT