Datastage and Stored Procedure

shiva459 · Post by **shiva459** » Tue Sep 30, 2003 4:45 am

As far as my knowledge goes DS does'nt support out parameters.

Regards

Shiv

trobinson · Post by **trobinson** » Tue Sep 30, 2003 7:16 am

If you are passing the stored procedure correctly, DataStage is oblivious to what happens within the stored procedure. What is the error you are getting? Is it an Oracle error? Depending on your need you could define the stored procedure parameter as INOUT. However, you'll never get anything out. Oracle stages are one-way. In or out. (Input or Output) They are not both. If you want A (single) value out, then you can get around this by encapsulating your stored procedure into a function call and calling via a reference lookup in a User defined SELECT. You could get multiple values via an array function return but that's got to be way too much work. By the way, doing a stored procedure call in the OCI stage is Unsupported. It works but you'll need to do it in ODBC if you want Ascential Support.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 30, 2003 10:59 pm

Try increasing your transaction isolation level.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

trobinson · Post by **trobinson** » Wed Oct 01, 2003 5:47 am

I doubt it. Datastage has no reason to default a stored procedure call transaction to read only. Did you try the stored procedure from SQLPlus? Were the results the same? It appears to me to be an Oracle issue.