How to perform parallel DMLs’ on different tables

SQL> create table test12 (l_str2 varchar(128));
Table created.
 
SQL> create table test22 (l_str2 varchar(128));
Table created.
 
SQL> insert into test12 (l_str2) values ('0');
1 row created.
 
SQL> insert into test22 (l_str2) values ('0');
1 row created.
 
SQL> commit;
Commit complete.
 
SQL> create type typ_dml_string as table of varchar2(4000);
/
Type created.

SQL> create or replace function fnc_pl_manager return simple_integer parallel_enable
is
begin
    execute immediate 'alter session force parallel query';
    return 1;
end fnc_pl_manager;
/
Function created.

SQL> create table tbl_pl_manager as select * from dual; 
Table created. 

SQL> create or replace view viw_pl_manager
as
select 1 as THREAD_ID from  tbl_pl_manager  where fnc_pl_manager=1
union
select 2 as THREAD_ID from  tbl_pl_manager  where fnc_pl_manager=1;
View created.

SQL> create or replace package pkg_pl_manager
as
  type rc_viw_pl_manager is ref cursor return viw_pl_manager%rowtype;
  function fnc_submit (p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager )
               return typ_dml_string
               parallel_enable(partition p_pl_refcur by any)
               pipelined
               deterministic;
     procedure prc_launch(l_what in varchar2);
end pkg_pl_manager;
/
Package created.

SQL> create or replace package body pkg_pl_manager
as
--------------------------------
 procedure prc_launch(l_what in varchar2)
 is
 pragma autonomous_transaction;
 begin
     execute immediate l_what;
     commit;
 end prc_launch;
 --------------------------------
 function fnc_submit(p_task_list in typ_dml_string, p_pl_refcur in rc_viw_pl_manager)
  return typ_dml_string
  parallel_enable(partition p_pl_refcur by any)
  pipelined
  deterministic
  is
      pointer_viw_pl_manager viw_pl_manager%rowtype;
  begin
      loop
      fetch p_pl_refcur into pointer_viw_pl_manager;
      exit when p_pl_refcur%notfound; --it's important to place exit statement exactly here
      prc_launch(p_task_list(pointer_viw_pl_manager.thread_id));
      end loop;
      return;
end fnc_submit;
  ---------------------------------
  end pkg_pl_manager;
/
Package body created.

SQL> select pkg_pl_manager.fnc_submit(typ_dml_string('update test12 set l_str2=to_char(12)||to_char(current_timestamp(6))', 'update test22 set l_str2=to_char(22)||to_char(current_timestamp(6))'), cursor(select THREAD_ID from viw_pl_manager)) as A from dual; 
A -------------------- 
TYP_DML_STRING() 

SQL> select L_STR2 from test12   
union
select L_STR2 from test22; 

L_STR2 
-------------------------------------- 
1210-FEB-20 11.34.19.584799 AM -05:00 
2210-FEB-20 11.34.19.584009 AM -05:00 








Leave a Reply

Your email address will not be published. Required fields are marked *