global temp variable in stored procedure
614084Jan 10 2008 — edited Jan 10 2008I 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.