Problem with calling Oracle Stored Procedure

[ray.wurlod]

You could try user-defined SQL using CALL - I do not know whether this will work.
As a general rule, definitions of stored procedures to be invoked by DataStage have to be imported (or created) first (so that the number of arguments can be verified) and must have at least a return result set. In my experience, at least one input parameter is required, even if the stored procedure ignores it. I have only ever invoked stored procedures through an ODBC stage. DataStage Designer's Help suggests that stored procedures can only be used from ODBC or BCP stages.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[mhester]

Raju,

Ray is correct, there is no facility to call a stored procedure from the OCI stage. The following is from the online help -

"You cannot call stored procedures, as there is no facility for parsing the row values as parameters."

Regards,

Michael Hester

[degraciavg]

hi raj,

you can use CALL in the Oracle OCI stage. below is an example on how to run a stored proc without parameter,

CALL SampleProc()

i've used and tested it before (on Ora8i and 9i) and it works... you might want to try it...

vladimir

[/quote]

[raju_chvr]

Can someone tell me where is the online help you are talking abt. in this thread 'mhester' has referred to online help. can you let me know what is the url ur referring to?

[trobinson]

You can use CALL in the OCI Stage as User Defined SQL. You can pass in parameters. It is unsupported. To make sure the parameters are bound correctly make all the columns KEY = YES. You cannot get any values back.
If you are hoping for some error checking be aware that not all errors returned from Oracle are trappable.
Online Help? You're reading it! I'm guessing he meant "F1" help or help from the pdfs. All are pretty weak.