Originally posted by: Bragger

Hi All,

Can anyone please share the example/steps/document link on how to call Stored Procedure in Input Card of a Map? The Stored Procedure will return multiple rows and columns and we need to map those rows in Output as well. Please enlighten if someone has worked on this?

Originally posted by: PaulBrett

Here is a simple example

Thank you.

Paul

Follow me on Twitter

Attachment(s)

Proc.zip   8 KB 1 version

Originally posted by: Bragger

Thank you! I will take a look.

Originally posted by: Bragger

Hi,

Thanks for sharing the sample map. The stored procedure is returning the values in the form of output parameters. Do you know how to fetch a result set consisting of multiple rows and columns from Stored Procedure? No matter what I do, I can-not fetch multiple rows from stored procedure (I intend to use cursor in SP).

I also tried to directly return multiple rows in DBQUERY function by making use of functional map but it didn't work. Here is the DBQUERY function that I used. My expectation was that upon each row returned by DBQuery the functional map will be called and then I will map the columns in output card. Do you know what should I do to make it work?

=F_extract(DBQUERY("SELECT NAME, AGE FROM <table name>","-DBTYPE DB2 -SOURCE <database> -USER xxxx -PASSWORD xxx"))

Originally posted by: PaulBrett

I can't seem to find a suitable Oracle stored procedure example that does not use SYS_REFCURSOR.

The reason I'm avoiding SYS_REFCURSOR, is because it is unsupported:

http://www-01.ibm.com/support/docview.wss?uid=swg21982008

If you have a specific stored procedure example, please include the create statement for it here along with create statements for any supporting objects.

Thank you.

Paul

Follow me on Twitter