Calling Stored Procedures as Named Query in Hibernate

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(?) }
vgsrikanthAsked:
Who is Participating?
 
for_yanCommented:
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
 
for_yanCommented:
Parameter 1 cannot be set.
You need to query.setInteger(2,2)
Not setInteger(1,2)
0
 
for_yanCommented:
Your first parameter is only OUT parameter; can not be set
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
Shouldn't you have it in quotes within the braces - just a guess
0
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
Maybe quotes outside braces like here:

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


Maybe you don't need "call" in thios case - it is bsically a function
0
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
Strange thing, come to my computer soon
0
 
vgsrikanthAuthor Commented:
Hi Yan, I did not understand when you meant " come to my computer soon".Should I go to any link.
0
 
for_yanCommented:
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
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:

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
 
for_yanCommented:


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
 
for_yanCommented:

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
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
My guess is you don't need new Integer(1), have you tried just:
   query.setParameter("cid",1);

0
 
for_yanCommented:
I may disappear for a couple of hours, then will come back
0
 
vgsrikanthAuthor Commented:
Hi Yan,I tried query.setParamter("cid",1) but still I am getting the same error.
0
 
for_yanCommented:
From this stacktrace do you think we can figure out which line of code is faulting
0
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
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
 
for_yanCommented:
Otherwise how would it know that we are assigning integer to it later?
0
 
for_yanCommented:
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
 
for_yanCommented:
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
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:
You know I think that has to do with your returning of cursor - sure how cursor can be returned to the Lisrt?
0
 
for_yanCommented:
Cursor is two -dimensional table
0
 
for_yanCommented:
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
 
for_yanCommented:
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
 
for_yanCommented:
And if you want to return cursor - you sghould probably map it to ResultSet
but it cvannot be List
0
 
vgsrikanthAuthor Commented:
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
 
for_yanCommented:


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
 
vgsrikanthAuthor Commented:
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
 
objectsCommented:
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
 
for_yanCommented:
Sure, of course I will chek
0
 
objectsCommented:
you *can* use stored procedures
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.