The
doc says:
The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate.
And a bit later:
For Oracle the following rules apply:
- A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set.
So having a second parameter for OUT in a procedure is not supported with Oracle.
I can confirm that with:
Code:
<sql-query name="selectAllEventsLike2" callable="true" >
<return class="org.hibernate.tutorial.hbm.Event"/>
{ call USER1.selectAllEventsLikeP(?,?)}
</sql-query>
// java
List result = session.getNamedQuery("selectAllEventsLike2")
.setParameter(0, "%foo%")
.list();
// SQL
CREATE OR REPLACE PROCEDURE selectAllEventsLikeP (
in_title_pat in varchar2,
st_cursor out SYS_REFCURSOR )
AS
BEGIN
OPEN st_cursor FOR
SELECT EVENT_ID,
EVENT_DATE,
TITLE
FROM EVENTS
WHERE TITLE LIKE in_title_pat;
END;
This works.
Using second parameter for out does not:
Code:
<sql-query name="selectAllEventsLike2" callable="true" >
<return class="org.hibernate.tutorial.hbm.Event"/>
{ call USER1.selectAllEventsLikeP(?,?)}
</sql-query>
// java
List result = session.getNamedQuery("selectAllEventsLike2")
.setParameter(1, "%foo%")
.list();
// SQL
CREATE OR REPLACE PROCEDURE selectAllEventsLikeP (
st_cursor out SYS_REFCURSOR,
in_title_pat in varchar2
)
AS
BEGIN
OPEN st_cursor FOR
SELECT EVENT_ID,
EVENT_DATE,
TITLE
FROM EVENTS
WHERE TITLE LIKE in_title_pat;
END;
What amount of work would be required to support procedures, that use an OUT parameter that is not the first?
Patch the Oracle dialect support code?
More?
Login
FAQ
Search
