Interview Questions   Tutorials   Discussions   Programs   

DB2 - How to resolve -504 sql code in DB2?




369
views
asked mar August 3, 2014 06:15 AM  

How to resolve -504 sql code in DB2?


           

1 Answers



 
answered By Mswami   0  
SQL Code: -504
CURSOR NAME cursor-name IS NOT DECLARED

Explanation
:
Cursor cursor-name was referenced in an SQL statement, and one of the following is true:
  1. Cursor cursor-name was not declared (using the DECLARE CURSOR statement) or allocated (using the ALLOCATE CURSOR statement) in the application program or SQL routine before it was referenced.
  2. Cursor cursor-name was referenced in a positioned UPDATE or DELETE statement which is not a supported operation for an allocated cursor.
  3. Cursor cursor-name was allocated, but a CLOSE cursor statement naming cursor-name was issued and deallocated the cursor before this cursor reference.
  4. Cursor cursor-name was allocated, but a ROLLBACK operation occurred and deallocated the cursor before this cursor reference.
  5. Cursor cursor-name was allocated, but its associated cursor declared in a stored procedure was not declared WITH HOLD, and a COMMIT operation occurred and deallocated the cursor before this cursor reference. The COMMIT operation can be either explicit (the COMMIT statement) or implicit (that is, a stored procedure defined as COMMIT_ON_RETURN = 'Y' was called before this cursor reference).
  6. Cursor cursor-name was allocated, but its associated stored procedure was called again since the cursor was allocated, new result sets were returned, and cursor cursor-name was deallocated.
  7. The declaration of a cursor cursor-name was not in scope for the reference to a cursor named cursor-name.
System action:
The statement cannot be processed.

Programmer response
:
Check the application program or SQL routine for completeness and for a possible spelling error in the cursor declaration or allocation. The declaration for or allocation of a cursor must appear in an application program or SQL routine before SQL statements that reference the cursor.

If the cursor-name was <UNKNOWN>, the cursor was not successfully declared or allocated. This error can occur if SQL(DB2) was used, and a warning message was issued during precompilation. Check the precompile output for warning messages on the DECLARE CURSOR or ALLOCATE CURSOR statement, and correct the statement.

For an allocated cursor, if an implicit or explicit COMMIT, ROLLBACK, or CLOSE occurred since the cursor was successfully allocated, modify the application program logic to do one of the following actions:
  • After the COMMIT, ROLLBACK, or CLOSE operation, call the associated stored procedure again, and reissue the ASSOCIATE LOCATORS and ALLOCATE CURSOR statements.
  • For COMMIT, declare the associated cursor in the stored procedure WITH HOLD so the COMMIT operation does not deallocate the cursor.
For an allocated cursor, if the associated stored procedure was called again and new result sets were returned since the cursor was allocated, reissue the ASSOCIATE LOCATORS and ALLOCATE CURSOR statements.
 
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert