ORACLE：我们可以在存储过程中创建全局临时表或任何表吗？(ORACLE: can we create global temp tables or any tables in stored proc

ORACLE：我们可以在存储过程中创建全局临时表或任何表吗？(ORACLE: can we create global temp tables or any tables in stored proc?)

下面是我写的存储过程：

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;

当我执行它时，我收到一条错误消息，提到：

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.

我试图删除TEMP_TRAN，它说表不存在。 因此系统中不存在TEMP_TRAN表。 为什么我收到此错误？ 我正在使用TOAD来创建这个存储过程。

任何帮助将受到高度赞赏。

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.

最满意答案

全局临时表不是由存储过程“即时”创建的。 它们将像任何其他表一样永久地创建一次。 它是临时的数据，而不是表对象。

关于TEMP_TRAN，可能存在该名称的对象 ，但不是表格 ？ 尝试这个：

select * from all_objects where object_name = 'TEMP_TRAN';

Global temporary tables are not meant to be created "on the fly" by stored procedures. They are to be created once, permanently, like any other table. It is the data that is temporary, not the table object.

Regarding TEMP_TRAN, perhaps an object of that name exists, but is not a table? Try this:

select * from all_objects where object_name = 'TEMP_TRAN';