Solved

Running oracle packages in series/parallel

Posted on 2011-09-20
4
250 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
ID: 36571298
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
ID: 36573448
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
ID: 36577019
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)
ID: 36580855
>>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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

24 Experts available now in Live!

Get 1:1 Help Now