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