DB2 - Problembeschreibung
Problem IT25908 | Status: Geschlossen |
PL/SQL PACKAGE WITH AN INITIALIZATION SECTION CAN RETURN -301 ERROR WHEN CALLED VIA JAVA OR CLPPLUS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problembeschreibung: | |
A PL/SQL package that contains an initialization section can return an SQL0301N error when the routine from the package is executed via CLPPlus or by a Java application. For example create the following PL/SQL statements: CREATE OR REPLACE PACKAGE tds.cursor_test_err IS FUNCTION get_data(p_cursor_out OUT SYS_REFCURSOR) RETURN VARCHAR2; END cursor_test_err; @ CREATE OR REPLACE PACKAGE BODY tds.cursor_test_err IS g_category VARCHAR2(20); FUNCTION get_data(p_cursor_out OUT SYS_REFCURSOR) RETURN VARCHAR2 IS BEGIN OPEN p_cursor_out FOR SELECT tabschema, tabname FROM syscat.tables WHERE tabschema = 'SYSIBM' FETCH FIRST 5 ROWS ONLY; RETURN 'OK'; EXCEPTION WHEN OTHERS THEN RETURN 'ERR'; END get_data; BEGIN g_category := 'TDS'; END cursor_test_err; @ Then using CLPPlus issue the following three steps: var rc refcursor var L_return varchar2 exec :L_return := tds.cursor_test_err.get_data(:rc); ... the third step will return an SQL0301N error: SQL0301N The value of input variable, expression or parameter number "2" cannot be used because of its data type. A Java application using the IBM DB2 JDBC provider would also fail if executing statements like this: String plsql = "" + " declare " + " L_return varchar2(20); " + " rc SYS_REFCURSOR;" + " begin " + " L_return := tds.cursor_test_err.get_data(:rc); " + " end;"; CallableStatement cs = c.prepareCall(plsql); cs.registerOutParameter(1, Types.VARCHAR); cs.execute(); | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher * **************************************************************** | |
Local-Fix: | |
Remove the initialization section from the PL/SQL package body and implement that logic some other way. Or Move the offending routine out of the package altogether. | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : IT07700 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 07.08.2018 27.11.2018 27.11.2018 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version |