My Oracle Support Banner

String Truncated to 4k When Retrieving OUT VARCHAR2 Parameter from Stored Procedure ORA-06502 (Doc ID 353027.1)

Last updated on APRIL 28, 2021

Applies to:

JDBC - Version to [Release 10.1 to 10.2]
Information in this document applies to any platform.
This problem can occur on any platform.


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 ( 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.



      The following procedure returns 32000 chars

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


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.