Skip to Main Content
  • Questions
  • dbms_parallel_execute and 2 packages of mine !

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Patrice.

Asked: October 20, 2016 - 2:27 pm UTC

Last updated: October 21, 2016 - 2:32 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi

I've 2 PL/SQL packages.

- First is dedicated to :
* create a parallel task,
* then to create chunks (by rowid),
* and finally to execute (previous) created task by executing a second PL/SQL Package.

- Second package execute many procedures and functions uppon start and end_id given earlier

All of this works perfectly BUT I can't find a way to retrieve inside the second package the related/linked chunk id...

In fact I want to prefix all my messages put into a log with this chunk_id since the log is shared by all the chunks.

Do you have any idea ?

regards and above all thank's for your support


and Connor said...

Wouldnt you just query the data dictionary with your start/end ID's to map it back to the chunk ?

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> begin
  2    dbms_parallel_execute.create_task (task_name => 'blah');
  3    dbms_parallel_execute.create_chunks_by_rowid(
  4      task_name   => 'blah',
  5      table_owner => user,
  6      table_name  => 'T',
  7      by_row      => true,
  8      chunk_size  => 20000);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select chunk_id, start_rowid, end_rowid
  2  from   user_parallel_execute_chunks
  3  where  task_name = 'blah'
  4  order by chunk_id;

  CHUNK_ID START_ROWID        END_ROWID
---------- ------------------ ------------------
    374201 AAAqsDAAGAAAs1oAAA AAAqsDAAGAAAs1vCcP
    374202 AAAqsDAAGAAAs1wAAA AAAqsDAAGAAAs13CcP
    374203 AAAqsDAAGAAAtOAAAA AAAqsDAAGAAAtOHCcP
    374204 AAAqsDAAGAAAtOIAAA AAAqsDAAGAAAtOPCcP
    374205 AAAqsDAAGAAAtOQAAA AAAqsDAAGAAAtOXCcP
    374206 AAAqsDAAGAAAtOYAAA AAAqsDAAGAAAtOfCcP
    374207 AAAqsDAAGAAAtOgAAA AAAqsDAAGAAAtOnCcP
    374208 AAAqsDAAGAAAtOoAAA AAAqsDAAGAAAtOvCcP
    374209 AAAqsDAAGAAAtOwAAA AAAqsDAAGAAAtO3CcP
    374210 AAAqsDAAGAAAtO4AAA AAAqsDAAGAAAtO/CcP
    374211 AAAqsDAAGAAAtPAAAA AAAqsDAAGAAAtPHCcP
    374212 AAAqsDAAGAAAtPIAAA AAAqsDAAGAAAtPPCcP
    374213 AAAqsDAAGAAAtPQAAA AAAqsDAAGAAAtPXCcP
    374214 AAAqsDAAGAAAtPYAAA AAAqsDAAGAAAtPfCcP
    374215 AAAqsDAAGAAAtPgAAA AAAqsDAAGAAAtPnCcP
    374216 AAAqsDAAGAAAtPoAAA AAAqsDAAGAAAtPvCcP
    374217 AAAqsDAAGAAAtQAAAA AAAqsDAAGAAAtR/CcP
    374218 AAAqsDAAGAAAtSAAAA AAAqsDAAGAAAtT/CcP
    374219 AAAqsDAAGAAAtUAAAA AAAqsDAAGAAAtV/CcP
    374220 AAAqsDAAGAAAtWAAAA AAAqsDAAGAAAtX/CcP
    374221 AAAqsDAAGAAAtYAAAA AAAqsDAAGAAAtZ/CcP
    374222 AAAqsDAAGAAAtaAAAA AAAqsDAAGAAAtb/CcP
    374223 AAAqsDAAGAAAtcAAAA AAAqsDAAGAAAtd/CcP
    374224 AAAqsDAAGAAAteAAAA AAAqsDAAGAAAtf/CcP
    374225 AAAqsDAAGAAAtgAAAA AAAqsDAAGAAAth/CcP
    374226 AAAqsDAAGAAAtiAAAA AAAqsDAAGAAAtj/CcP
    374227 AAAqsDAAGAAAtkAAAA AAAqsDAAGAAAtl/CcP
    374228 AAAqsDAAGAAAtmAAAA AAAqsDAAGAAAtn/CcP
    374229 AAAqsDAAGAAAtoAAAA AAAqsDAAGAAAtp/CcP

29 rows selected.

SQL>


So you would query user_parallel_execute_chunks.

Hope this helps

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

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