Oracle Database Discussions

Hi,

I am trying to create a global temporary table inside a procedure and after doing some work I want to drop it. But it is throwing an error like below :
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at "E2E_TEST.TEST_GLOBAL", line 19
ORA-06512: at line 2
I am using the follwoing code :
CREATE OR REPLACE procedure test_global
as
v_num number(10);
v_smt varchar2(300);
begin
execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
on commit preserve rows
as
select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
where dept.DEPTNO = dept1.DEPTNO (+)';
v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';
execute immediate v_smt into v_num;
dbms_output.put_line(v_num);
execute immediate 'drop table RECON_TEST cascade constraints purge';
end;
/
But It I write the code like below :
CREATE OR REPLACE procedure test_global
as
v_num number(10);
v_smt varchar2(300);
begin
execute immediate 'CREATE GLOBAL TEMPORARY TABLE RECON_TEST
as
select dept.deptno src_pk,dept1.deptno trg_pk from dept,dept1
where dept.DEPTNO = dept1.DEPTNO (+)';
v_smt := 'select src_pk from RECON_TEST where rownum < 2 ';
execute immediate v_smt into v_num;
dbms_output.put_line(v_num);
execute immediate 'drop table RECON_TEST cascade constraints purge';
end;
/
Then it throwing an error :
NO DATA FOUND.

Can any one please suggest any work around.

Regards,
Koushik