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.

3.I dont know how to execute.Please correct if am wrong anywhere.

Ah, OK. Thats problably it :)

Looking over documents, it seems it would be "doWork".

http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/Session.html

http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/Session.html#doWork%28org.hibernate.jdbc.Work%29

I havent worked with Hibernate too much but I have worked with stored procedures in Oracle and Java. Any more questions, please ask.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.