Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Poornima.

Asked: February 08, 2019 - 1:37 pm UTC

Last updated: February 14, 2019 - 12:11 pm UTC

Version: 12C

Viewed 10K+ times! This question is

You Asked

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!!

and Chris said...

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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

neatly wrapped

Racer I., February 14, 2019 - 11:13 am UTC

Hi,

I'm partial to PIPELINED functions and package types (particularly using %ROWTYPE).

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_tb_ar_students is table of setm_students%ROWTYPE;
FUNCTION GetNewStudents RETURN ty_tb_ar_students PIPELINED;
end;
/
show errors;
/

create or replace package body pkg_test as
  SUBTYPE ty_rec_students IS setm_students%ROWTYPE;
  TYPE ty_tb_ia_students is table of ty_rec_students index by pls_integer;
  FUNCTION GetNewStudents RETURN ty_tb_ar_students PIPELINED
  IS
    l_tb_ia_students ty_tb_ia_students;
    l_rec_student ty_rec_students;
  BEGIN
    l_rec_student.id := 1; l_rec_student.name := 'Mike'; l_tb_ia_students(1) := l_rec_student;
    l_rec_student.id := 2; l_rec_student.name := 'John'; l_tb_ia_students(2) := l_rec_student;
    l_rec_student.id := 3; l_rec_student.name := 'Sara'; l_tb_ia_students(3) := l_rec_student;
    l_rec_student.id := 4; l_rec_student.name := 'Tim'; l_tb_ia_students(4) := l_rec_student;
    l_rec_student.id := 5; l_rec_student.name := 'Brian'; l_tb_ia_students(5) := l_rec_student;
    dbms_output.put_line('Collection count normal '||l_tb_ia_students.count);
    FOR i IN 1..l_tb_ia_students.COUNT LOOP
      PIPE ROW(l_tb_ia_students(i));
    END LOOP;
    RETURN;
  END;
end;
/
show errors;
/

select * from table(pkg_test.GetNewStudents);

merge into setm_students_target tar
  using (
    select * from table(pkg_test.GetNewStudents)
  ) src  
  on (src.id = tar.id)
  when not matched then
    insert values (src.id,  src.name); 
    
commit;

select * from setm_students_target;


regards,
Chris Saxon
February 14, 2019 - 12:11 pm UTC

Yep, that's another way around the problem.

Thank you

Poornima Ramasami, February 15, 2019 - 11:12 am UTC

Great. Thank you Chris and Racer!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library