| Temporary Tables inside a Package [message #38220] |
Tue, 02 April 2002 23:13  |
Bhagwan Singh
Messages: 23
Registered: December 2001
|
Junior Member |
|
|
Hi All,
Can someone help me on this.
Iam creating a procedure inside a package which returns a cursor object as output.Till here its fine.
Now.I want to create a temporary table inside a procedure which I will populate it from say 2 cursors[[for example]]. and finally I query on this temp table to send this cursor object as output.
has anyone tried this.
please suggest or send me a small snippet
regards
bhagwan
|
|
|
|
| Re: Temporary Tables inside a Package [message #38227 is a reply to message #38220] |
Wed, 03 April 2002 08:36  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You definitely do NOT want to perform DDL (create/drop table, etc.) in a procedure that will be called repetitively. DDL is highly expensive, in terms of performance, and should be avoided in a proc.
I would highly recommend creating the global temporary table outside of PL/SQL and then reference it (insert/delete/update) from within your procedure.
|
|
|
|
| Re: Temporary Tables inside a Package [message #38231 is a reply to message #38227] |
Wed, 03 April 2002 08:58 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Just remember the performance hit you are taking every time you run the procedure. Also, if this procedure can be run by multiple sessions simultaneously, you will have to come up with some unique name for the temporary table.
execute immediate 'create table ' || v_tablename || ' (c1 date, c2 varchar2(30))';
execute immediate 'drop table ' || v_tablename; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
