Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Cursor and Global Temporary Table in Stored Procedure

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Cursor and Global Temporary Table in Stored Procedure

    Yes ... You can

    Cheers
    Sathyaram
    Originally posted by bgdeepak
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    14

    Re: Cursor and Global Temporary Table in Stored Procedure

    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Cursor and Global Temporary Table in Stored Procedure

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2003
    Posts
    3

    Question Re: Cursor and Global Temporary Table in Stored Procedure

    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



    Originally posted by sathyaram_s
    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

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: Cursor and Global Temporary Table in Stored Procedure

    Originally posted by asham
    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?
    Are you creating it WITH REPLACE as in Sathyaram's example?

  7. #7
    Join Date
    Dec 2003
    Posts
    3

    Re: Cursor and Global Temporary Table in Stored Procedure

    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


    Originally posted by Damian Ibbotson
    Are you creating it WITH REPLACE as in Sathyaram's example?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •