Are you referring to chapter 13. Native SQL Queries?
I have read that but that only show me how to run custom select statements and the syntax is still using {cat.*} type of syntax, not ansi sql-92 syntax.
Code:
Query sqlQuery = sess.createSQLQuery("select {cat.*} from cats {cat}", "cat", Cat.class);
sqlQuery.setMaxResults(50);
List cats = sqlQuery.list();
I honestly don't know how to map that onto my stored procedure syntax.
Example stored procedure (simplified to avoid too much details on this post):
Code:
TYPE jobcode_cursor IS REF CURSOR RETURN jobcode%ROWTYPE;
FUNCTION findByPrimaryKey (v_id IN VARCHAR2) RETURN jobcode_cursor
IS
c jobcode_cursor;
BEGIN
OPEN c CURSOR FOR
SELECT id, code, description
FROM jobcode
WHERE id - v_id;
RETURN c;
END;
Calling the stored procedure is done with:
Code:
Connection connection = ...;
CallableStatement statement = connection.prepareCall("BEGIN ? := findByPrimaryKey(?); END;");
statement.setString(1, ...);
statement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
statement.execute();
java.sql.ResultSet results = (java.sql.ResultSet) statement.getObject(1);
while (results.next()) {
/* get the various attributes and stuff them in a jobcode business object. */
}
/* Close all database resources. */
Can you tell me how to map that onto acceptable syntax for createSQLQuery(String) please?
Many thanks,
Rudi
Login
FAQ
Search
