Home » SQL & PL/SQL » SQL & PL/SQL » Droping Temporary Table
Droping Temporary Table [message #36185] Thu, 08 November 2001 20:42 Go to next message
Malli
Messages: 10
Registered: November 2001
Junior Member
Hi All!
I am using temporary tables in PL/SQL Function.
I am retrieving values of temporary table into Ref Cursor Type variable. If i drop the table in the same function after the above operation and when i execute the function it is giving error
"object no longer exists".
i have to drop the temporary table after my operation, then where i have to drop the table.
for example code is
------------------------------------------------------
Create Function fun
(
param1 Number
)
Return PackTest.ctype
as
rcur PackTest.ctype;
Col1Val Number;
Col2Val VARCHAR2(100);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempTab(col1 Number, Col2 VARCHAR2(100))';
Col1Val := 22;
Col2Val := 'ss';
EXECUTE IMMEDIATE 'INSERT INTO TempTab VALUES('||col1Val||','''||Col2Val||''')';

OPEN rcur FOR
'SELECT * FROM TempTab';

EXECUTE IMMEDIATE 'DROP TABLE TempTab';
RETURN rcur;
END;
-------------------------------------------------------
This is sample code of my function.
Pl help me in solving this problem about how i have to drop the table in this function or whether there is any type other than Ref Cursors to use for returning of number of rows of temporary table.

Tks in Advance
Malli

----------------------------------------------------------------------
Re: Droping Temporary Table [message #36199 is a reply to message #36185] Fri, 09 November 2001 08:01 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Malli, you should not be creating and dropping the GTT in your function. DDL is hugely expensive and a GTT should be created once and left there.

Your session can use it, and, depending on which option you use when you create it, the temporary data will be cleared when you commit or when your session ends.

This will also fix your 'object no longer exists' problem.

----------------------------------------------------------------------
Previous Topic: Try to execute operating system command - RPC Error
Next Topic: Row count
Goto Forum:
  


Current Time: Sat May 18 15:49:30 CDT 2024