Call procedure oracle with out parameters type "is table of varchar2" using java SimpleJdbcCall

Question

I've created a procedure oracle with 2 parameters, one of them is a out parameter type TABLE OF VARCHAR2 . how to call it in java and get result?

My test procedure created below:

/* 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;

When I'm testing it in oracle I got successfully with the code below:

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;

But in java I got some errors, I'm doing this way to call the procedure:

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

So I got this on running from java:

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)

unfortunately, I couldn't change anything in client's database :( so I can't change the declaration TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; and I need to build a application in java, spring boot. Is there some way to do this without change procedure and package on oracle.

What I'm doing wrong? Thanks in advance.

Answer 1

one of them is a out parameter type TABLE OF VARCHAR2

You are mistaken, one of them is a TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER which is an associative array and you cannot map to this type using JDBC.

See this answer or this answer.

1. You need to define the data type in the SQL scope using CREATE TYPE (rather than in the PL/SQL scope, in a package) as JDBC can only work with SQL defined data types.
2. A consequence of point 1 is that JDBC does not support associative arrays, as they are a PL/SQL only data type, and you need to use collections (unlike C#, which only supports associative arrays and does not support collections). So you need to remove the INDEX BY clause from the type.

Declare the type outside the package using:

CREATE TYPE name_array IS TABLE OF VARCHAR2(50);

Then remove the associative array declaration from the package and use the new collection type instead. There may be more errors after this that you need to debug; but it is never going to work using an associative array.

unfortunately, I couldn't change anything in client's database :( so I can't change the declaration TYPE name_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; and I need to build a application in java, spring boot. Is there some way to do this without change procedure and package on oracle.

You could try to work around it by calling an anonymous PL/SQL block (which, in turn, calls the package) to convert the associative array to a VARRAY such as 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;
/

Answer 2

I'm here to show de code as MTO has answered to me. working around calling an anonymous PL/SQL block from java and get the return was the best solution.

I finally got the code below successfully:

    // declaring a plsql block calling procedure and treating out return parameters
    String plSql =  " DECLARE "+
                    "    p_name_array PACK1.NAME_ARRAY "+
                    "    p_name_out_array_return SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); "+
                    "    v_idx BINARY_INTEGER; "+
                    " BEGIN "+
                    " "+
                    "     pack1.proc_filter_and_return_array( p_name_in => ? , p_name_out_array => p_name_array); "+
                    " "+ 
                    "     v_idx := p_name_array.first; "+
                    "     WHILE v_idx IS NOT NULL LOOP  "+
                    "        p_name_out_array_return.extend;  "+
                    "        p_name_out_array_return(idx + 1 ) :=  p_name_array(idx); "+
                    "        v_idx := p_name_array.next(v_idx); "+
                    "    END LOOP;  "+
                    " "+  
                    "   ? :=  p_name_out_array_return; "+
                    " END; ";

    // calling plsql from jdbcTemplate
    jdbcTemplate.execute( new CallableStatementCreator(){
        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement cs = con.prepareCall(plSql);   
                              cs.setString(1, "value to first parameter ? in plsql");
                              // registring out second param p_name_out_array_return used in plsql
                              cs.registerOutParameter(2, Types.ARRAY , SYS.ODCIVARCHAR2LIST);
            return cs;
        }
    } , new CallableStatementCallback<Object>(){
        @Override
        public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
            cs.execute();
            // do something with result out param 2 
            Arrays.asList((Object[])cs.getArray(2).getArray()).forEach(System.out::println);

            return null;
        }

    } );