Solved

Running oracle packages in series/parallel

Posted on 2011-09-20
4
245 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:gs79
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
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
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:gs79
Comment Utility
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now