?
Solved

Calling Stored Procedures as Named Query in Hibernate

Posted on 2011-05-03
37
Medium Priority
?
2,661 Views
Last Modified: 2012-05-11
I am trying to call a Stored Procedure in Hibernate which is declared as a Named Query in the mapping file.The below is the code and the error that I am getting.

Stored Procedure Code in Oracle :-
----------------------------------------
CREATE OR REPLACE PROCEDURE SP_CourseID1(CourseName1 OUT SYS_REFCURSOR,MyCourseID IN INT) AS
BEGIN
OPEN CourseName1 FOR
SELECT
c.Course_Name AS CourseName
FROM
COURSES c
WHERE
c.Course_ID=MyCourseID;
END;
/

Mapping File Code :-
-------------------------
<sql-query name="selectAllCourseNames_SP" callable="true" >
      <return alias="c" class="com.hibernate.Course" >
        <return-property name="courseName" column="Course_Name"/>
      </return>
        { ? = call SP_CourseID1(?) }    
   </sql-query>    

Main Application Code :-
----------------------------
transaction = session.beginTransaction();
              Query query = session.getNamedQuery("selectAllCourseNames_SP");
            query.setInteger(1,2);
            List courses = query.list();    
            for (Iterator iterator = courses.iterator(); iterator.hasNext();)
            {
                Course c = (Course) iterator.next();
                System.out.println("The course Name from the Stored Procedure is : " + c.getCourseName());
            }          
            transaction.commit();

Exception that I am getting :-
-------------------------------
Exception in thread "main" java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: { ? = call SP_CourseID1(?) }
0
Comment
Question by:vgsrikanth
  • 24
  • 11
  • 2
37 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35513840
Parameter 1 cannot be set.
You need to query.setInteger(2,2)
Not setInteger(1,2)
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35513876
Your first parameter is only OUT parameter; can not be set
0
 

Author Comment

by:vgsrikanth
ID: 35513916
Hi Yan, I have changed the above code as you suggested but I am still getting this error.

Exception in thread "main" java.lang.IllegalArgumentException: Positional parameter does not exist: 2 in query: { ? = call SP_CourseID1(?) }
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 47

Expert Comment

by:for_yan
ID: 35514028
Shouldn't you have it in quotes within the braces - just a guess
0
 

Author Comment

by:vgsrikanth
ID: 35514093
Hi Yan, I have tried putting in quotes but I am still getting the same error as mentioned above.

Exception in thread "main" java.lang.IllegalArgumentException: Positional parameter does not exist: 2 in query: { "? = call SP_CourseID1(?)" }
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514192
Maybe quotes outside braces like here:

("{call fetchRec(?)}");


Maybe you don't need "call" in thios case - it is bsically a function
0
 

Author Comment

by:vgsrikanth
ID: 35514233
Hi Yan, I have tried what you have told but I am still getting the below error :

Exception in thread "main" java.lang.IllegalArgumentException: Positional parameter does not exist: 2 in query: ("{call SP_CourseID1(?)}")
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514251
Strange thing, come to my computer soon
0
 

Author Comment

by:vgsrikanth
ID: 35514364
Hi Yan, I did not understand when you meant " come to my computer soon".Should I go to any link.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514501
No, sorry,  I meant that I'd now be able to think a little bit more concentrated than when
I was on the go and using mobile. Now I'm at my computer, maybe I'll have some more suggestions to try
0
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 total points
ID: 35514545
Look, they have it in this format almost in all Named Queryes examplse I'm inding - with CDATA:

<sql-query name="findDateDifference"><![CDATA[
select (? - temp.bv_date) as days from
( select CLO_DTE as bv_date from BRAND ) temp]]>
</sql-query>
like here:
https://forum.hibernate.org/viewtopic.php?f=1&t=1002593&start=0

Did you use Named Queries before with more simple cases of normal
query not callable procedure - ?
Maybe we should try at first with simple query like this above

0
 

Author Comment

by:vgsrikanth
ID: 35514564
Hi Yan, I have tried Named Queries with simple select queries using CDATA and it is working fine.The only problem I am getting is with Stored Procedures.I am finding it difficult to execute a stored procedure by using Named Queries in the mapping file.I could not find much info on Net.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514619

This is very similar to your task, but not quite sure they resolved:

http://www.coderanch.com/t/218865/ORM/java/Named-query-stored-procedure.htm
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514751


It looks like in many cases they use it without question marks,
but with named parameters - erhpas youy want to give a try like here:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25008104.html?sfQueryTermInfo=1+10+30+hibern+name+procedur+queri+store
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35514826

