470,864 Members | 2,010 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,864 developers. It's quick & easy.

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
2 1949
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
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.

Similar topics

1 post views Thread by Guillaume Mallet | last post: by
2 posts views Thread by cmitchell | last post: by
6 posts views Thread by a | last post: by
5 posts views Thread by Boni | last post: by
2 posts views Thread by Lars Netzel | last post: by
7 posts views Thread by H. Williams | last post: by
28 posts views Thread by Steven Bethard | last post: by
7 posts views Thread by Steven Bethard | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.