IBM Support

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

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

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:                                              *
    * See SYSROUTE APARs to see where this APAR is addressed       *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IT07700

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-03-13

  • Closed date

    2017-05-08

  • Last modified date

    2017-05-08

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

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

    IT07786

Fix information

Applicable component levels

  • RA50 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 May 2017