This seesm to be good description - at the bottom for stored procedures:
http://docs.jboss.org/hibernate/stable/core.old/reference/en/html/querysql-namedqueries.html

They seeem to use ? only for output cursor and named parameters for the arguments.
While they don't have exact example like yours but combining exampe for siple query and for stored procedure
it seesm understanadable
0
 

Author Comment

by:vgsrikanth
ID: 35515346
Hi Yan, thank you for all the help.I have tried as you sugested in the links you have provided but I still am getting errors.

The below is the code I modified.
---------------------------------------
<sql-query name="selectAllCourseNames_SP" callable="true" >
      <return alias="c" class="com.hibernate.Course" >
        <return-property name="courseName" column="Course_Name"/>
      </return>
        { ? = call GETCOURSE(:cid) }    
   </sql-query>  

Query query = session.getNamedQuery("selectAllCourseNames_SP");
            query.setParameter("cid",new Integer(1));

Oracle Function is
----------------------

CREATE OR REPLACE FUNCTION Getcourse(MyCourseID IN INTEGER) RETURN VARCHAR IS
  CourseName VARCHAR(100);
BEGIN
  SELECT Course_Name INTO CourseName FROM COURSES WHERE Course_ID=MyCourseID;
  RETURN CourseName;
END;
/


The below is the exception I got :-
----------------------------------------
Hibernate: { ? = call GETCOURSE(?) }
org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      at org.hibernate.loader.Loader.doList(Loader.java:2231)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
      at org.hibernate.loader.Loader.list(Loader.java:2120)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
      at com.hibernate.Main.listCourse(Main.java:31)
      at com.hibernate.Main.main(Main.java:20)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:622)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:179)
      at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:782)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1027)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2887)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2978)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4102)
      at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:409)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:215)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1805)
      at org.hibernate.loader.Loader.doQuery(Loader.java:697)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.doList(Loader.java:2228)
      ... 8 more


0
 
LVL 47

Expert Comment

by:for_yan
ID: 35515393
My guess is you don't need new Integer(1), have you tried just:
   query.setParameter("cid",1);

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35515410
I may disappear for a couple of hours, then will come back
0
 

Author Comment

by:vgsrikanth
ID: 35515546
Hi Yan,I tried query.setParamter("cid",1) but still I am getting the same error.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35515670
From this stacktrace do you think we can figure out which line of code is faulting
0
 

Author Comment

by:vgsrikanth
ID: 35515834
Yan, since it is a ORA error :  java.sql.SQLException: ORA-06550:
I think the code is not able to communicate with the Oracle Function.

But the Oralce Function compiled well without errors in Oracle but I dont know why It is saying : expression is of wrong type
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516499
One obvious thing that we are lacking - this input parameter should be somehow
declared and its type should be specified in <sql-query ... element
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516509
Otherwise how would it know that we are assigning integer to it later?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516570
There is also such option in

http://book.opensourceproject.org.cn/lamp/mysql/mysqlstored/opensource/0596100892/mysqlspp-chp-14-sect-4.html

<sql-query name="getRecentEventsSP" callable="true">
     <return alias="event" class="Event">
          <return-property name="id" column="EVENT_ID" />
          <return-property name="title" column="EVENT_TITLE" />
          <return-property name="date" column="EVENT_DATE" />
     </return>
     { call getRecentEvents(?) }
</sql-query>


and

List result = session.getNamedQuery("getRecentEventsSP")
                 .setDate(0,yesterday).list(  );

so maybe you can try this way:

<sql-query name="selectAllCourseNames_SP" callable="true" >
      <return alias="c" class="com.hibernate.Course" >
        <return-property name="courseName" column="Course_Name"/>
      </return>
        { ? = call GETCOURSE(?) }    
   </sql-query>  

and then in Java code:

List result = session.getNamedQuery("selectAllCourseNames_SP")
                 .setInt(0,1).list(  );

But try

 .setInt(0,1).list(  );
or
 .setInt(1,1).list(  );
or
.setInt(2,1).list(  );








0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516603
These examples 14-32- 14-35 from
http://book.opensourceproject.org.cn/lamp/mysql/mysqlstored/opensource/0596100892/mysqlspp-chp-14-sect-4.html
look really similar to what you are doing

They don'ts show any out parameter in the procedure, but they do have output result set.
Looks a little bit strange to me, but I'd make it a try
0
 

Author Comment

by:vgsrikanth
ID: 35516697
Hi Yan, thank you so much for all the help.I tried all ways that you have asked me to but I am still getting the exception at run time.


