Re: Create #TempTable in PL/SQL?
Date: 1996/08/06
Message-ID: <4u8e9q$118_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <3206686F.46B9_at_msn.com>, Roderick Prince <rprince_at_msn.com> writes:
|> Been tasked with converting MS/Sybase SQLServer stored procedures to
|> Oracle... Oh yea, fairly fluent in SQL but not at all wrt stored proc's
|> of any type...
|>
|> Under SQLServer they often create temporary tables inside a stored
|> procedure via a statement like
|> SELECT * INTO #Orders
|> FROM Orders
|> WHERE CustomerID = 12345;
|>
|> They do some massaging of the data...
|> ... and finally select a subset of it prior to the end of the procedure.
|>
|> Being a pin-head I figure I can execute some DDL via DBMS_SQL in my
|> stored procedure to create the tables and everything would be hunky
|> dory. Question is - how big a pin-head am I?
|>
|> Seriously though,
|> How do I create temporary tables?
|> Are they persistent beyond the current transaction? Is that the
|> stored procedures transaction or the calling applications?
|> Are they created for each user or are they created on behalf of
|> the owner of the procedure and everyone shares!
|>
Oracle doesn't really have the concept of a temporary table, at least not the way that SQLServer does. You could certainly create tables with DBMS_SQL, but they would be regular tables. In other words, the create statement would issue an implicit commit, it would be owned by the owner of the stored procedure which called it, etc. They would exist until they were dropped.
Having said that, how can you implement this? You could create the tables beforehand, and just insert into them and delete from them. They wouldn't be temporary. Or you could recode the procedure to use PL/SQL tables instead - these are stored in memory. A PL/SQL table is not a database table - you access it like an array rather than with SQL.
|>
|> Any and all pointers on this are greatly appreciated.
|>
|> Happy Trails,
|>
|> Roderick...
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Tue Aug 06 1996 - 00:00:00 CEST