ORACLE: can we create global temp tables or any tables in stored proc?
- by mrp
Hi,
below is the stored proc I wrote:
create or replace procedure test005
as
begin
CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/
INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate);
INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate);
INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate);
COMMIT;
end;
when i executed it , i get an error message mentioning:
create or replace procedure test005
as
begin
CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/
INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate);
INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate);
INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate);
COMMIT;
end;
Error at line 1
ORA-00955: name is already used by an existing object
Script Terminated on line 1.
I tried to drop the TEMP_TRAN and it says table doesn't exist. So there is no TEMP_TRAN table existed in system. why am I getting this error? I am using TOAD to create this stored proc.
Any help would be highly appreciated.