org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      at org.hibernate.loader.Loader.doList(Loader.java:2231)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
      at org.hibernate.loader.Loader.list(Loader.java:2120)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
      at com.hibernate.Main.listCourse(Main.java:31)
      at com.hibernate.Main.main(Main.java:20)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:622)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:179)
      at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:782)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1027)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2887)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2978)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4102)
      at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:409)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:215)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1805)
      at org.hibernate.loader.Loader.doQuery(Loader.java:697)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.doList(Loader.java:2228)
      ... 8 more
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516723
You know I think that has to do with your returning of cursor - sure how cursor can be returned to the Lisrt?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516727
Cursor is two -dimensional table
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516735
And also loo at this about error PLS-00382:

You Asked

I get PLS-00382: expression is of wrong type in the following scenario:

vemail       VARCHAR2(80)    DEFAULT NULL;

for i in 1 .. email_obj.count loop
    vemail := email_obj(i);
end loop;


email_obj is an email_array type which is passed into the function where the loop is
executed.

Create TYPE EMAIL_TYPE AS OBJECT (EMAIL VARCHAR2(80));
CREATE EMAIL_ARRAY AS TABLE OF EMAIL_TYPE

What am I doing wrong?

 


and we said...

email_obj(i) is an OBJECT instance, not a string.

email_obj(i).email is a string retrieved from that given object instance.

ops$tkyte@ORA9IR2> declare
  2      vemail       VARCHAR2(80)    DEFAULT NULL;
  3      email_obj    email_array :=
                         email_array( email_type('x'), email_type('y') );
  4  begin
  5          for i in 1 .. email_obj.count loop
  6          vemail := email_obj(i).email;
  7          end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed.
 


Reviews       
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516741
Maybe you can try as they have it - in those examples which i mentioed
These examples 14-32- 14-35 from
http://book.opensourceproject.org.cn/lamp/mysql/mysqlstored/opensource/0596100892/mysqlspp-chp-14-sect-4.html

Chek - they don't retrtn cursor
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35516749
And if you want to return cursor - you sghould probably map it to ResultSet
but it cvannot be List
0
 

Author Comment

by:vgsrikanth
ID: 35516839
Hi Yan, thank you for all the input.What you said is true, we should traverse the ResultSet not the List.
I know it is asking too much but can you just modify the below code into a executable code as I am unable to modify to get the ResultSet.Thank you very much.

Oracle Function :-
------------------------
CREATE OR REPLACE FUNCTION Getcourse(MyCourseID IN INTEGER) RETURN VARCHAR IS CourseName VARCHAR(255);
BEGIN
  SELECT Course_Name INTO CourseName FROM COURSES WHERE Course_ID=MyCourseID;
  RETURN CourseName;
END;

Mapping File :-
-------------------
<sql-query name="selectAllCourseNames_SP" callable="true" >
      <return alias="c" class="com.hibernate.Course" >
        <return-property name="courseName" column="Course_Name"/>
      </return>
        { ? = call GETCOURSE(?) }    
   </sql-query>  


Main Application Code :-
-----------------------------
Query query = session.getNamedQuery("selectAllCourseNames_SP");
            query.setInteger(0,1);

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35517159


have you tried to do the way you had it before
but using method getResultList(), like here:

   @PersistenceContext
    public EntityManager em;
    ...
    customers = em.createNamedQuery("findAllCustomersWithName")
            .setParameter("custName", "Smith")
            .getResultList();
in this example
http://download.oracle.com/javaee/5/api/javax/persistence/NamedQuery.html
 
I guess List can be equivalent to ResultSet if your fileds are mapped to the
fileds of the class


 I think you have the sitaution absolutlrtly analogous
to exapmples  14-32- 14-35 from
http://book.opensourceproject.org.cn/lamp/mysql/mysqlstored/opensource/0596100892/mysqlspp-chp-14-sect-4.html
I suggest just to folow that example.
Notice - they don't have {?=call...}


They jsut have {call...}
Also you see they return in fact the table but capture it into the List - that
becuse the have defined class Event and it inludes three fields
which they retrieve, so indeed their list of events maps
to the ResultSet which they retirive

You probably aslo have class corresponding to courses

I'd try to follow that example most closely
0
 

Author Comment

by:vgsrikanth
ID: 35517266
Thanks Yan, I shall try what you have told today and If you dont mind can you check this thread tomorrow so that I  can ask you if I have any questions.
0
 
LVL 92

Expert Comment

by:objects
ID: 35517299
Problem appears to be with your stored procedure.
The first parameter must be an OUT parameter that returns a result set. you can use a ref cursor to achieve that
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35517327
Sure, of course I will chek
0
 
LVL 92

Expert Comment

by:objects
ID: 35790266
you *can* use stored procedures
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question