I am calling a Stored Proc in Oracle using Hibernate.

Procedure:

create or replace 
procedure             GetStocks(email in VARCHAR2)
is 

BEGIN
execute immediate 'SELECT * FROM stock WHERE email=email';
   END;

Java Code

 Query query = session.createSQLQuery("CALL GetStocks(:email)").addEntity(Stock.class).setParameter("email", "abc@xyz.com");
    System.out.println("QUERY:"+query.toString());

     System.out.println("QUERY SIZE:"+query.list().size());
     List result = query.list();
    for(int i=0; i<result.size(); i++){
        Stock st = (Stock)result.get(i);
        System.out.println("Address "+st.getdetails());
    }  

I have the following error:

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

1: Is Hibernation neccesary?
2: (99% of all SQL related problems are because of this) Have you ran the actual SQL query by itself to make sure the actual SQL is legal/correct?
3: Another thing, I dont see you actually executing the query, just preparing it.

Please comment on these three things and maybe I can help you further.

1: Yes
2:Yes,individual scripts run successfully.
3.I dont know how to execute.Please correct if am wrong anywhere.

Thanks riahc3.

Can you help me in writing a oracle stored procedure to select mutliple columns from 3 three tables and return it as an object so that i can cast it my required VO.

Thanks riahc3.

Can you help me in writing a oracle stored procedure to select mutliple columns from 3 three tables and return it as an object so that i can cast it my required VO.

I can problably help you write a oracle stored procedure to select mutliple columns from 3 three tables and return it as an object so you could cast it in your required VO........just not with Hibernate.

Not that I have a problem with Hibernate, I just have never used it.

Edited 3 Years Ago by riahc3

This article has been dead for over six months. Start a new discussion instead.