oracle 创建会话表,oracle临时会话表在存储过程中的使用

本帖最后由 zeh368 于 2015-9-18 18:55 编辑

问题：

在将db2的存储过程转换为oracle的过程中，涉及到临时会话表，会出现问题：具体描述：

原db2中的存储过程涉及临时会话表的部分：

--创建临时会话表

DECLARE GLOBAL TEMPORARY TABLE COL_NAMES(

id integer,

COLNAMES varchar(50)

)WITH REPLACE NOT LOGGED on commit preserve ROWS ;

DELETE FROM SESSION.COL_NAMES;

commit;

--向临时会话表中插入数据

INSERT INTO SESSION.COL_NAMES values(1, 'ARRIVAL');

INSERT INTO SESSION.COL_NAMES values(2, 'SORTOUT');

INSERT INTO SESSION.COL_NAMES values(3, 'RBACK');

INSERT INTO SESSION.COL_NAMES values(4, 'TRANSOUT');

INSERT INTO SESSION.COL_NAMES values(5, 'TRANSBACK');

commit;

--游标中用到临时会话表

FOR c2 AS loop2 CURSOR WITH HOLD FOR

SELECT ID,COLNAMES FROM SESSION.COL_NAMES ORDER BY id WITH ur

DO

execute immediate replace(v_exec_0,'ARRIVAL',c2.COLNAMES);

END FOR;

在oracle中进行转换：

CREATE OR REPLACE PROCEDURE K_TJBS_D IS

--创建游标，使用到了临时会话表

CURSOR csr3 is

SELECT ID,COLNAMES FROM COL_NAMES ORDER BY id ;

BEGIN

--创建临时会话表

temp_tab_str := 'create GLOBAL TEMPORARY TABLE COL_NAMES(id integer, COLNAMES varchar2(50)) on commit preserve ROWS ';

execute immediate temp_tab_str;

DELETE FROM COL_NAMES;

commit;

--向临时会话表中插入数据

INSERT INTO COL_NAMES values(1, 'ARRIVAL');

INSERT INTO COL_NAMES values(2, 'SORTOUT');

INSERT INTO COL_NAMES values(3, 'RBACK');

INSERT INTO COL_NAMES values(4, 'TRANSOUT');

INSERT INTO COL_NAMES values(5, 'TRANSBACK');

commit;

--使用游标

FOR c1 in csr3 loop

execute immediate replace(v_exec_0,'ARRIVAL',c2.COLNAMES);

END loop;

这样转换，编译不会通过，会在创建游标csr3的时候报：表或视图COL_NAMES不存在的错误，oracle的版本为11g

请问：在这种情况下，oracle应该中如何转换，多谢！