java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next

Question

I am trying to execute stored procedure in oracle using hibernate.When I call procedure from hibernate the process get called but after that it showing me exception that

ERROR: Cannot perform fetch on a PLSQL statement: next

Here is my stored procedure in oracle :

create or replace PROCEDURE GETREGISTRATIONRECORDS 
(
  REGID IN NUMBER 
) 
AS 
MY_CURSER SYS_REFCURSOR;
BEGIN
OPEN MY_CURSER FOR
SELECT * FROM REGISTRATION
WHERE ID = REGID;
END GETREGISTRATIONRECORDS;

And in hibernate i have called this procedure as shown bellow :

Registration.java

@NamedNativeQueries({
@NamedNativeQuery(
        name="callRegistrationProcedure",
        query="call GETREGISTRATIONRECORDS(:regID)",
        resultClass=Registration.class)
})
@Entity
public class Registration { 
......
}

and in RegistrationDao.java i have below function

@SuppressWarnings("unchecked")
public List<Registration> getFirstName(int id)
{

    Query query = SQLFactory.getSession().getNamedQuery("callRegistrationProcedure").setParameter("regID", id);
    List<Registration> result = query.list();
    for(int i=0; i<result.size(); i++){
        Registration stock = (Registration)result.get(i);
        System.out.println(stock.getFirstName());
        System.out.println(stock.getLastName());

    }

    return result;
}

But When I execute my application i get following error :

Hibernate: call GETREGISTRATIONRECORDS(?)
Apr 09, 2015 10:45:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper   logExceptions
WARN: SQL Error: 17166, SQLState: null
Apr 09, 2015 10:45:24 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Cannot perform fetch on a PLSQL statement: next
Apr 09, 2015 10:45:24 AM org.apache.catalina.core.StandardWrapperValve invoke 
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/SpringHibernateWebApplication] threw exception [Request processing failed; nested exception is org.hibernate.exception.GenericJDBCException: Cannot perform fetch on a PLSQL statement: next] with root cause
java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:192)

I have gone through these links but i did not find the solution

java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next. In Hibernate

Hibernate 4.2.18 Stored procedure call on oracle 11g with ojdbc6(11.2.0.2) gives Cannot perform fetch on a PLSQL statement: next

So please help me to get the the solution.

Answer 1

I got the solution.Following are the changes made in above code

Stored Procedure in oracle :

create or replace PROCEDURE GETREGISTRATIONRECORDS 
(
   MY_CURSER OUT SYS_REFCURSOR,
   REGID IN NUMBER 
) AS 
BEGIN
OPEN MY_CURSER FOR
SELECT * FROM REGISTRATION
WHERE ID=REGID;
END GETREGISTRATIONRECORDS;

in Registration.java

@NamedNativeQuery(
    name="callRegistrationProcedure",
    query="call GETREGISTRATIONRECORDS(?,:regID)",
    callable=true,
    resultClass=Registration.class)
@Entity
public class Registration { 
......
}

No need to do any changes in RegistrationDao.java I got the desired output.Records get fetch successfully from the oracle database.