I have here an Oracle DataWarehouse and have to execute the following function:
function getUserSelection(
p_userID in number,
p_userDate in date,
p_userCompany in string,
p_mode in number default null)
return v_c
According to our DB admin v_c should be a cursor.
It does nor work from Java. What I tried:
String sqlQuery = "{ ? = call getUserSelection(?,?,?,?) }";
CallableStatement callStmt = con.prepareCall(sqlQuery);
callStmt.registerOutParameter("v_c", OracleTypes.CURSOR);
callStmt.setInt("p_userID", 123);
callStmt.setDate("p_userData", new java.sql.Date(System.currentTimeMilis()));
callStmt.setUserCompany("p_userCompany", "Kiwi");
callStmt.set("p_mode", 0);
callStmt.execute();
This leads to the error
not supported sql92 token at position 10
Then tried the following:
String sqlQuery = """
begin ? := getUserSelection(?,?,?,?); end;
""";
CallableStatement callStmt = con.prepareCall(sqlQuery);
callStmt.registerOutParameter("v_c", OracleTypes.CURSOR);
callStmt.setInt("p_userID", 123);
callStmt.setDate("p_userData", new java.sql.Date(System.currentTimeMilis()));
callStmt.setUserCompany("p_userCompany", "Kiwi");
callStmt.set("p_mode", 0);
callStmt.execute();
and am getting
java.sql.Exception ORA-06550 Row 1 Column 20 PLS-00103 Found symbol '>' while expecting :=.(@%;"
Oracle Function itself works fine, I can execute it from SQL Developer without issues.
Any ideas?
7