Hi,

I have 2 stored procedures A and B. A makes a call to B and B returns a cursor to A. Stored Procedure B executes fine individually. However, when I execute A, I get the following error:

SQLState = 07003
SQLCode = -518

Code snippet for stored procedure B:
DECLARE SampleCursor CURSOR WITH RETURN TO CALLER FOR
SELECT *
FROM TABLE
FETCH FIRST 5 ROWS ONLY;

OPEN SampleCursor

Code snippet for stored procedure A:
DECLARE LOC1 RESULT_SET_LOCATOR VARYING;

CALL B(OUT_SQLCODE, OUT_SQLSTATE, OUT_MESSAGE);

ASSOCIATE RESULT SET LOCATOR (:LOC1) WITH PROCEDURE B;
ALLOCATE CSR1 CURSOR FOR RESULT SET :LOC1;

EXECUTE CSR1 USING LOC1;

Can anyone please help me with this?

I think cursors are supposed to be OPEN'ed, not EXECUTE'd...

EXECUTE is for dynamic SQL, which you have to PREPARE first. The explanation for message SQL0518 tells you that.

But as n_i said, you want to use OPEN instead.

I had tried to OPEN the cursor. However, I got the following error:

THE OPEN STATEMENT FOR CURSOR "CSR1" IS INVALID BECAUSE THE CURSOR WAS DEFINED BY AN ALLOCATE CURSOR STATEMENT

Any idea about how this can be done?

Well, since the cursor was in fact opened by the nested procedure, you should be able to fetch from it after the ALLOCATE statement.

Hmm. I just need to return the cursor from the calling stored procedure. I don't want to fetch values from the cursor into local variables by looping. Can you please suggest how this can be done?

Then you should have declared it WITH RETURN TO CLIENT.

I tried using "WITH RETURN TO CLIENT" in the cursor definition. However, I am getting a compilation error:

ILLEGAL SYMBOL "CLIENT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: CALLER

I am using DB2 V8. Has it got anything to do with this error?

I guess you are on DB2 for z/OS? Because DB2 LUW definitively supports WITH RETURN TO CLIENT, whereas DB2 z/OS does not yet allow this syntax.

Yes Stolze, I am using DB2 on z/OS. I tried looking for pointers on the net. But, unfortunately could not get any. Can you please help me with this?

Hey Stolze, can you please help me with this? I am trying to figure out a way to call my nested stored procedure.

The call is not the problem. Returning result sets to the client (instead to the caller) is. As far as I have found, this is not yet supported.

We found a workaround for this.Get the ResultSet in caller SP, loop through it to read the data and make entries in a temporary table. Then have another cursor which reads this temporary table and sends ResultSet out.

Yes, that will work, of course.