You Asked

I have a porject that am working on which involves multiple tables with millions of rows each, I plan to use the dbms_parallel_execute to process the table in chunks
The situation is as follows
Tablea has columns col1 that needs to be uypdated
tableb has columns col1,col2 that needs to be updated
tablec has colums col1,col2,col3 that needs to be updated
tabled has columns col1 that needs to be updated
...
...
How can I use refcursor to process this via a stored procedure and use the dbms_parallel_execute
My process would be

procedure ProcessTable(tname,sis in rowid,eid rowid)
cursor c1 is select table_name,column name from control_Table where table_name=tname
loop
 fetch c1 into v1
 execute dynamic sql to select rowid rid,col1,col2... from tname -> different tables will have different columns
 loop
 fetch .... -> how do I fetch when I do not know the column list;
  update tname
  set col..=...
where rowid=rid;

end loop;

The above procedure would be executed using dbms_parallel_execute apis

and Chris said...

I feel like you're approaching this the wrong way. You can pass SQL statements to dbms_parallel_execute. You just need

create or replace procedure p ( 
  task varchar2, chunk_sql varchar2, 
  update_stmt varchar2 , parallel_threads pls_integer
) as
begin
  dbms_parallel_execute.create_task( task_name => task );
  
  dbms_parallel_execute.create_chunks_by_sql(
    task_name   => task,
    sql_stmt    => chunk_sql,
    by_rowid    => false
  );

  dbms_parallel_execute.run_task(
      task_name        => task,
      sql_stmt         => update_stmt,
      language_flag    => dbms_sql.native,
      parallel_level   => parallel_threads
  );
  
  dbms_parallel_execute.drop_task( task_name => task );
end p;
/

If this doesn't help we're going to need more details to understand exactly what you're trying to achieve. And why this doesn't do what you want.

Rating

(2 ratings)

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

Comments

A reader, August 23, 2017 - 9:33 pm UTC

My main problem is not so much with the parallel execution of the task but with how go go about processing the dynamic sql and the varied column list
Summary of work as follows
Select col1,col2,...(col list known only at run time)
Parse each column for a specific string
Update the tables fo all the columns that reflect the parsed value to be true
Record the id of the row that was updated for audit requirements
Hope I am making myself clear

August 24, 2017 - 10:31 am UTC

I'm not following. Post a worked example (including the DDL for your tables!) showing us what you're trying to do.

Sample code

Ian D'Roza, August 24, 2017 - 9:48 pm UTC

create table table_list as select t.owner,t.table_name,c.column_name,c.data_type
from dba_tables t,dba_tab_columns c
where t.owner=<application scehema>'
and t.table_name=c.table_name;

create procedure processlist ( tname varchar2,srid rowid,erid rowid) as
cursor c1 is select table_name,listagg(column_name,',') WITHIN GROUP (ORDER BY Table_name) col_list,listagg(data_type,',') WITHIN GROUP (ORDER BY table_name) dt_list
from table_list where table_name=tname group by table_name;
v1 c1%ROWTYPE;
begin
for v1 in c1
loop
-- Process l
dynamic_str:='select rowid,'||v1.col_list||' from '||v1.table_name;
process this dynamic sql
for each row check if each column data has a specific type of string(the columns could be varchar2,xmltype or clob)
if column meets the criteria replace some part of the column
update the v1.table_name set column_name=(replace value)
reord the table_name,rowid that was updated
end process dynamic loop
end loop;
end;
/

Hope the process gives you some clue as to what I am trying to do.

August 25, 2017 - 10:35 am UTC

So you want to dynamically update tables containing varchar2, clob, etc.?

If so, you can query user_tab_cols to find these tables and columns. And build up your SQL statement using listagg. For example:

create table t (
  x varchar2(10), y int, z varchar2(10)
);

select table_name, 
       'update ' || table_name || ' set ' ||
       listagg(column_name, ' = ''NEW VALS'',') within group (order by column_name) ||
       ' = ''NEW VALS'' returning rowid into :rids' update_stmt
from   user_tab_cols
where  data_type in ('VARCHAR2', 'XMLTYPE', 'CLOB')
and    table_name = 'T'
group  by table_name ;

TABLE_NAME  UPDATE_STMT                                                            
T           update T set X = 'NEW VALS',Z = 'NEW VALS' returning rowid into :rids

The returning clause enables you to capture which rows your update changed. So when you run the update, you bulk collect these into an array. You can then save these into another table (or whatever):

insert into t values ('this', 0, 'that');
insert into t values ('this', 1, 'that');
insert into t values ('this', 2, 'that');
insert into t values ('this', 3, 'that');
insert into t values ('this', 4, 'that');

commit;

declare
  type rowids_tab is table of rowid index by pls_integer;
  
  rowids rowids_tab;
begin
  for updates in (
    select table_name, 
           'update ' || table_name || ' set ' ||
           listagg(column_name, ' = ''NEW VALS'',') within group (order by column_name) ||
           ' = ''NEW VALS'' returning rowid into :rids' update_stmt
    from   user_tab_cols
    where  data_type in ('VARCHAR2', 'XMLTYPE', 'CLOB')
    and    table_name = 'T'
    group  by table_name 
  ) loop
    execute immediate updates.update_stmt
    returning bulk collect into rowids;
    
    for i in 1 .. rowids.count loop
      dbms_output.put_line(updates.table_name || ' ' || rowids(i));
    end loop;
  end loop;
end;
/

T AAAcyCAABAAARn/AAA
T AAAcyCAABAAARn/AAB
T AAAcyCAABAAARn/AAC
T AAAcyCAABAAARn/AAD
T AAAcyCAABAAARn/AAE

select * from t;

X         Y  Z         
NEW VALS  0  NEW VALS  
NEW VALS  1  NEW VALS  
NEW VALS  2  NEW VALS  
NEW VALS  3  NEW VALS  
NEW VALS  4  NEW VALS

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Sample code

More to Explore

PL/SQL demos

PL/SQL docs

Dynamic sql without a known result set

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Sample code

More to Explore

PL/SQL demos

PL/SQL docs