Using ORACLE Procedure with Ref Cursor output as source in SAP Data Integrator

[Former Member]

Hi everyone,

                     We need to use Oracle Procedure with a Ref Cursor output parameter as a source in SAP Data Integrator, we are searching, and we can't find out a solutions or workarround for that.

For us now is not possible to modify the procedure to store the result in a table and then use the table as source, for that reasen we need to find a way to call the Procedure directly in DI and get the resultset from the ref cursor.

Best Regards,

Martin

[former_member187605]

I don't have access to an Oracle environment so I cannot test whether calling the stored procedure from a SQL transform would work or not.

Is there anybody out there who would want to give this a try?

[former_member198401]

Hi Dirk,

I created a stored procedure with ref cursor and tried to call in DS using New Function Call but was not able to get the output as desired.

I was able to execute the stored procedure at database level by binding the output in a variable

When we import the strored procedure created using refcursor BODS gives the attached warning message

Warning: Parameter <RETURN_ROWS_CURSOR> of stored procedure <GETEMPLOYEEBYDEPID> is omitted, so you cannot provide or retrieve value to or from the parameter. You may get database errors when you call the procedure. Possible causes: (1) The type information for the parameter is missing; (2) The data type of the parameter is not a Data Services supported type, such as INTEGER, CHAR, DATETIME, etc.

It seems that the IN OUT parameter of type REF CURSOR is not supported in Data Services.

Regards

Arun Sasi

[former_member187605]

Thanks for verifying this.

What about calling the same from within a SQL transform? In fact, any valid (sequence of) SQL statement(s) that returns a result set can be used there. And the SQL is not evaluated by DS, but pushed "as is" to the database. Would that make any difference?

[former_member198401]

Hi Dirk,

Also tried with SQL Transform and tried to call the Stored Procedure but it gave me the same error

zero iteration count error oracle bodi-1130199

Regards

Arun Sasi

[former_member187605]

So the only option would be to encapsulate the stored proc in Oracle and have it return the result set.

Thank you for your assistance.

[former_member198401]

Yes!!Currently only OUT parameters can be returned to BODS using a function call. Not sure about the IN OUT parameters of REF CURSOR type

Only Product group people will be able to tell this

will be able to answer this

Regards

Arun Sasi

[former_member198401]

Hi Dirk, Martin,

I found out some more useful points

Please refer to Section of 6.5.5 Structure of a stored procedure of Data Services Reference Guide

Data Services omits a parameter if any of the following data type conditions is true:

• Is not a Data Services supported data type or is long

• Data type is missing in the database dictionary

• Is a composite data type (e.g., table, record, or cursor)

After Data Services imports a stored procedure, the Designer reports warnings about any omitted parameters.

Note:

Even if a stored procedure has an omitted parameter, the procedure is callable if the parameter has a

default value defined. If the omitted parameter does not have a default value, the procedure is not

callable and Data Services throws a runtime error.

Only BusinessObjects Universe Designer  or IDT support usage of Stored Procedure with Ref Cursor.

https://irfansworld.wordpress.com/2012/11/17/what-you-should-know-about-stored-procedure-universe-in...

Regards

Arun Sasi