By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,225 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,225 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.