Running oracle packages in series/parallel

gs79
gs79 used Ask the Experts™
on
I have 8 oracle packages created by OWB. These 8 packages are created by 8 different OWB mappings. These packages can be categorized in to two sets:
'feed' and 'stage'.

the packages  belonging to feed group are as follows:
feed1
feed2
feed3
feed4

Similarly the packages belonging to stage group are as follows
stage1
stage2
stage3
stage4.

I have a requirement where I need to run the oracle packages in following sequence:

feed1 - stage1 should run in series, similarly feed2 and stage2 packages should run in series and so on..

Now feed1-stage1
and feed2-stage2
feed3 - stage3
and feed4-stage4
 can all run in parallel..\

Since the process flow tool we are using here is messed up, I would like to create another procedure which executes the above packages the way I have described above..

Please let me know how this can be achieved in a procedure..

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Devinder Singh VirdiLead Oracle DBA Team

Commented:
I am not sure how to call any package in background, however you can write a shell script by creating one common script accepting different parameter.

Start different session and call the procedure as require. i.e.

sqlplus username/pass@db @file call_pak_1

sqlplus username/pass@db @file call_pak_2
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Just execute them in order?  If you need some sort of error checking between them it should be pretty simple to set up.  For example, do not run stage1 if feed1 fails.

create or replace procedure RunThemAll
is
begin
    somepkg.feed1
    somepkg.stage1
...
end;
/

>>can all run in parallel..\

You can set up separate jobs using dbms_scheduler for this.

In the procedure set up a create_job:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sched.htm#i1000363

Define a PLSQL_BLOCK of:
begin
    somepkg.feed1
    somepkg.stage1
end;

for each job you want to run in parallel.

Author

Commented:
Can I execute the package with in the procedure 'runthemall' you mentioned above. The feed1, feed2 ..stage1.stage2.. are all packages..You mean I need to call the procedure within this packages in the plsql block? Is there a way to execute just the packages in plsql block?

Thanks
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>You mean I need to call the procedure within this packages in the plsql block?

Yes.  You need to explicitly execute/call the individual procedures.

>>Is there a way to execute just the packages in plsql block?

If you are asking if there is a way to execute ALL procedures in a package with a single command, then no.

A package is just a container.  There is no way to execute a package.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial