Using GLOBAL TEMPORARY TABLE and CURSOR

I have a requirement where I have to populate a TEMPORARY TABLE and use the table in a CURSOR.

In a stored procedure it is mandatory to declare the CURSOR before I declare the the TEMPORARY TABLE. But it gives me an error:
"SESSION.TABLE_NAME" is an Undefined Name.

Following is the code:

CREATE PROCEDURE CORE.TEMP_TABLE_SP ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE TEMP CURSOR FOR
SELECT * FROM SESSION.TEMP_SERVICE;

DECLARE GLOBAL TEMPORARY TABLE TEMP_SERVICE (ID_NUM INT) WITH REPLACE NOT LOGGED;

Delete from session.TEMP_SERVICE;

Insert into session.TEMP_SERVICE
VALUES(2);

OPEN TEMP;
END P1

Can any body help me?......

I would think that you should declare and populate the global temp table first

fv

In a Stored Procedure in db2 we can not declare temporary table before we declare the cursor.

Here you go....... try this one it works

CREATE PROCEDURE FETCH_CS01_TEST_RESULTS_PRC1()
RESULT SETS 1
LANGUAGE SQL
P1 : BEGIN


DECLARE GLOBAL TEMPORARY TABLE XYZ(
ID INTEGER);




P2 : BEGIN


DECLARE c1 CURSOR WITH RETURN FOR SELECT ID FROM SESSION.XYZ ;


OPEN c1;

END P2;
END P1@

Thanks.. This helped me also..

Hi, wonderful tip. We were stuck trying to use a temporary table with a cursor, until we read in this thread that you have to declare the cursr first and then the global temporary table.

Thanks!