CAN WE MAKE USE OF CURSORS IN A SIMPLE BLOCK IN DB2?

nsd

HI ALL,

HERE I HAVE MADE USE OF SAME CURSOR DECLARATIONS AND USAGE IN THE
FOLLOWING TWO DB2 CODES .
BUT I AM GETTING AN ERROR IN THE FIRST CASE i.e CURSOR IN A SIMPLE
BLOCK.

SO I HAVE A QUESTION...
CAN WE MAKE USE OF CURSORS IN A SIMPLE BLOCK?

NOTE: WE CAN MAKE USE OF IT IN CASE OF ORACLE.
PL. HAVE A LOOK AT THE FOLLOWING CODES

-----------------------------------------------------------
1). CURSOR INSIDE A SIMPLE BLOCK (giving error)
-----------------------------------------------------------
BEGIN ATOMIC
DECLARE v_sno INTEGER;
DECLARE my_cur CURSOR FOR SELECT sno FROM t_testforcur;
OPEN my_cur;
FETCH FROM my_cur INTO v_sno;
CLOSE my_cur;
END@
-----------------------------------------------------------

-----------------------------------------------------------
2). CURSOR INSIDE A PROCEDURE
-----------------------------------------------------------
CREATE PROCEDURE p_testforcur( )
LANGUAGE SQL
SPECIFIC p_test
BEGIN ATOMIC
DECLARE v_sno INTEGER;
DECLARE my_cur CURSOR FOR SELECT sno FROM t_testforcur;
OPEN my_cur;
FETCH FROM my_cur INTO v_sno;
CLOSE my_cur;
END@
------------------------------------------------------------

regards,
nsd

Nov 12 '05 #1

Tonkuma

Please see "SQL Reference Volume 2 Version 8.2" --> Statements.
And compare Compound SQL(Dynamic), Compound SQL(Embedded) and Compound
SQL(Procedure). In their descriptions, usable SQL statements for each
Compound SQLs are documented. For first two Compound SQLs, you can't
use CURSOR related statements inside the Compound SQL statements.
But, you can use cursor by embedded within an application program, if
it is not inside a compound SQL.

Nov 12 '05 #2

Serge Rielau

nsd wrote:

HI ALL,

HERE I HAVE MADE USE OF SAME CURSOR DECLARATIONS AND USAGE IN THE
FOLLOWING TWO DB2 CODES .
BUT I AM GETTING AN ERROR IN THE FIRST CASE i.e CURSOR IN A SIMPLE
BLOCK.

SO I HAVE A QUESTION...
CAN WE MAKE USE OF CURSORS IN A SIMPLE BLOCK?

NOTE: WE CAN MAKE USE OF IT IN CASE OF ORACLE.
PL. HAVE A LOOK AT THE FOLLOWING CODES

-----------------------------------------------------------
1). CURSOR INSIDE A SIMPLE BLOCK (giving error)
-----------------------------------------------------------
BEGIN ATOMIC
DECLARE v_sno INTEGER;
DECLARE my_cur CURSOR FOR SELECT sno FROM t_testforcur;
OPEN my_cur;
FETCH FROM my_cur INTO v_sno;
CLOSE my_cur;
END@ this is "inline" SQL PL. see compound statement (dynamic) for the
supported statements.
Try FOR loop instead.
-----------------------------------------------------------

-----------------------------------------------------------
2). CURSOR INSIDE A PROCEDURE
-----------------------------------------------------------
CREATE PROCEDURE p_testforcur( )
LANGUAGE SQL
SPECIFIC p_test
BEGIN ATOMIC
DECLARE v_sno INTEGER;
DECLARE my_cur CURSOR FOR SELECT sno FROM t_testforcur;
OPEN my_cur;
FETCH FROM my_cur INTO v_sno;
CLOSE my_cur;
END@

regular SQL PL as defined in compound statement (procedure)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3