RE: Temporary tables inside procedure
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Jul 2006 12:54:40 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
