Running Stored Procedure in Oracle Enterprise Stage

Suman · Post by **Suman** » Wed Dec 06, 2006 5:21 am

I am trying to convert a server job to a parrallel job and in the server job in oracle oci stage one stored procedure is triggered before the sql query.Similar option is not present in Oracle Enterprise Stage.
If someone has any information on how to do this in parrallel job then please reply.

keshav0307 · Post by **keshav0307** » Sat Dec 09, 2006 6:00 pm

Your Oracle OCI stage is source or target?

if its not possible is a single job then you can make a sequece and call the stored procedure before/after the sql query.

chulett · Post by **chulett** » Sat Dec 09, 2006 6:04 pm

Can't you do that in the 'Open Command' area, or whatever it is called?

Suman · Post by **Suman** » Fri Dec 22, 2006 3:42 am

The Oracle oci stage is source stage. For target Oracle oci stage I can use Stored procedure stage before the Oracle enterprise stage in parallel job.For source oracle stage I am planning to use a script which will call the stored procedure in a before job routine.

Suman · Post by **Suman** » Fri Dec 22, 2006 3:46 am

chulett wrote:Can't you do that in the 'Open Command' area, or whatever it is called?

I have not tried that option but definitely look this option.

DSguru2B · Post by **DSguru2B** » Fri Dec 22, 2006 6:50 am

Open command is equivalent to 'Before Sql' in Server and Close command is equivalent to 'After Sql'.

Suman · Post by **Suman** » Wed Dec 27, 2006 1:10 am

DSguru2B wrote:Open command is equivalent to 'Before Sql' in Server and Close command is equivalent to 'After Sql'. ...

I have tried executing the command 'call schemaname.procedurename();' in Open command option. It is not exceuting the procedure and not giving any error message also. Is there any other way to call the procedure in this option.

Suman

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 7:28 am

Try it with exec schemaname.procedurename(). Also try without the trailing semicolon. We had different results on different versions in the recent past.

Suman · Post by **Suman** » Thu Dec 28, 2006 1:16 am

DSguru2B wrote:Try it with exec schemaname.procedurename(). Also try without the trailing semicolon. We had different results on different versions in the recent past. ...

I have tried with exec without the semicolon. Still the procedure is not getting executed.We are using datstage version 7.5.2.

Suman

DSguru2B · Post by **DSguru2B** » Thu Dec 28, 2006 8:34 am

I did a quick search and you were using the correct syntax. Forget the exec and lets put call back. So now your OPEN statement should be
call schemaname.storedprocedurename(). Try getting rid of the trailing semicolon. If that doesnt work get your dba involved, ask him to monitor if the stored procedure really did or did not get fired. Maybe it did but something happened to it half way.

Suman · Post by **Suman** » Tue Jan 02, 2007 12:00 am

DSguru2B wrote:I did a quick search and you were using the correct syntax. Forget the exec and lets put call back. So now your OPEN statement should be
call schemaname.storedprocedurename(). Try getting rid of the trailing semicolon. If that doesnt work get your dba involved, ask him to monitor if the stored procedure really did or did not get fired. Maybe it did but something happened to it half way.

Finally the procedure is working with the syatax 'call schemaname.storedprocedurename()' without semicolon. The problem was there was no commit in the procedure. Thanks to all of you for your valuable suggestion and Happy New Year to all.

Suman