Query q=sess.getNamedQuery("getPeople");
q.setString (0,"John");
Person p=null;
Iterator itr=q.list().iterator();
while(itr!=null&&itr.hasNext())
{
    p=(Person) itr.next();
   System.out.println("p.PERSON_ID=" + p.getPERSON_ID());
   System.out.println("p.FIRST_NAME=" + p.getFIRST_NAME());
   System.out.println("p.LAST_NAME=" + p.getLAST_NAME());
}

<hibernate-mapping>
   <class>
....
    </class>

<sql-query name="getPeople" callable= "true">
     <return class="com.xyz.Person" />
        { ? = call getPeople(?) }
</sql-query>

</hibernate-mapping>

create or replace function getPeople(arg varchar)
    RETURN SYS_REFCURSOR AS
    st_cursor SYS_REFCURSOR;
    BEGIN

      open st_cursor for
      select * from person where upper(first_name)=upper(arg);

    RETURN  st_cursor;

end;
/