IT25908: PL/SQL PACKAGE WITH AN INITIALIZATION SECTION CAN RETURN -301 ERROR WHEN CALLED VIA JAVA OR CLPPLUS

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

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

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.
  

Problem summary

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

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 4
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IT07700

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP