Oracle 强制转换表类型不使用立即执行

嗨，

我已经创建了下表类型的VARCHAR2-
创建或替换类型VC_ARRAY是varchar2的表 (4000);
和表格

创建表tb_test_src
从用户对象中选择对象名称，对象id，其中rownum <= 20;

创建表tb_test_tgt
从用户对象中选择对象名称，对象id，其中rownum <= 10;


请看下面两个程序，
唯一的区别是我正在执行合并命令-
1.使用立即执行 (p_dummy_with_exe_imm)
2.没有立即执行 (p_dummy)

没有立即执行的过程，即p_dummy完美执行，但是具有立即执行的过程 (即p_dummy_with_exe_imm) 抛出错误为ORA-00904: “TB_ROWID”: 无效标识符

你能帮忙把这个扔掉吗？

创建或替换过程p_dummy_with _ exe_imm
是
TYPE r_cursor 是 REF CURSOR;
c_cur_var r_cursor;
tb_rowid VC_ARRAY;
n_row_updated pls_integer;
v_sql VARCHAR2(22222);
开始

打开从tb_test_src中选择rowid的c_cur_var;
循环
将c_cur_var批量收集到tb_rowid限制5中;
当tb_rowid时退出。计数 = 0;

v_sql := '合并到tb_test_tgt tgt
使用 (从tb_test_src中选择 * 其中ROWID (从表中选择column_value作为名称 (强制转换 (tb_rowid作为VC_ARRAY) src
打开 (src.object_name = tgt.object_name)
当匹配时，然后
更新
设置tgt。对象id = src。对象id
当不匹配时
插入 (tgt.object_id，tgt.object_name)
值 (src.object_id，src.object_name)';

立即执行v_sql;
N _ row_updated := SQL % ROWCOUNT;
dbms_output.put_line('n _ row_updated:' | | N _ row_updated);
Tb_rowid.删除;

结束循环;

结束p_dummy_with _ex_imm;
/

-

创建或替换过程p_dummy
是
TYPE r_cursor 是 REF CURSOR;
c_cur_var r_cursor;
tb_rowid VC_ARRAY;
n_row_updated pls_integer;
v_sql VARCHAR2(22222);
开始

打开从tb_test_src中选择rowid的c_cur_var;
循环
将c_cur_var批量收集到tb_rowid限制5中;
当tb_rowid时退出。计数 = 0;

合并到tb_test_tgt tgt
使用 (从tb_test_src中选择 * 其中ROWID (从表中选择column_value作为名称 (强制转换 (tb_rowid作为VC_ARRAY) src
打开 (src.object_name = tgt.object_name)
当匹配时，然后
更新
设置tgt。对象id = src。对象id
当不匹配时
插入 (tgt.object_id，tgt.object_name)
值 (src.object_id，src.object_name);

N _ row_updated := SQL % ROWCOUNT;
dbms_output.put_line('n _ row_updated:' | | N _ row_updated);
Tb_rowid.删除;

结束循环;

结束p_dummy;
/

您只需要将rowid数组绑定到动态SQL中即可。

SQL> @drop tb_test_src

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL> @drop tb_test_tgt

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge

1 row selected.


Table dropped.

SQL>
SQL>
SQL> create or replace TYPE VC_ARRAY is table of varchar2(4000);
  2  /

Type created.

SQL>
SQL> create table tb_test_src
  2  as select object_name, object_id from user_objects where rownum <= 20;

Table created.

SQL>
SQL> create table tb_test_tgt
  2  as select object_name, object_id from user_objects where rownum <= 10;

Table created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE p_dummy_with_exe_imm
  2  IS
  3  TYPE r_cursor IS REF CURSOR;
  4  c_cur_var r_cursor;
  5  tb_rowid VC_ARRAY;
  6  n_row_updated pls_integer;
  7  v_sql VARCHAR2(22222);
  8  begin
  9  open c_cur_var for select rowid from tb_test_src;
 10  loop
 11  FETCH c_cur_var BULK COLLECT INTO tb_rowid LIMIT 5;
 12  EXIT WHEN tb_rowid.COUNT = 0;
 13  v_sql :=
 14  'MERGE INTO tb_test_tgt tgt
 15  USING ( SELECT * FROM tb_test_src WHERE ROWID IN (select column_value as name from table(cast(tb_rowid as VC_ARRAY)))) src
 16  ON (src.object_name = tgt.object_name)
 17  WHEN MATCHED THEN
 18  UPDATE
 19  SET tgt.object_id = src.object_id
 20  WHEN NOT MATCHED THEN
 21  INSERT (tgt.object_id, tgt.object_name)
 22  VALUES (src.object_id, src.object_name)';
 23
 24  EXECUTE IMMEDIATE v_sql ;
 25  n_row_updated := SQL%ROWCOUNT;
 26  dbms_output.put_line('n_row_updated:'||n_row_updated);
 27  tb_rowid.delete;
 28  end loop;
 29  end p_dummy_with_exe_imm;
 30  /

Procedure created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_dummy
  2  IS
  3  TYPE r_cursor IS REF CURSOR;
  4  c_cur_var r_cursor;
  5  tb_rowid VC_ARRAY;
  6  n_row_updated pls_integer;
  7  v_sql VARCHAR2(22222);
  8  begin
  9
 10  open c_cur_var for select rowid from tb_test_src;
 11  loop
 12  FETCH c_cur_var BULK COLLECT INTO tb_rowid LIMIT 5;
 13  EXIT WHEN tb_rowid.COUNT = 0;
 14
 15  MERGE INTO tb_test_tgt tgt
 16  USING ( SELECT * FROM tb_test_src WHERE ROWID IN (select column_value as name from table(cast(tb_rowid as VC_ARRAY)))) src
 17  ON (src.object_name = tgt.object_name)
 18  WHEN MATCHED THEN
 19  UPDATE
 20  SET tgt.object_id = src.object_id
 21  WHEN NOT MATCHED THEN
 22  INSERT (tgt.object_id, tgt.object_name)
 23  VALUES (src.object_id, src.object_name);
 24
 25  n_row_updated := SQL%ROWCOUNT;
 26  dbms_output.put_line('n_row_updated:'||n_row_updated);
 27  tb_rowid.delete;
 28
 29  end loop;
 30  end p_dummy;
 31  /

Procedure created.

SQL>
SQL> exec p_dummy

PL/SQL procedure successfully completed.

SQL>
SQL> exec p_dummy_with_exe_imm
BEGIN p_dummy_with_exe_imm; END;

*
ERROR at line 1:
ORA-00904: "TB_ROWID": invalid identifier
ORA-06512: at "MCDONAC.P_DUMMY_WITH_EXE_IMM", line 24
ORA-06512: at line 1


SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_dummy_with_exe_imm
  2  IS
  3  TYPE r_cursor IS REF CURSOR;
  4  c_cur_var r_cursor;
  5  tb_rowid VC_ARRAY;
  6  n_row_updated pls_integer;
  7  v_sql VARCHAR2(22222);
  8  begin
  9  open c_cur_var for select rowid from tb_test_src;
 10  loop
 11  FETCH c_cur_var BULK COLLECT INTO tb_rowid LIMIT 5;
 12  EXIT WHEN tb_rowid.COUNT = 0;
 13  v_sql :=
 14  'MERGE INTO tb_test_tgt tgt
 15  USING ( SELECT * FROM tb_test_src WHERE ROWID IN (select column_value as name from table(cast(:tb_rowid as VC_ARRAY)))) src
 16  ON (src.object_name = tgt.object_name)
 17  WHEN MATCHED THEN
 18  UPDATE
 19  SET tgt.object_id = src.object_id
 20  WHEN NOT MATCHED THEN
 21  INSERT (tgt.object_id, tgt.object_name)
 22  VALUES (src.object_id, src.object_name)';
 23
 24  EXECUTE IMMEDIATE v_sql using tb_rowid;
 25  n_row_updated := SQL%ROWCOUNT;
 26  dbms_output.put_line('n_row_updated:'||n_row_updated);
 27  tb_rowid.delete;
 28  end loop;
 29  end p_dummy_with_exe_imm;
 30  /

Procedure created.

SQL>
SQL> exec p_dummy_with_exe_imm

PL/SQL procedure successfully completed.

SQL>
复制