Getting the resultset from Oracle stored procedure

jagadhish · Post by **jagadhish** » Wed Aug 30, 2006 6:22 am

Hi,

I have a oracle procedure which returns resultset to datastage using Ref. cursor.

Understood from the earlier posts that STP stage does not support REf. cursor. Can I use ODBC/OCI stages to call that procedure and get the resultset data from that procedure?

Is there any other way to get the resultset from procedure other than using the Ref cursor...

Thanks in advance...

jagadhish

kumar_s · Post by **kumar_s** » Wed Aug 30, 2006 6:28 am

You can use ODBC/OCI to call the stored procedure. which will return the result set to datastge.

jagadhish · Post by **jagadhish** » Mon Sep 11, 2006 1:41 am

Hi,

I have been treing the same(using odbc stage), but could not make it.

How I can map the cursor resultset to columns defined in the odbc stage?

Any help is greatly appreciated.

kumar_s · Post by **kumar_s** » Mon Sep 11, 2006 3:45 am

Call the stored procedure in input tab, write into a intermediate table, and select the table in output tab.

jagadhish · Post by **jagadhish** » Wed Sep 13, 2006 12:28 am

Hi,

We cant write embedded Sql in the stages( client requirement), Any Insert or Select should be done through stored procedure. So I need to call this procedure to get the resultset and map it to the output columns.

We can call procedure and declare all the columns in the in the output tab of odbc stage , but how should the cursor resultset is mapped to those columns?

I'm not getting the correct syntax which will map the resultset to the columns.

what is the call syntax i should write for this?

thanks in advance,
Jags