Using SQL object type in procedure and use them in pipeline function

Question

Here i am trying to use a Pipeline function which will take a Collection as input and return a collection after some validation which i use to insert data into a table;

Here are some test objects which i have created to explain my problem.

create table tst_cri_sdb (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));  


insert into TST_CRI_SDB values (100, to_date('13032019','ddmmyyyy'), 'EUR', 'STM');  
insert into TST_CRI_SDB values (101, to_date('14032019','ddmmyyyy'), 'GBP', 'CTM');  
insert into TST_CRI_SDB values (102, to_date('15032019','ddmmyyyy'), 'USD', 'STM');  
insert into TST_CRI_SDB values (103, to_date('16032019','ddmmyyyy'), 'INR', 'CTM');  
insert into TST_CRI_SDB values (104, to_date('17032019','ddmmyyyy'), 'EUR', 'STM');  


create type tst_rec as object (icri number, datesitu date, curr varchar2(3), ctmstm varchar2(10));  


create type tst_table_rec as table of tst_rec;  


create table sdb_gpcs (curr varchar2(3), ctmstm varchar2(5), goca number, cust_grp varchar2(30));  


insert into sdb_gpcs values ('EUR','CTM', 100345 ,'A1105');  
insert into sdb_gpcs values ('EUR','CTM', 200345 ,'A4405');  
insert into sdb_gpcs values ('EUR','STM', 300345 ,'A3305');  
insert into sdb_gpcs values ('USD','CTM', 500345 ,'A5505');  
insert into sdb_gpcs values ('USD','STM', 600345 ,'A6605');  
insert into sdb_gpcs values ('USD','STM', 700345 ,'A7705');  


select * from sdb_gpcs where curr = 'EUR' and ctmstm = 'CTM';  


create table tst_cri_plus_sdb (deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));  


create type tst_plus_rec as object(deal_id number, datesitu date, acc_code number, acca_cust_grp varchar2(10), curr varchar2(3), ctmstm varchar2(5));  


create type tst_plus_table_rec as table of tst_plus_rec;  


create or replace function get_plus_sdb_w ( p_tab IN tst_table_rec)  
return tst_plus_table_rec PIPELINED  
is  

l_rec tst_plus_rec;  

begin  

  for i in 1..p_tab.count  
  loop  

    for j in (select * from sdb_gpcs)  
    loop  

      l_rec := tst_plus_rec(p_tab(i).icri, p_tab(i).datesitu, j.goca, j.cust_grp ,p_tab(i).curr, p_tab(i).ctmstm);  

      PIPE row(l_rec);  

    end loop;  

  end loop;  

end;   


CREATE or replace procedure tst_insert  
is  

  cursor c1 is select * from tst_cri_sdb;  

  l_tab tst_table_rec := tst_table_rec();  

  l_tab_plus tst_plus_table_rec := tst_plus_table_rec();  

begin  


  for i in c1j  
  loop  

    l_tab.extend;  
    l_tab(l_tab.last) := tst_table_rec(tst_rec(i.icri, i.datesitu, i.curr, 
   i.ctmstm));  

  end loop;    

  SELECT *  
  bulk collect into l_tab_plus  
  FROM   TABLE(get_plus_sdb_w(l_tab));  

  forall idx IN INDICES OF l_tab_plus  
   insert into tst_cri_plus_sdb values l_tab(idx);  

end;

My idea here is to collect all data tst_cri_sdb table into a collection then pass this collection to a pipeline function which will return a collection again so that i can bulk collect it and insert it in the table tst_cri_plus_sdb.

Help me to collect data in the procedure in a collection and also with pipeline function.

Please ask me more information if required.

I am using --

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Answer 1

Your procedure has a couple of simple errors; the cursor name is c1 but you later refer to it as c1j, and you're trying to assign a whole table object instance as an element of a table, instead of just a record:

l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);

The bigger issue is that you're mixing object and native types. When you unnest the collection with the table() clause you get multiple columns back, not a single object type; so you would have to reconstruct your object:

  select tst_plus_rec(deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  bulk collect into l_tab_plus
  from table(get_plus_sdb_w(l_tab));

and then the forall insert would have to return to each object attribute:

  forall idx IN INDICES OF l_tab_plus
  insert into tst_cri_plus_sdb (deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  values (l_tab_plus(idx).deal_id, l_tab_plus(idx).datesitu, l_tab_plus(idx).acc_code,
    l_tab_plus(idx).acca_cust_grp, l_tab_plus(idx).curr, l_tab_plus(idx).ctmstm);

So putting those together:

create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- no need to initialise this one as bulk collect will replace it
--  l_tab_plus tst_plus_table_rec := tst_plus_table_rec();
  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  select tst_plus_rec(deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  bulk collect into l_tab_plus
  from table(get_plus_sdb_w(l_tab));

  forall idx IN INDICES OF l_tab_plus
  insert into tst_cri_plus_sdb (deal_id, datesitu, acc_code, acca_cust_grp, curr, ctmstm)
  values (l_tab_plus(idx).deal_id, l_tab_plus(idx).datesitu, l_tab_plus(idx).acc_code,
    l_tab_plus(idx).acca_cust_grp, l_tab_plus(idx).curr, l_tab_plus(idx).ctmstm);
end;
/

exec tst_insert;

select * from tst_cri_plus_sdb;

   DEAL_ID DATESITU     ACC_CODE ACCA_CUST_ CUR CTMST
---------- ---------- ---------- ---------- --- -----
       100 2019-03-13     100345 A1105      EUR STM  
       100 2019-03-13     200345 A4405      EUR STM  
       100 2019-03-13     300345 A3305      EUR STM  
       100 2019-03-13     500345 A5505      EUR STM  
       100 2019-03-13     600345 A6605      EUR STM  
       100 2019-03-13     700345 A7705      EUR STM  
       101 2019-03-14     100345 A1105      GBP CTM  
       101 2019-03-14     200345 A4405      GBP CTM  
...
       104 2019-03-17     600345 A6605      EUR STM  
       104 2019-03-17     700345 A7705      EUR STM  

30 rows selected. 

You don't need the intermediate collection with bulk collect and forall though, you can insert directly:

create or replace procedure tst_insert
is
  cursor c1 is select * from tst_cri_sdb;
  l_tab tst_table_rec := tst_table_rec();
-- you don't need this variable at all now
--  l_tab_plus tst_plus_table_rec;
begin
  for i in c1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := tst_rec(i.icri, i.datesitu, i.curr, i.ctmstm);
  end loop;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/

which gets the same result. And you could avoid the populating-loop by changing that to a bulk-collect:

create or replace procedure tst_insert
is
  l_tab tst_table_rec;
begin
  select tst_rec(icri, datesitu, curr, ctmstm)
  bulk collect into l_tab
  from tst_cri_sdb;

  insert into tst_cri_plus_sdb
  select *
  from table(get_plus_sdb_w(l_tab));
end;
/

db<>fiddle

---

In later versions of Oracle you could do this all in a package without any schema-level object types; but as you're on 11g that won't work (the table() clause will throw PLS-00642).