By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Nested Stored Procedure returning result sets in DB2 on AS/400

P: n/a
Here is the question
1. I have two stored procedures P1 and P2.
2. I want to call stored procedure P2 from P1.
3. Stored Procedure P2 returns a result set(as an open cursor).
4. In P1, I want to retrieve the result set returned by P2 into a
cursor, iterate through the cursor and insert records into a temporary
table created in P1.
5. Here are the steps I followed inside the stored proced P1:

/* Declare the variable */
DECLARE rSet RESULT_SET_LOCATOR VARYING;
/* Call the stored procedure */
CALL P2();

/* Establish a link between each result set and its locator using the
ASSOCIATE LOCATORS. */
ASSOCIATE RESULT SET LOCATORS (rSet) WITH PROCEDURE P2;

/* Associate a cursor with each result set. */
ALLOCATE C1 CURSOR FOR rSet;

.... and so on.......
6. When I try to run the stored procedure, I got the following error:
Message: [SQL0204] RESULT_SET_LOCATOR in *LIBL type *SQLUDT not found.
7. After some research I found that RESULT_SET_LOCATOR is not supported
on AS/400, in an IBM Red Book. I am not able to find any alternative
ways of doing this. This document is probably dated, but I am not able
to find the latest version of this document.
8. For me putting the P2 code in line in P1 code is not an option as I
already have too much code in P1.
9. We are currently on V5R3

I am literally stuck!. Please help!!!

Any help is deeply appreciated.

Thanks

Sahadev

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.