oracle pkg and shell script

[sweetleaf]

Hi ,

Here's an odd situation.

I'm using a 3rd party tool called Data Stage. I have a Data Stage program which after its ran, calls a shell script which in turn calls an oracle packaged procedure (SP1) sitting in a seperate unix box.

SP1 in turn makes the following call:

sp2;
sp3;

Interestingly enough, sp2 runs when invoked from the shell script but sp3 does not.
If I change the order in which they are called to this:

sp3;
sp2;

neither will run.

When I run SP1 directly from something like sql*plus or sql-navigator both calls to sp2 and sp3 are successful.

But I really need to be able to invoke both sp2 and sp3 from the shell script.

The shell script calls SP1. When I first made the shell script SP1 only called sp2.
Now SP1 calls sp2 and sp3. I'm not sure this makes any difference.

Please help!

cheers

 

[lewisp]

You need to supply more information. When you say SP3 does not run do you mean SP2 doesnt call it or SP3 fails when it runs?

How do you know SP3 hasn't run? Do you get error messages or are you expecting some data to change in the database that doesnt, for example? Do you do a commit after the SP3 run to ensure any data SP3 changes is saved to the database?

Does SP3 take parameters that are different when you call from SQL*Plus?

It looks to me that SP3 is failing without returning any errors and preventing SP2 from being run (when in that order).

 

[did02]

Hi Sweetleaf,

I worked for a while with Datastage (on NT) + Oracle 8.
Could you please tell me more about the job log.

Rgds,
Did02

 

[cynthiarice]

sweetleaf,
This is probably nothing to do with what you're doing, but when I tried to use a bat script to run a series of commands, Oracle would work the script up to the point where an input and enter was required then it would hang. Is there anything in sp3 which requires a return or is there a response and a return. Like I said may not have anything to do with what you're doing in sp3 but worth a look.