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

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

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

    } );