Oracle 11g: Pass array as input parameter to an oracle stored procedure using simple jdbc call

Question

I have the following stored proc:

PROCEDURE test( p_a IN VARCHAR_ARRAY, p_b VARCHAR_ARRAY, p_c IN VARCHAR_ARRAY, 
p_d VARCHAR_ARRAY , p_e NUMBER_ARRAY,  p_f IN NUMBER_ARRAY, p_Results OUT SYS_REFCURSOR);

where

TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type VARCHAR_ARRAY IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;

Now, I'm trying to call this proc from java using SimpleJdbcCallOperations.

SimpleJdbcCallOperations jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withSchemaName(schema)
                .withCatalogName(catalog);
final Result result = (Result) response.results().get(Constants.Dal.RESULTS);
final RowMapper<T> rowMapper = result.mapper();
jdbcCall.returningResultSet(Constants.Dal.RESULTS, rowMapper);
SqlParameterSource in = new SqlParameterSource();
in.addValue("p_a", new SqlArrayValue(a), Types.ARRAY, "VARCHAR_ARRAY");
in.addValue("p_b", new SqlArrayValue(b), Types.ARRAY, "VARCHAR_ARRAY");
in.addValue("p_c", new SqlArrayValue(c), Types.ARRAY, "VARCHAR_ARRAY");
in.addValue("p_d", new SqlArrayValue(d), Types.ARRAY, "VARCHAR_ARRAY");
in.addValue("p_e", new SqlArrayValue(e), Types.ARRAY, "NUMBER_ARRAY");
in.addValue("p_f", new SqlArrayValue(f), Types.ARRAY, "NUMBER_ARRAY");

final List<T> results = (List<T>) jdbcCall.executeObject(response.clazz(), in);

where a,b,c,d are String[] and e,f are BigDecimal[]

But I get the following error:

CallableStatementCallback; uncategorized SQLException for SQL [{call test(?, ?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17059]; Fail to convert to internal representation: [Ljava.lang.String;@5dadd44e; nested exception is java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.String;@5dadd44e"

I've tried many combinations.. using List<> instead of [] and double instead of BigDecimal, different Types, ... Any help would be highly appreciated.

UPDATE:

Types are declared inside stored proc. and i tried adding params by specifying the schema, package name, like this: in.addValue("p_a", new SqlArrayValue(a), Types.ARRAY, "SCHEMANAME.PACKAENAME.VARCHAR_ARRAY");

Oracle version : 11.2.0.3.0 (11g)

@Lukas Eder mentioned in comment that only 12c supports Associative arrays. How do I achieve this in 11g?

What Oracle version are you using? Because prior to 12c, you cannot bind associative arrays to JDBC anyway... — Lukas Eder
– Lukas Eder, Commented Sep 13, 2015 at 16:18
Are those types declared at schema level (i.e. using CREATE OR REPLACE TYPE ...) or are they declared within a package? — Luke Woodward
– Luke Woodward, Commented Sep 13, 2015 at 20:22
@LukeWoodward - It's in procedure, but i tried adding params by specifying the schema, package name, like this: in.addValue("p_a", new SqlArrayValue(a), Types.ARRAY, "SCHEMANAME.PACKAENAME.VARCHAR_ARRAY"); — Ocelot
– Ocelot, Commented Sep 13, 2015 at 21:24
@LukasEder - I'm using 11.2.0.3.0 .. (11g) . How do I achieve the same in 11g? — Ocelot
– Ocelot, Commented Sep 13, 2015 at 21:32

Ocelot · Accepted Answer · 2015-09-14 05:36:11Z

2

Found the solution. Have to convert the Java Arrays to Oracle native Arrays before passing them:

MapSqlParameterSource inParams = new MapSqlParameterSource();    
try {
    DataSource ds = jdbcTemplate.getDataSource();
    Connection conn = DataSourceUtils.getConnection(ds);
    OracleConnection oracleConnection = null;
    if (conn.isWrapperFor(OracleConnection.class))
        oracleConnection = conn.unwrap(OracleConnection.class);

    ARRAY aArrayValue = oracleConnection.createARRAY("VARCHAR_ARRAY", a.toArray());
    ARRAY bArray = oracleConnection.createARRAY("VARCHAR_ARRAY", b.toArray());
    ARRAY cArray = oracleConnection.createARRAY("VARCHAR_ARRAY", c.toArray());
    ARRAY dArray = oracleConnection.createARRAY("VARCHAR_ARRAY", d.toArray());
    ARRAY eArray = oracleConnection.createARRAY("NUMBER_ARRAY", e.toArray());
    ARRAY fArray = oracleConnection.createARRAY("NUMBER_ARRAY", f.toArray());

    inParams.addValue("p_a", aArrayValue);
    inParams.addValue("p_b", bArray);
    inParams.addValue("p_c", cArray);
    inParams.addValue("p_d", dArray);
    inParams.addValue("p_e", eArray);
    inParams.addValue("p_f", fArray);

} catch (SQLException e) {
    e.printStackTrace();
}

answered Sep 14, 2015 at 5:36

Ocelot

1,7534 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yan Pak Over a year ago

It's very important to add to the answer that you need always to close your datasource connection for returning it back to the pool connection! conn.close(); Otherwise, you will get connection leak!

Collectives™ on Stack Overflow

Oracle 11g: Pass array as input parameter to an oracle stored procedure using simple jdbc call

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related