2

I'm having a problem I haven't encountered before: there is a stored function in a database: CC_PROC, which takes two date entries and returns a table. In other words, to call it, you type:

SELECT * FROM (TABLE( CC_PROC( DATE '2012-01-01', DATE '2012-01-15')));

This seems to work perfectly in SQLPlus and NetBeans, and the above line has been apparently been in use for some time.

Anyway, when calling it from java using a prepared statement, I get: "CC_PROC": invalid identifier on the executeQuery call.

This is with:

PreparedStatement preparedStatement = 
     connection.prepareStatement("SELECT * FROM (TABLE ( CC_PROC( ? , ? )))");
preparedStatement.setDate(1,firstDate);
preparedStatement.setDate(2,secondDate);
resultSet = preparedStatement.executeQuery();

I feel like maybe this is obvious and my limited experience using JDBC directly instead of Hibernate is throwing me. I'd like to not have to re-code the contents of CC_PROC in java business logic. Any ideas?

Thanks!

skaffman
  • 398,947
  • 96
  • 818
  • 769
Decker
  • 459
  • 3
  • 10
  • 1
    Did you try now executing same from sqlplus with hard coding inputs? – kosa Feb 02 '12 at 20:19
  • Yeah; prepareStatement("SELECT * FROM (TABLE ( CC_PROC( DATE '2012-01-01' , DATE '2012-01-15' )))"); Upon execution gives the same error. :/ – Decker Feb 02 '12 at 20:30
  • That means something changed, could be at database (or) your input type. – kosa Feb 02 '12 at 20:31
  • I'm not sure I'm following you; since the hardcoded version doesn't work either, but it uses the same string that works in sqlplus, what does that imply? It can't be a sql.Date conversion problem, since the hardcoded version explicitly names a DATE... – Decker Feb 02 '12 at 20:36
  • Could be and I am not sure that we can use DATE there. Try by removing DATE there. – kosa Feb 02 '12 at 20:42
  • I tried throwing in the to_date(x,y) calls, and had the same issue, but that was a good guess... Thanks for talking through it with me. – Decker Feb 02 '12 at 20:57
  • This topic should help you : http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc – sebastiencol Feb 02 '12 at 20:59

1 Answers1

1

Aha, found the answer:

The oracle user was SALESOWN, so the fix was:

PreparedStatement preparedStatement = connection.prepareStatement(
        "SELECT * FROM (TABLE ( SALESOWN.CC_PROC( ? , ? )))");

Yikes. I don't want to admit the amount of time it took to figure that out.

Apparently SQLPlus and NetBeans do attempt to help out a little...

Thanks for the help guys!

Decker
  • 459
  • 3
  • 10
  • 1
    It's better to do it only once if you are able to: http://stackoverflow.com/questions/2353594/default-schema-in-oracle-connection-url. `ALTER SESSION SET CURRENT_SCHEMA=SALESOWN` – Anthony Accioly Feb 02 '12 at 21:07