You Asked
Hi,
I have created below TABLE type of VARCHAR2 -
create or replace TYPE VC_ARRAY is table of varchar2(4000);
and tables
create table tb_test_src
as select object_name, object_id from user_objects where rownum <= 20;
create table tb_test_tgt
as select object_name, object_id from user_objects where rownum <= 10;
Please have look at below two procedures,
The only difference is I am executing MERGE command -
1. with EXECUTE IMMEDIATE (p_dummy_with_exe_imm)
2. without EXECUTE IMMEDIATE (p_dummy)
Procedure without EXECUTE IMMEDIATE i.e. p_dummy execute perfectly, but the one with with EXECUTE IMMEDIATE (i.e. p_dummy_with_exe_imm) throwing error as ORA-00904: "TB_ROWID": invalid identifier
Could you please help get rid of this.
CREATE OR REPLACE PROCEDURE p_dummy_with_exe_imm
IS
TYPE r_cursor IS REF CURSOR;
c_cur_var r_cursor;
tb_rowid VC_ARRAY;
n_row_updated pls_integer;
v_sql VARCHAR2(22222);
begin
open c_cur_var for select rowid from tb_test_src;
loop
FETCH c_cur_var BULK COLLECT INTO tb_rowid LIMIT 5;
EXIT WHEN tb_rowid.COUNT = 0;
v_sql := 'MERGE INTO tb_test_tgt tgt
USING ( SELECT * FROM tb_test_src WHERE ROWID IN (select column_value as name from table(cast(tb_rowid as VC_ARRAY)))) src
ON (src.object_name = tgt.object_name)
WHEN MATCHED THEN
UPDATE
SET tgt.object_id = src.object_id
WHEN NOT MATCHED THEN
INSERT (tgt.object_id, tgt.object_name)
VALUES (src.object_id, src.object_name)';
EXECUTE IMMEDIATE v_sql ;
n_row_updated := SQL%ROWCOUNT;
dbms_output.put_line('n_row_updated:'||n_row_updated);
tb_rowid.delete;
end loop;
end p_dummy_with_exe_imm;
/
---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE p_dummy
IS
TYPE r_cursor IS REF CURSOR;
c_cur_var r_cursor;
tb_rowid VC_ARRAY;
n_row_updated pls_integer;
v_sql VARCHAR2(22222);
begin
open c_cur_var for select rowid from tb_test_src;
loop
FETCH c_cur_var BULK COLLECT INTO tb_rowid LIMIT 5;
EXIT WHEN tb_rowid.COUNT = 0;
MERGE INTO tb_test_tgt tgt
USING ( SELECT * FROM tb_test_src WHERE ROWID IN (select column_value as name from table(cast(tb_rowid as VC_ARRAY)))) src
ON (src.object_name = tgt.object_name)
WHEN MATCHED THEN
UPDATE
SET tgt.object_id = src.object_id
WHEN NOT MATCHED THEN
INSERT (tgt.object_id, tgt.object_name)
VALUES (src.object_id, src.object_name);
n_row_updated := SQL%ROWCOUNT;
dbms_output.put_line('n_row_updated:'||n_row_updated);
tb_rowid.delete;
end loop;
end p_dummy;
/
and Connor said...
You simply need to bind the rowid array into the dynamic 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>