DB2 - Problembeschreibung
Problem IT00382 | Status: Geschlossen |
SELECT INTO GLOBAL VARIABLE WITH 'FOR UPDATE' SPECIFIED RETURNS SQL0151N ERROR | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
When you specify the FOR UPDATE clause in a SELECT INTO statement that assigns a value to a global variable, module variable, or PL/SQL package variable, DB2 reports error SQL0151N. The following script illustrates the error: set serveroutput on % create table staff(id integer, name varchar(32), dept smallint) % insert into staff values(10, 'Smith', 123) % CREATE OR REPLACE MODULE M1 % ALTER MODULE M1 ADD VARIABLE ch_name ANCHOR staff.name % ALTER MODULE M1 PUBLISH PROCEDURE example(in p_id anchor to staff.id) BEGIN declare l_name anchor to staff.name; SELECT name INTO ch_name FROM staff WHERE id = p_id FOR UPDATE WITH RS; call dbms_output.put_line(ch_name); END % call m1.example(10) % DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0151N The column "NAME" cannot be updated. LINE NUMBER=3. SQLSTATE=42808 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to DB2 Version 10.5 Fix Pack 4. * **************************************************************** | |
Local-Fix: | |
Declare a local variable to receive the value from the SELECT INTO, and then assign the local variable to the global variable. For example: ALTER MODULE M1 PUBLISH PROCEDURE example(in p_id anchor to staff.id) BEGIN declare l_name anchor to staff.name; SELECT name INTO l_name FROM staff WHERE id = p_id FOR UPDATE WITH RS; set ch_name = l_name; call dbms_output.put_line(ch_name); END % | |
verfügbare FixPacks: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Lösung | |
Delivered in DB2 Version 10.5 Fix Pack 4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 18.03.2014 08.09.2014 08.09.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |