Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Question Unanswered: Dynamic cursor in stored procedure

    Hi,

    I read in another thread that a cursor must be declared prior to other processing such as setting variables with the keyword SET in stored procedures.

    We are in the process of converting from Oracle 8i to DB2 UDB. In Oracle 8i, we have a stored procedure which dynamically creates a cursor based on previously set variables:

    Query_String := 'Query goes here'
    OPEN Cursor FOR Query_String

    Is such functionality possible in DB2 UDB?

    Thanks in advance for your help!

    Bruno

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Bruno,
    Yes it is.

    DECLARE STMT VARCHAR(xxx);
    DECLARE CURSOR1 CURSOR for S1;

    SET STMT = 'SELECT ...';
    PREPARE s1 from STMT;

    OPEN CURSOR1;

    HTH

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Here's an example .... This proc takes the name of the table ands retrieves all rows from the table
    CREATE PROCEDURE PROC10(TNAME CHAR(10))
    LANGUAGE SQL
    READS SQL DATA
    RESULT SETS 1

    S1 : BEGIN

    DECLARE SQL_CURS1 CHAR(100) ;
    DECLARE STMT_CURS1 CHAR(100) ;
    DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1 ;

    SET SQL_CURS1 = 'SELECT * FROM '||TNAME ;
    PREPARE STMT_CURS1 FROM SQL_CURS1 ;

    OPEN CURS2 ;

    END S1

    @
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jun 2004
    Posts
    9

    Red face

    I'll give it a try.

    Thanks to both of you!

    Bruno

Posting Permissions

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