call Oracle stored procedure with array output argument from Java

Question

I have a problem will calling plsql procedure from Java. Package with procedure is below: (schema job_runner and connection user/scheme is not same):

create or replace package  test_package_for_sp as

  type some_record_type is record
  (
      field_number      number,
      field_varchar2    varchar2 (128),
      field_date        date
  );

  type some_table_type is table of some_record_type;


  procedure proc_table (p_card_bin    in     varchar2,
                        p_date        in     date default null,
                        p_out_table      out some_table_type);
}

And then I try to call it from Java with callableStatement:

    final String typeTableList = "SOME_TABLE_TYPE";

    CallableStatement cs = null;
    try (Connection con = dataSource.getConnection()) {
        con.setSchema("JOB_RUNNER");

        cs = con.prepareCall("{call job_runner.test_package_for_sp.proc_table(?, ?, ?)}");

        cs.setString(1, "54867321");
        cs.setDate(2, Date.valueOf(ZonedDateTime.now().minusDays(200).toLocalDate()));
        cs.registerOutParameter(3, Types.ARRAY, typeTableList);

        cs.execute();
    } finally {
        if (cs != null)
            cs.close();
    }

Error raise:

java.sql.SQLException: invalid name pattern: <connection_scheme>.SOME_TABLE_TYPE

    at oracle.jdbc.oracore.OracleTypeADT.initMetadata11_2(OracleTypeADT.java:764)
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:479)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:443)

If I change value in typeTableLis from value SOME_TABLE_TYPE to full path with package and scheme JOB_RUNNER.TEST_PACKAGE_FOR_SP.SOME_TABLE_TYPE exception change to:

java.sql.SQLSyntaxErrorException: ORA-01948: identifier's name length (35) exceeds maximum (30)
ORA-06512: at "SYS.DBMS_PICKLER", line 18
ORA-06512: at "SYS.DBMS_PICKLER", line 58
ORA-06512: at line 1


    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

Does anybody know how to call this procedure from java?

Answer 1

You can set the JDBC connection property "oracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName" to "true", then switch the schema to "job_runner" (ALTER SESSION SET CURRENT_SCHEMA=job_runner) and use TEST_PACKAGE_FOR_SP.SOME_TABLE_TYP for typeTableList.

Answer 2

One of the approach:

package testproject;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.sql.ARRAY;

import oracle.jdbc.OracleCallableStatement;

import oracle.sql.ArrayDescriptor;

    public class MainClass {
    public MainClass() {
        super();
    }

    public static void main(String[] args) {
        MainClass mainClass = new MainClass();

        Connection conn = null;

        ArrayDescriptor des = null;

        try {

            Object[] obj1 = { 1, "2017-01-01 10:12:10", 200 };
            Object[] obj2 = { 2, "2017-06-01 10:12:10", 600 };
            Object[] obj3 = { 3, "2017-08-01 10:12:10", 990 };

            conn =
DriverManager.getConnection("jdbc:oracle:thin:@<DB_HOST>",
                            "<user>", "<pass>");

          try {
              des = ArrayDescriptor.createDescriptor("AJ_TEST_OBJ_TBL", conn);
          } catch (SQLException e) {
              System.out.println("Arraydesc went wrong.");
              System.out.println(e.getStackTrace());
          }

          ARRAY nArray =
              new ARRAY(des, conn, new Object[] { obj1, obj2, obj3 });

            OracleCallableStatement pstmt =
                (OracleCallableStatement)conn.prepareCall("begin 
aj_test_array_pck.print_tbl_parameters(?,?); end;");

            pstmt.setArray(1, nArray);
            pstmt.registerOutParameter(2, Types.VARCHAR);
            pstmt.execute();

            String status = pstmt.getString(2);
            System.out.println("Status: " + status);

            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("Oops with select");
            System.out.println(e.getStackTrace());
        } catch (ClassNotFoundException e) {
            System.out.println("Oops with class");
        }
    }

}

where "AJ_TEST_OBJ_TBL" is a SQL type object.