Oracle and Jdbc and OraclePreparedStatement#setPlsqlIndexTable and java.util.Date

Question

I´m a little bit frustrated with my Java environment that didn´t allow me to call the method OraclePreparedStatement#setPlsqlIndexTable ... but i think i should write the code before...

String plSqlBody = "some pl/sql procedure call"
/*
 * The PL/SQL procedure parameter is here of type
 * TYPE t_date_table IS TABLE OF DATE INDEX BY PLS_INTEGER;
 */
OracleCallableStatement ocs = (OracleCallableStatement)
        conn.prepareCall(plSqlBody);
java.util.Date[] date = new java.util.Date[10];
// Initialise the array... and then...
ocs.setPlsqlIndexTable(index, date, 20, 20, OracleTypes.DATE, 20);

And here i get my exception:

java.sql.SQLException: Ungültiger PL/SQL-Indextabellen-Elementtyp
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

I didn´t find a solution for the problem. I changed the Java type from java.util.Date to java.sql.Date / oracle.sql.DATE and OracleTypes.DATE to OracleTypes.TIME / OracleTypes.TIMESTAMP, but nothing solved the problem. I find somewhere the hint, that these types are not allowed here, but i can´t believe it. Do you know the right way here?

java.util.Date date = new java.util.Date[10] doesn't compile. Please modify your question with code that compiles, otherwise it's hard to see what's wrong. — skaffman
– skaffman, Commented Nov 18, 2009 at 8:27

SimonJ · Accepted Answer · 2009-11-18 08:24:54Z

1

From the Oracle docs (JDBC Developer's Guide, JDBC OCI Extensions chapter):

Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.

Maybe you could pass the dates in as strings/numbers instead?

answered Nov 18, 2009 at 8:24

SimonJ

21.3k2 gold badges38 silver badges51 bronze badges

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

1 Comment

andrewinkler Over a year ago

That would by an uncomfortable solution. I don´t know the reason, why Oracle´s JDBC driver does not support DATE here. I saw the same problem at a C# project. The Oracle .NET driver there seems to allow to pass an array of TIMESTAMP objects to the procedure. But i´m not an .NET expert.

Collectives™ on Stack Overflow

Oracle and Jdbc and OraclePreparedStatement#setPlsqlIndexTable and java.util.Date

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