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!

creating temporary table in pl/sql block problem

TakhteJamshidOct 13 2006 — edited Oct 13 2006
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 10 2006
Added on Oct 13 2006
5 comments
1,096 views