从 Java 调用带有数组输出参数的 Oracle 存储过程

Question

我有一个问题会从 Java 调用 plsql 过程。程序包如下：（模式job_runner和连接用户/方案不同）：

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);

}

然后我尝试使用 callableStatement 从 Java 调用它：

    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();

    }

错误引发：

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)

如果我将值typeTableLis从值更改SOME_TABLE_TYPE为完整路径，并且包和方案JOB_RUNNER.TEST_PACKAGE_FOR_SP.SOME_TABLE_TYPE异常更改为：

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)

有人知道如何从java调用这个过程吗？

Answer 1

您可以将 JDBC 连接属性设置"oracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName"为"true"，然后将架构切换为"job_runner"(&nbsp;ALTER SESSION SET CURRENT_SCHEMA=job_runner) 并TEST_PACKAGE_FOR_SP.SOME_TABLE_TYP用于 typeTableList。

Answer 2

方法之一：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;&nbsp; &nbsp; public class MainClass {&nbsp; &nbsp; public MainClass() {&nbsp; &nbsp; &nbsp; &nbsp; super();&nbsp; &nbsp; }&nbsp; &nbsp; public static void main(String[] args) {&nbsp; &nbsp; &nbsp; &nbsp; MainClass mainClass = new MainClass();&nbsp; &nbsp; &nbsp; &nbsp; Connection conn = null;&nbsp; &nbsp; &nbsp; &nbsp; ArrayDescriptor des = null;&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Object[] obj1 = { 1, "2017-01-01 10:12:10", 200 };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Object[] obj2 = { 2, "2017-06-01 10:12:10", 600 };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Object[] obj3 = { 3, "2017-08-01 10:12:10", 990 };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; conn =DriverManager.getConnection("jdbc:oracle:thin:@<DB_HOST>",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "<user>", "<pass>");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; des = ArrayDescriptor.createDescriptor("AJ_TEST_OBJ_TBL", conn);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (SQLException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Arraydesc went wrong.");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(e.getStackTrace());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ARRAY nArray =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new ARRAY(des, conn, new Object[] { obj1, obj2, obj3 });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OracleCallableStatement pstmt =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (OracleCallableStatement)conn.prepareCall("begin&nbsp;aj_test_array_pck.print_tbl_parameters(?,?); end;");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pstmt.setArray(1, nArray);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pstmt.registerOutParameter(2, Types.VARCHAR);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pstmt.execute();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String status = pstmt.getString(2);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Status: " + status);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; pstmt.close();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; conn.close();&nbsp; &nbsp; &nbsp; &nbsp; } catch (SQLException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Oops with select");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(e.getStackTrace());&nbsp; &nbsp; &nbsp; &nbsp; } catch (ClassNotFoundException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Oops with class");&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}其中“AJ_TEST_OBJ_TBL”是一个 SQL 类型的对象。