Hi Tom,
I am just trying to understand if the table function will work with collections.
Please find my below code.
Script:
create table setm_students (id number, name varchar2(10));
create table setm_students_target as select * from setm_students where 1=2;
create or replace package pkg_test is
TYPE ty_rec_students is record (id number, name varchar2(10));
TYPE ty_tb_ar_students is table of ty_rec_students index by pls_integer;
end;
/
declare
l_tb_ar_students pkg_test.ty_tb_ar_students;
l_tb_ar_students_dummy pkg_test.ty_tb_ar_students;
begin
l_tb_ar_students := pkg_test.ty_tb_ar_students(1=>pkg_test.ty_rec_students(1, 'mike'),
2=>pkg_test.ty_rec_students(2, 'John'),
3=>pkg_test.ty_rec_students(3, 'Sara'),
4=>pkg_test.ty_rec_students(4, 'Tim'),
5=>pkg_test.ty_rec_students(5, 'Brian'));
dbms_output.put_line('Collection count normal '||l_tb_ar_students.count);
begin
select * bulk collect into l_tb_ar_students_dummy --this select from tbl works
from table(l_tb_ar_students);
dbms_output.put_line('Collection count test'||l_tb_ar_students_dummy.count);
/*merge into setm_students_target tar
using (select * from table(l_tb_ar_students)) src --does not work
on (src.id = tar.id)
when not matched then
insert values (src.id, src.name); */
exception
WHEN OTHERS THEN
dbms_output.put_line('ALERT '||SQLERRM||' TRACE '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
end;
end;
/
The select statement from table function works.
The merge using the table function throws 'invalid data type' error. Is This because the table function used is sql and that i have not collected it into a local collection?
Kindly let me know, how to use table function using collections in MERGE statement.
Kindly pardon my ignorance.
Thanks in advance!!
You can't use PL/SQL arrays as table functions in non-query DML (insert/update/delete/merge). You'll need to use object types instead:
create or replace type ty_rec_students as object (
id number, name varchar2(10)
);
/
create or replace type ty_tb_ar_students
is table of ty_rec_students;
/
declare
l_tb_ar_students ty_tb_ar_students;
begin
l_tb_ar_students := ty_tb_ar_students (
ty_rec_students(1, 'mike'),
ty_rec_students(2, 'John'),
ty_rec_students(3, 'Sara'),
ty_rec_students(4, 'Tim'),
ty_rec_students(5, 'Brian')
);
dbms_output.put_line('Collection count normal '||l_tb_ar_students.count);
merge into setm_students_target tar
using (
select * from table(l_tb_ar_students)
) src
on (src.id = tar.id)
when not matched then
insert values (src.id, src.name);
end;
/
select * from setm_students_target;
ID NAME
1 mike
4 Tim
2 John
3 Sara
5 Brian