How to call a stored procedure with Hibernate ?

Supposing you have the following Oracle Stored procedure:

CREATE FUNCTION agenda RETURN SYS_REFCURSOR
AS 
    my_cursor SYS_REFCURSOR;
BEGIN
    OPEN my_cursor FOR
        SELECT id,person_name,person_surname,person_address
        FROM Person;
    RETURN  my_cursor;
 END;

Invoking it from Hibernate requires mapping the Stored Procedure in the Person class.

<sql-query name="SP_agenda" callable="true">
    <return alias="ev" class="Person">
        <return-property name="id" column="id"/>
        <return-property name="name" column="person_name"/>
        <return-property name="surname" column="person_surname"/>
        <return-property name="address" column="person_address"/>
    </return>
    { ? = call agenda() }
</sql-query>

Then, you can execute the procedure just like a normal Query:

Query query = session.getNamedQuery("SP_agenda");
List results = query.list();