SQL & PL/SQL

I want to create a temp table inside a stred procedure and make use of it . I want perform some delete statements based on select statemets .An I want to drop the table at the end .

When I tried to create a table inside the stored procedure using exxecute immediate statement ,.


sql_stmt := 'CREATE GLOBAL TEMPORARY TABLE pattern_str_temp as select * from pattern_structure';
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE pattern_str_temp as select * from woc_pattern_structure' ;

Then my select statements that contain this table do not identify the table name.


I got compilor error when I use it in hte stored procedure in the select statement .Then I did like this-


WHENEVER SQLERROR CONTINUE
/
DROP TABLE pattern_str_temp;
CREATE TABLE pattern_str_temp AS SELECT * FROM pattern_structure ;



COMMIT;

CREATE OR REPLACE PACKAGE BODY Woc_Delete_Model_Data
AS
/******************************************************************************
NAME: Woc_Delete_Model_Data
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 11/01/2008 gtutika 1. Deletes given Product Model
******************************************************************************/

---------------------------------------------------------------------------------------------------------------------
PROCEDURE deleteCategory(p_product_model IN varchar2,
p_request_status OUT VARCHAR2,
p_err_mesg OUT VARCHAR2
)
IS

l_category VARCHAR2(200);
l_count NUMBER;


CURSOR getAttribute IS
SELECT Category_Name
FROM
Woc_Attribute_Category
WHERE Attribute_Name in
(SELECT Child_Name FROM pattern_structure
WHERE Child_Type = 'Attribute' and product_Model_Name = p_product_model)
FOR UPDATE;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

--dbms_output.put_line('START-Inside DeleteCategory Procedure .........');
--sql_stmt := 'CREATE GLOBAL TEMPORARY TABLE pattern_str_temp as select * from pattern_structure';
--EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE pattern_str_temp as select * from pattern_structure' ;

OPEN getAttribute ;
LOOP
FETCH getAttribute INTO l_category ;
EXIT WHEN getAttribute%NOTFOUND;

l_count := Is_Category_Used(p_product_model , l_category);

IF (l_count=0) THEN

DELETE FROM WOC_ATTRIBUTE_CATEGORY where CATEGORY_NAME = l_category;

DELETE from woc_item_category
where CATEGORY_NAME = l_category;


DELETE FROM WOC_CATEGORY WHERE CATEGORY_NAME = l_category;

END IF;

END LOOP;


--(getAttribute%ROWCOUNT);
CLOSE getAttribute;

--dbms_output.put_line('END-Inside DeleteCategory Procedure .........');



EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
p_err_mesg := 'ERROR IN CUSOR';
--dbms_output.put_line('ERROR in CUSOR');
ROLLBACK;

END;
---------------------------------------------------------------------------------------------
FUNCTION Is_Category_Used(p_product_model IN varchar2 , p_category IN Varchar2)
RETURN NUMBER IS

l_count NUMBER;
l_attribute VARCHAR2(40);
l_pattern varchar2(30);
CURSOR getAttribute IS
SELECT attribute_Name from
WOC_ATTRIBUTE_CATEGORY WHERE category_name = p_category and Attribute_Name in
(Select Child_Name from pattern_str_temp
where child_type = 'Attribute' and product_Model_Name = p_product_model);


BEGIN

DBMS_OUTPUT.ENABLE(1000000);


SELECT count(*) into l_count from
WOC_ATTRIBUTE_CATEGORY WHERE category_name = p_category and Attribute_Name in
(Select Child_Name from pattern_str_temp
where child_type = 'Attribute' and product_Model_Name <> p_product_model);

OPEN getAttribute;
LOOP
FETCH getAttribute INTO l_attribute;
EXIT WHEN getAttribute%NOTFOUND;



DELETE FROM pattern_str_temp WHERE Product_Model_Name=p_product_model
and child_type = 'Attribute' and child_Name= l_attribute;

END LOOP;
CLOSE getAttribute;

RETURN l_count;


EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
--dbms_output.put_line('ERROR in CUSOR');
ROLLBACK;

END;
---------------------------------------------------------------------------------------------------------------------
PROCEDURE delete_batch_woc_model(p_product_model IN VARCHAR2,p_flag IN VARCHAR2,
p_err_mesg OUT VARCHAR2)
IS
p_request_status VARCHAR2(30);

BEGIN


deleteCategory(p_product_model,p_request_status ,p_err_mesg );

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
p_err_mesg := 'ERROR IN CUSOR';
dbms_output.put_line('ERROR in CUSOR');
ROLLBACK;

END;



END Woc_Delete_Model_Data;
--drop table pattern_str_temp ;

----------------------------------------------------------------------------------------
SHOW ERRORS;

But once the data is deleted , the data in the temp table is deleted when I load the data and try to delete it agian since I have no data in temp table ,the data is not deleted .So I need to create the temp table every time the stored procedure is called ,delete accordingly and drop the table at the end .

Please suggest how to do it.

Thanks.