Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

global temp variable in stored procedure

614084Jan 10 2008 — edited Jan 10 2008
I need to declare a global temp variable in a stored procedure.. I am doing it this way because it is a distributed application and it is the only access to the oracle server I will have.. so I tried this syntax, and it gives me an error:

create or replace PROCEDURE usp_Select_HierarchyTest(results OUT SYS_REFCURSOR) AS
DECLARE
CREATE GLOBAL TEMPORARY TABLE tmp_sometemptable
(
--my rows
) ON COMMIT DELETE ROWS;
BEGIN
--Do Stuff
END usp_Select_HierarchyTest;


I can run this as a script, if I remove the stored procedure syntax, but it doesnt like the "DECLARE" statement.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2008
Added on Jan 10 2008
5 comments
476 views