DB2 - Problembeschreibung
Problem IC87406 | Status: Geschlossen |
DYNAMIC PL/SQL CURSORS ARE CLOSED AFTER A COMMIT | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
If your PL/SQL application opens a cursor using dynamic SQL text, the cursor will be closed after a COMMIT operation. This is incorrect for PL/SQL code, where cursors should remain open after a commit. In other words, they should exhibit the same behaviour as an equivalent SQL PL cursor declared with the WITH HOLD attribute. The following test script demonstrates the behaviour. The CLOSE statement should complete successfully; instead, it fails with error SQL0501N: 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. /* db2set DB2_COMPATIBILITY_VECTOR=ORA */ set sqlcompat plsql; set serveroutput on; declare dummy number(10); type r_cursor is REF CURSOR; dyn_cursor r_cursor; sql_text varchar(2000); begin -- Open a dynamic cursor sql_text := 'SELECT 1 FROM DUAL' ; open dyn_cursor for sql_text; dbms_output.put_line('Dynamic cursor opened.'); loop fetch dyn_cursor into dummy; exit when dyn_cursor%NOTFOUND; commit; dbms_output.put_line('Value fetched from dynamic cursor.'); end loop; -- The cursor should still be open after the COMMIT. close dyn_cursor; dbms_output.put_line('Dynamic cursor closed.'); end; / connect reset; terminate; | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 2. * **************************************************************** | |
Local-Fix: | |
If a dynamic cursor is required and it is necessary to keep the cursor open through a commit operation, consider writing a helper routine in SQL PL (instead of PL/SQL) to dynamically open a cursor variable WITH HOLD. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
Lösung | |
This problem is fixed in DB2 Version 10.1 Fix Pack 2. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 22.10.2012 18.01.2013 18.01.2013 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.2 | |
10.5.0.2 |