stored procedure

prasad_chennai · Post by **prasad_chennai** » Mon Mar 10, 2008 7:36 am

I am calling a stored procedure through the stored procedure stage from datastage.When i call stored procedure,i just fire the call statement and my datastage job finishes.My datastage Job will not wait until the stored procedure executes successfully.So then how i will come to know that procedure is executed successfully or its got failed.

So Is there any functionality in stored procedure stage,that it will wait until the procedure executes completely and returns any status.My stored procedure doen't return any value,but will value only IN parameter.

so can anybody guide about this.

Thanks,
prasad

chulett · Post by **chulett** » Mon Mar 10, 2008 7:45 am

Never seen that behaviour. What database?

prasad_chennai · Post by **prasad_chennai** » Mon Mar 10, 2008 7:50 am

Oracle itself.
can't we make our job wait, till procedure execute completely.

chulett · Post by **chulett** » Mon Mar 10, 2008 7:53 am

That is how it should be working. We use Oracle and the SP stage and have never seen this. Time to check with your official Support provider, it seems.

prasad_chennai · Post by **prasad_chennai** » Wed Mar 12, 2008 2:02 am

I asked my admin about the same and ha sdone some setttings and code working fine now.i will get the setup been done i will update about this.

In my stored procedure. iam just passing the Input parameter and it wont return any OUT parameter.So if the procedure fails due to any exceptions,then will my Datastage Job will also get aborted at the same with the same exception been trapped in datastage log.Or to have this functionality we need to have OUT parameter compulsorily.

Thanks,
prasad

ray.wurlod · Post by **ray.wurlod** » Wed Mar 12, 2008 2:49 am

Again, this falls under the heading of testing - you need to set up conditions (such as a faulty input argument) and verify what your DataStage does. You have already observed that it had been configured differently from Craig's - who knows what other differences may obtain?

chulett · Post by **chulett** » Wed Mar 12, 2008 7:30 am

prasad_chennai wrote:In my stored procedure. iam just passing the Input parameter and it wont return any OUT parameter.So if the procedure fails due to any exceptions,then will my Datastage Job will also get aborted at the same with the same exception been trapped in datastage log.Or to have this functionality we need to have OUT parameter compulsorily.

This is exactly the kind of sproc we typically use without issue. You don't need out parameters for error handling, as long as the sproc raises an error the job will notice. As noted, you should always test this kind of functionality (force it to fail) in order to verify proper functionality for yourself.