String Truncated to 4k When Retrieving OUT VARCHAR2 Parameter from Stored Procedure ORA-06502

(Doc ID 353027.1)

Last updated on MAY 19, 2017

Applies to:

JDBC - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.

Symptoms

When using a CallableStatement to execute a function returning a large VARCHAR2  or a procedure with a large OUT VARCHAR2 parameter (  size between  4K and 32K), the Oracle 10g JDBC drivers ( both 10.1. and 10.2) will truncate the return value if the function or procedure is invoked using the "{ call  xxx() }" syntax.

When using JDBC 10.x to connect to a 10g database ( 10.1 and 10.2) the call succeeds and truncates to 4k.

When using JDBC 10.x to connect to a 9.2 database ( 9.2.0.7 tested) the call throws ORA-06512.

When using JDBC 9.x drivers the complete length of the varchar is returned up to 32k in size.

 

TestCase:

      The following procedure returns 32000 chars

procedure test6502( result out varchar2) is
begin
    for i in 1..1000 loop
    result := result || 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; -- appending 32 chars
 
    end loop;
  return;
end;
    connecting to 10.2.0.1 database:
    ---------------------------------------------------------------
            Connection c = getConnection();
    //        CallableStatement s = c.prepareCall("begin test6502(?); end;");
            CallableStatement s = c.prepareCall("{ call test6502(?)}");
            s.registerOutParameter(1,Types.VARCHAR);
            s.execute();
            String aStr = s.getString(1);
            System.out.println(aStr.length());
   
           c.close();
    -------------------------------------------------------
    Using the JDBC 10.2.0.1 driver:
    4000
    only 4000 bytes are returned
    --------------------------------------------------------
    Using the the JDBC 9.2.0.7 Driver:
    32000
    The full result is returned
    --------------------------------------------------------
    When connecting to a 9.2 database using the JDBC 10.2.0.1 the result is different:
    Exception in thread main
    java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SCOTT.TEST6502", line 4
    ORA-06512: at line 1
   
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    -------------------------------------------------------------------------------------
    If we modify the code: to use the begin-end semantic:
    --------------------------------------------------------
    Using the the JDBC 10.2.0.1 Driver on both the 10.2 and the 9.2 database
    32000
    The full result is returned
    --------------------------------------------------------

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms