DB2 FOR LUW / DB2FORLUW / B10 - DB2

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();

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
****************************************************************

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.

not known / see Local fix

forerunner  : IT07700 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

07.08.2018
27.11.2018
27.11.2018