Answered: Retrieving a Cursor as OUT Parameter from a SP in .net (db2 v9.7 FP 5 on Linux)
I am stuck again and need to pick your brains. I have a simple stored procedure that has a CURSOR as an OUT parameter. The Code is as given below:-
CREATE OR REPLACE PROCEDURE "MYTEST1"(OUT p_op_rc CURSOR)
SET p_op_rc = CURSOR FOR select * from table;
This procedure compiled without any errors (using DataStudio client), and gives the required output also, however the trouble starts when we use any other client (.net), the procedure call fails with the below error message:-
SQL20441N A "CURSOR" data type is not supported in the context where it is being used.
I get the same error when I try to use Putty to run the procedure from the Linux command line.
Any thoughts or insights would be highly appreciated.
Cursor variables are not supported for use in applications. Cursor variables can only be declared and referenced in SQL PL contexts.
In your case, the command line (clp) is the application.
But if you call your sproc from another sproc (i.e. from an SQLPL context), you should get what you need if you call the second (parent) sproc from the clp.
Thanks for the response, the challenge I am facing is that I am trying to migrate Oracle Stored Procedures to DB2 using the Oracle compatibility vector, now since Oracle enables to use CURSOR as OUT variable, any idea what is the best way to tackle this?
Without seeing the real code, I cannot advise further.
Note that DB2 supports CURSOR as an OUT parameter - with the restriction that the *caller* has to be an SQLPL context.
There's always more than one way to solve a problem however...
Thank You to all who helped me out on this topic. I was finally able to figure out the reason behind the error I was facing, it was due to the DB2 Express C that was installed on the .net server. Since Express C could not parse the cursor, the error was passed on to the calling program. When I removed the Express C installation and used a direct call to the db2 server, the code is working as expected. With db2 9.7 we can use CURSOR as an OUT parameter with both Java and .net, I just wanted to update the thread so that someone else facing the same problem can get a better answer.