Calling stored function error in Java

Question

I am trying to call a stored function in Java. Function has no package, it's placed under user (schema) USER and returns a cursor. I tried twoways to call it and none of these works.

First one

Query query = coreDao.getEntityManager().createNativeQuery("{call USER.gen_rephead_sm_task_report(?, ?) }");
query.setParameter(1, dateFrom);
query.setParameter(2, dateTo);
List<?> queryResult = query.getResultList();

I got a not a procedure or not defined

I also tried this approach with

select gen_rephead_sm_task_report(?, ?) from dual

as a createNativeQuery parameter but with the same result.

Second one

Connection connection = dataSource.getConnection();//javax.sql.DataSource
CallableStatement statement = connection.prepareCall("{? = call USER.gen_rephead_sm_task_report(?, ?) }");
statement.registerOutParameter(1, OracleTypes.CURSOR);//oracle.jdbc.OracleTypes
statement.setDate(2, new java.sql.Date(dateFrom.getTime()));
statement.setDate(3, new java.sql.Date(dateTo.getTime()));
statement.executeQuery();
ResultSet set = ((OracleCallableStatement) statement).getCursor(1);

I got the ClassCastException on the last line (obviously OracleCallableStatement doesn't implement the CallableStatement). So which types shoud I use here?

try `statement.execute()` instead of `statement.executeQuery()` — MaVRoSCy, Nov 02 '12 at 09:31
are you connecting as the owner of the procedure or another user? also you have USER. in your code sample, i'm assuming that in your real code you don't have the string "USER" but the actual name of the schema? if you're connecting as another user, does that user have a package called as the same name as the schema object your calling? using SQLPLUS connect as the user you're connecting as in your JAVA code and do "DESC USER.gen_rephead_sm_task_report" what do you get (substituting USER for your real schema name of course)? — DazzaL, Nov 02 '12 at 10:26
Can you check your driver is only available in one place? It should not be in WAR and under lib folder — Jacob, Nov 02 '12 at 10:29

score 0 · Accepted Answer · answered Nov 02 '12 at 11:14

Ok, so I found a link which solves my problem (iDevelopment). Here is what I used

connection = dataSource.getConnection();
CallableStatement statement = connection.prepareCall(TIMESHEET_QUERY);
statement.registerOutParameter(1, OracleTypes.CURSOR);
statement.setDate(2, new java.sql.Date(dateFrom.getTime()));
statement.setDate(3, new java.sql.Date(dateTo.getTime()));
statement.execute();

ResultSet set = (ResultSet) statement.getObject(1);

Everything is straight from javax.sql.* so there is no need for Oracle Specific API.

Calling stored function error in Java

1 Answers1