Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?

for rec in select column from table1
loop
execute stored_procedure( rec.column );
end loop;
share|improve this question
    
Would creating 2 separate threads help you? –  Henrique Ordine Apr 11 '13 at 19:29

1 Answer 1

The pl/sql package DBMS_PARALLEL_EXECUTE can be used to parallelize pl/sql. Examples can be found in the manual. You can use the first example (Chunk by ROWID):

define a procedure

create process_chunk(start_id rowid, end_id in row_id)
...
begin  
...
for rec in select column from table1 where rowid between start_id and end_id
  loop
  stored_procedure( rec.column );
  end loop;
...
end;
/

and then (following the example) write a job that does the following

...
begin
...
--create a task
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID in chunks of 100 rows
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', USER, 'TABLE1', true, 100);
-- execute with parallel level 10
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'call process_chunk(:start_id,:end_id)',
  DBMS_SQL.NATIVE,  parallel_level => 10);

--and implement code for error handling and removing the task according to the example.
...
end;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.