使用java SimpleJdbcCall调用out参数类型为“is table of Varchar2”的过程oracle

Question

我创建了一个有两个参数的过程oracle，其中一个是out参数类型表的varchar2。如何在java中调用并得到结果？

下面创建的测试过程：

/* creating package with specs */
create or replace PACKAGE PACK1 AS
  TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE proc_filter_and_return_array( p_name_in  IN  VARCHAR2, p_name_out_array OUT name_array );
END PACK1;

/* creating package body with procedure */
create or replace PACKAGE BODY PACK1
as
    PROCEDURE proc_filter_and_return_array(
                p_name_in   IN       VARCHAR2,
                p_name_out_array OUT name_array
    )IS
                CURSOR c_table1_select is
                         select name FROM table1_test where name like '%' || p_name_in  || '%';
                v_index NUMBER := 0;
    BEGIN
            FOR x IN c_table1_select
             LOOP     
               p_name_out_array( v_index ) := x.name;
               v_index := v_index + 1;  
             END LOOP; 
    
    END proc_filter_and_return_array; 
END PACK1;

DECLARE
    p_name_array pack1.name_array;
BEGIN
    pack1.proc_filter_and_return_array(p_name_in => 'name_to_filter', p_name_out_array => p_name_array);
    dbms_output.put_line(' number from table: ' || p_name_array(1) );
END;

    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
        .withCatalogName("PACK1") 
        .withProcedureName("PROC_FILTER_AND_RETURN_ARRAY") 
        .declareParameters( new SqlParameter("P_NAME_IN", Types.VARCHAR) )
        .declareParameters( new SqlOutParameter("P_NAME_OUT_ARRAY", Types.ARRAY, "PACK1.NAME_ARRAY" ));
        
    MapSqlParameterSource map = new MapSqlParameterSource();
    map.addValue("P_NAME_IN", "name_to_filter");

    Map<String, Object> result = simpleJdbcCall.execute(map);

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call PACK1.PROC_FILTER_AND_RETURN_ARRAY(?, ?)}]; 
SQL state [99999]; error code [17074]; invalid name pattern: PACK1.NAME_ARRAY; nested exception is java.sql.SQLException: invalid name pattern: PACK1.NAME_ARRAY] with root cause
java.sql.SQLException: invalid name pattern: PACK1.NAME_ARRAY
        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)
        at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1499)
        at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:274)
        at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:127)
        at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:79)
        at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:83)
        at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:76)
        at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:599)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:201)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:240)
        at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1243)
        at com.zaxxer.hikari.pool.HikariProxyCallableStatement.registerOutParameter(HikariProxyCallableStatement.java)
        at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:188)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1090)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1147)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:372)
        at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198)

不幸的是，我无法更改客户机数据库中的任何内容：（因此，我无法更改声明类型name_array是binary_integer的VARCHAR2(50)索引表；，我需要用java构建一个应用程序，spring boot。

我做错了什么？提前谢了。

Answer 1

其中之一是varchar2的out参数类型表

您搞错了，其中一个是由binary_integer索引的VARCHAR2(50)的表，它是一个关联数组，您不能使用JDBC映射到这种类型。

看到这个答案或者这个答案。

null

CREATE TYPE name_array IS TABLE OF VARCHAR2(50);

您可以尝试通过调用匿名PL/SQL块（该块反过来调用包）将关联数组转换为varray，例如sys.odcivarchar2list:

DECLARE
  v_name_assoc_array PACK1.NAME_ARRAY;
  v_name_list        SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_idx              BINARY_INTEGER;
BEGIN
  PACK1.proc_filter_and_return_array( :p_name_in, v_name_assoc_array );

  v_idx := v_name_assoc_array.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    v_name_list.EXTEND;
    v_name_list(v_name_list.COUNT) := v_name_assoc_array(v_idx);
    v_idx := v_name_assoc_array.NEXT(v_idx);
  END LOOP;

  :p_name_out_array := v_name_list;
END;
/