var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Dynamic cursor in stored procedure
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!
Yes it is.
DECLARE STMT VARCHAR(xxx);
DECLARE CURSOR1 CURSOR for S1;
SET STMT = 'SELECT ...';
PREPARE s1 from STMT;
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))
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 ;
Visit the new-look IDUG Website
, register to gain access to the excellent content.
I'll give it a try.
Thanks to both of you!