• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 233
  • Last Modified:

SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open. SQLSTATE=24501

SQL0501N  The cursor specified in a FETCH statement or CLOSE statement is not
open or a cursor variable in a cursor scalar function reference is not open.
SQLSTATE=24501

I am facing the above error while executing a Db2 procedure.

Can you please help me to solve this.
0
Prardhan N
Asked:
Prardhan N
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Panagiotis ToumpaniarisSystem EngineerCommented:
From here :

Explanation

The program attempted to do one of:

    FETCH using a cursor at a time when the specified cursor was not open.
    CLOSE a cursor at a time when the specified cursor was not open.
    Reference a cursor variable in an OPEN statement and the cursor variable is not open.
    Reference a cursor scalar function, such as CURSOR_ROWCOUNT function, and the cursor variable is not open.

The statement cannot be processed.


User response

Check for a previous message (SQLCODE) that may have closed the cursor. Note that after the cursor is closed, any fetches or close cursor statements receive SQLCODE -501.

If no previous SQLCODEs have been issued, correct the application program to ensure that the cursor is open when the FETCH or CLOSE statement is executed.

If a cursor variable is referenced in a cursor scalar function, verify that that the cursor is not null, is defined, and is open, else replace the cursor variable with one that is in that state.
0
 
Prardhan NAuthor Commented:
i could see this help from db2 ? SQL0501N

Can you pls elaborate more?

i am attaching the file with code.

Can you please guide me more.

Thanks a lot for your help
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
No code attachment.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gary PattersonVP Technology / Senior Consultant Commented:
Usually this just means that you tried to FETCH or CLOSE a cursor that isn't open.  Usually this is due to a defect in your code.  Maybe the OPEN failed and you didn't detect it, for example.
0
 
Prardhan NAuthor Commented:
Yes, i have changed my for cursor statements to while cursor and error is not throwing now.

but i need to know how to use the data studio to trace the particular order value (i mean for a particular data value) for step by step
tracing of my procedure code.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Here is a great tutorial on debugging IBM i DB2 stored stored procedures in IBM Data Studio:

https://www.ibm.com/developerworks/ibmi/library/i-debugger-db2-i/index.html
0
 
Prardhan NAuthor Commented:
I have the requirement of dynamic cursor to store a single column with 2 to 3 record values and iterate with in that single column to process.

I have to declare the dynamic cursor with in a while loop. Please help me with respect to this.

Do i need to use refcursor?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
You can easily create a dynamic cursor by building the "declare cursor" statement and executing/preparing it.

DECLARE cursorstatement VARCHAR(1000);
DECLARE c1 CURSOR for s1;
SET cursorstatement = 'SELECT columnlist FROM mytable';
PREPARE s1 FROM cursorstatement;
OPEN c1;

Open in new window


And of course, cursorstatement can be set to any valid SELECT statement, even one generated from another SQL statement(s).


Kent
0
 
Prardhan NAuthor Commented:
The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".. SQLCODE=-911, SQLSTATE=40001, DRIVER=4.18.60
The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".. SQLCODE=-911, SQLSTATE=40001, DRIVER=4.18.60

facing this error while compiling the Procedure.

I have cleared the locks. but still same issue while compilation. Is this happening while prepare
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Can you post the SQL that you're trying to compile?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
By my count, this is the third different question posted in this single thread.  Please close this question and open a new one - you may get more experts to respond.
0
 
Prardhan NAuthor Commented:
Thanks for all your help with this regard. Appreciating your efforts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now