Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Running oracle packages in series/parallel

Posted on 2011-09-20
4
Medium Priority
?
296 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 78

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 78

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

926 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