Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to do Multithreading in Oracle PlSql using a Single Session

542957May 23 2009 — edited May 23 2009
Hi All,

I have a complex but interesting requirement.

1) I have a table with 90k rows.
2) My PlSql Package picks up all the 90k rows one by one, does some processing/transformation on it
3) if the row satisfies some business rules, then it gets inserted into an application table. If the row is not eligible, then it inserts the row into a Error table.
4) After processing all the 90k records, If my Error Table Count reaches 10% of the input count (90k), then all inserts (i.e. both application table inserts and
error table inserts) must be rolled back.
5) Right now, we are achieving this logic in a single session but there are far many performance issues, because processing has to go through row by row.

Is it possible to implement the above logic as "multi threading within PlSql" whereby i can run Step 2 in 10 parallel slaves and if the total error count of all the 10 slaves exceeds 10% of the input data, then there should be a rollback for all 10 slaves.

+A sample testcase is given below. Issue is: rollback statement in p1 is not taking any effect (because plsql spawns sessions to execute p2 which autocommits itself on graceful exit). But is there any other way by which i can make the rollback to be effective+
create table t1 (a number);
create table j1(b number);

create or replace procedure p1
as
l_cnt number;
i number;
l_job number;
begin
	for i in 1 .. 5 loop
        dbms_job.submit( l_job, 'p2('||i||');' );        
    end loop;
    commit;
    loop
        dbms_lock.sleep(30);
        select count(*) into l_cnt from j1;
        exit when (l_cnt = 5);
    end loop;
   rollback;
end;
/

create or replace procedure p2(i number)
as
begin
insert into t1 values(i);
insert into j1 values(1);
end;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 20 2009
Added on May 23 2009
2 comments
1,656 views