Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Running oracle packages in series/parallel

Posted on 2011-09-20
4
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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