creating temporary table in pl/sql block problem
hello
i have a problem in creating and using temporary table in pl/sql block
please verify below block
---------------------------------------------
begin
execute immediate 'create global temporary table alitemp1 (co_t varchar2(10),color varchar2(10))';
insert into alitemp1 (co_t,color) values ('001','red');
execute immediate 'DROP TABLE alitemp1';
end;
/
-----------------------------------------------------------------
when i execute that block i will receive this error
insert into alitemp1 (co_t,color) values ('001','red');
*
ERROR at line 3:
ORA-06550: line 3, column 14:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored
i think it because that alitemp1 table don't create
but two below block run fine
---------------------------------------
begin
execute immediate 'create global temporary table alitemp1 (co_t varchar2(10),color varchar2(10))';
execute immediate 'DROP TABLE alitemp1';
end;
/
---------------------------------------
---------------------------------------
begin
execute immediate 'create global temporary table alitemp1 (co_t varchar2(10),color varchar2(10))';
end;
/
select table_name from user_tables where table_name='ALITEMP1';
TABLE_NAME
----------------------
ALITEMP1
it means that problem is when the below line exists in block
insert into alitemp1 (co_t,color) values ('001','red');
if may please guide me