Bad Lengths for OUT and IN OUT CHAR Parameters In Callablestatement (Doc ID 372616.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 10.2.0 and later
Information in this document applies to any platform.

Symptoms

When a PL/SQL procedure is called from Java using OracleCallableStatment and specifying the length with registerOutParameter, the lengths in PL/SQL of OUT and IN OUT parameters are too long.

With JDBC/Thin, the length of both OUT and IN OUT parameters is 32512.

With JDBC/OCI, IN OUT parameters are always 8000. OUT parameters are twice the length defined by registerOutParameter.

It is only the lengths in PL/SQL that are incorrect. The lengths in Java are correct.

A PL/SQL procedure is defined as follows.

create or replace procedure testproc(in_param    in     char,
                                     inout_param in out char,
                                     out_param      out char,
                                     msg            out varchar2) as
begin
    out_param := 'Hi';
    msg := 'length(in_param) = ' || length(in_param) || chr(10) ||
           'length(inout_param) = ' || length(inout_param) || chr(10) ||
           'length(out_param) = ' || length(out_param);
end;
/

A Java application defines parameters as follows.

 cs.registerOutParameter(2, Types.CHAR, 0, 4); 
 cs.registerOutParameter(3, Types.CHAR, 0, 5); 
 cs.registerOutParameter(4, Types.VARCHAR, 0, 200); 

 cs.setString(1, "ABCD"); 
 cs.setString(2, "ABCD");

The Java application executes the procedure and then outputs the results using the code:

 String ans = cs.getString(4); 
 String outp = cs.getString(3); 

 System.out.println("ans is:"); 
 System.out.println(ans); 
 System.out.println(); 
 System.out.println("out param length is " + outp.length()); 
 System.out.println("out param value is '" + outp + "'"); 
 System.out.println();

The output with JDBC/Thin is:

ans is:
length(in_param) = 4
length(inout_param) = 32512 <-- length of 4 expected
length(out_param) = 32512 <-- length of 5 expected

out param length is 5 <-- length in Java is correct
out param value is 'Hi '

The output with JDBC/OCI is:

ans is:
length(in_param) = 4
length(inout_param) = 8000
length(out_param) = 10

out param length is 5
out param value is 'Hi   '

Under certain conditions, the parameter lengths that are too long can result in errors from the PL/SQL procedure.

[1]
An ORA-6502 occurs when using under the following conditions.

A PL/SQL procedure is defined as follows. The ORA-6502 occurs on an assignment statement.

procedure proc2(arg1 in out four_char, 
                arg2    out four_char) is 
    t1 four_char; 
begin 
    t1 := arg1;  -- this stmt will raise ORA-6502 
    arg2 := 'MILO'; 
    t1 := arg2;  -- so will this 
end proc2;

The procedure in is a package, and four_char is defined in the package as: 

subtype four_char is char(4);

A Java application defines parameters as follows.

cs.registerOutParameter(1, Types.CHAR, 0, 4);
cs.registerOutParameter(2, Types.CHAR, 0, 4);

cs.setString(1, "ARG1");

The output is:

making call with call string { call tar2.proc2(?,?)}

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.TAR2", line 9
ORA-06512: at line 1

[2]
An ORA-1460 occurs under the following conditions.

A PL/SQL procedure is defined as follows. The ORA-1460 occurs when parameters are defined using %type.

  procedure proc1(dat_pat_id    in     tar_tab2.pid%type,
                  dat_event_num in out tar_tab2.e_num%type) is
      cursor c1 is
         select e_num
           from tar_tab2
           where pid = dat_pat_id
             and e_num = dat_event_num;

      r1 c1%rowtype;
  begin
      open c1;
      fetch c1 into r1;
      close c1;
  end proc1;

A Java application defines parameters as follows.

cs.registerOutParameter(1, Types.CHAR, 0, 8);
cs.registerOutParameter(2, Types.CHAR, 0, 4);

cs.setString(1, "ABCD1234");
cs.setString(2, "WXYZ");

The output is:

java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SCOTT.TAR2", line 19
ORA-06512: at "SCOTT.TAR2", line 26
ORA-06512: at line 1

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