I am using a stored procedure which is using a Cursor and Global Temporary Table.
Cursor is using the Global Temporary table. When I declare the cursor it gives me the error that Global Temporary Table is not defined.
Can I define a Global Temporary table before a Cursor Declaration in a Stored Procedure.
The Error number that I am getting is SQL0204N
SQL State is : 42704

Yes ... You can

Cheers
Sathyaram

HI,

First i have to declare the cursor and then the global temporary tables creation happens.SO the error is that the global temporary table is undefined.
Now suppose i create the global temporary tables in the SP before and then declare, it says that the cursor definition is invalid since it should be the first statement in the SP.SO how are we supposed to go about it.

Can you give us a sample piece of code.This is how we declare the cursor and then the global temporary table.

create procedure x
begin a
DECLARE GLOBAL TEMPORARY TABLE a
(
a integer
);

DECLARE cursor1 CURSOR WITH RETURN FOR
select a from session.a;

--do some processing here

end a

Here is a sample :

CREATE PROCEDURE sPROC1 (
IN parm1 CHAR(7)
,IN PARM2 CHAR(24)
)
LANGUAGE SQL
SPECIFIC sp1
MODIFIES SQL DATA
RESULT SETS 1

P1: BEGIN


DECLARE GLOBAL TEMPORARY TABLE SESSION.DGTT1
(
ID INT
,CoL1 CHAR(8)
,Col2 VARCHAR(150)
,col3 CHAR(7)
,col4 CHAR(24)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

BEGIN
DECLARE CURS1 CURSOR WITH RETURN FOR
SELECT *
FROM SESSION.DGTT1 ;


INSERT INTO SESSION.DGTT1
values(10,'00','111','111','111') ;



OPEN CURS1 ;

END;

END P1

Thank you so much for the solution.

There is one issue with this though.. I cant drop the temp table. and the next time I call this stored procedure, it fails cause the table is not dropped, and it wont let me create a table of the same name. But I need the cursor open when I quit the SP to read the recordset.

Is there a way I can check to see if the table is present, drop it and then redeclare it?

thanks in advance,
asha

Are you creating it WITH REPLACE as in Sathyaram's example?

Hi Damian,

thanks for bringing that to my notice. I wasnt creating it with 'WITH REPLACE'. I added that and it seems to be working fine.

Thanks again.
asha