Running Oracle SQL Stored Procedure in HQL

Question

I am attempting to run the stored procedure 'do_build' from Hibernate, and have written the call in this way:

this.entityManager.createQuery("execute do_build", Boolean.class)

but am getting the following exceptions

01 Oct 2013 15:15:00,058 [ERROR] (schedulerFactoryBean_Worker-1) org.hibernate.hql.PARSER: line 1:1: unexpected token: execute

and

java.lang.IllegalArgumentException: node to traverse cannot be null!
at org.hibernate.hql.ast.util.NodeTraverser.traverseDepthFirst(NodeTraverser.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:280)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:98)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1760)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:277)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
at com.sun.proxy.$Proxy37.createQuery(Unknown Source)

I just wanted to confirm before making the change - should I simply be querying with 'call do_build' instead, or is there something else potentially wrong here?

Answer 1

I am answering this question in hopes that it will help you directly but also if not, that others who have had this dragon to joust with will come across the answer here on SO. This is a real doozie in its own way and you'd think there'd be a very clear discussion of exactly how to handle this sort of thing in every major Hibernate info deck, but unfortunately, there are only scattered bits here and there and hints along the way. After struggling for a couple days to piece together the fix, I found what worked for me.

First, I have to use .hbm.xml files and mappings, not annotations. This is due to the lack of declared PKs on the tables in use, and making PKs for them (surrogate keys, in essence), is nixed (this is out of my control). Annotations only seem "happy" if there are declared PKs on the tables. If not, you have to use an identity class for each table class. (E.g.: You have a table: "MyTable". If you use the Hib. Reverse Engineering Tools in Eclipse, for example, it will generate two source files for you: MyTable.java and MyTableId.java (and abstract classes, too, if you ask for them). MyTableId.java is the one that will hold the actual value-related stuff.)

I am working with Hibernate 3.3. We're not up to 4.x just yet.

Finally, the database in use is Oracle. My challenge was to run an SP that took one parameter and returned no records. It is a system SP, used to bind an arbitrary string value to the current connection session ('USERENV') associated with the key value 'CLIENT_IDENTIFIER'. This is supposed to allow the developer to assign to the session an application user's identity string (a logged-in userid, for example), which can then be extracted in a trigger or SP on the database side. That extraction is the easy part; it's getting Hibernate to let you run this SP that is the hard part.

It used to be you could grab the base Oracle connection and run the call to the SP over it without much fanfare. That looked like this:

String userid = "<something from someplace>";
Session session = getSession(); // whatever way you get your Hib. session.
/* 'WSCallHelper' as used below is a helper class found in the IBM Websphere API
   programmer library.  In some other context, a programmer would use a different
   means to isolate the Oracle-native connection. */
OracleConnection oracleconnection = 
    ( OracleConnection ) WSCallHelper.getNativeConnection( session.connection() );
CallableStatement st =  oracleconnection.
    prepareCall( "{call DBMS_SESSION.SET_IDENTIFIER(:userid)}");
st.setString( "userid", userid );
st.execute();

Problem now: session.connection() is deprecated in 3.3, and in the most recent 4.x, you will not find it at all in the Hibernate "Session" class Javadocs.

This means that you have to, if you plan to upgrade (??) your Hibernate version some time to 4.x and have this kind of code lying around, it'll stop working. If you plan to write something new and don't know if you will be upgrading or not-- better safe than sorry. (You don't want the 3:00 AM call, do you? Me neither.)

First thing I ran into when looking for a way to run an SP on Oracle using either a native SQL Query object (SQLQuery) or an HQL query (a Query object) is that both have the problem of supporting only a select action or an update action: .list() and .executeUpdate(). There is no plain and simple .execute() like you find in other DALs or in java.sql. The SP I wanted to run [ DBMS_SESSION.SET_IDENTIFIER(userid) ] returns nothing. In addition, all efforts I made to just handing the Hibernate session the string {CALL DBMS_SESSION.SET_IDENTIFIER(userid)} failed. I tried futzing with the statement syntax, etc. No dice.

Finally after a lot of surfing around, I realized that if Hibernate expects there to be something returned (any kind of value) from an SP, it has to be treated as a database entity of some kind. This is in keeping with trying to run even a simple, unqualified select statement using Hibernate; if you have no annotated Java files mapping data to tables or .hbm.xml files doing likewise, Hibernate simply won't cooperate-- and that's the idea. So I had to come up with a way to represent the SP's relationship to the database, even though there is no table that maps to it. The dragon needed to be tricked.

Step 1: Create an .hbm.xml file for Dual (yes, the pseudo-table in Oracle), but only if you are not using annotations. It should look a lot like this, modified for your package structure, query name, and actual SP you want to run, as desired/needed:

<?xml version="1.0" encoding="utf-8"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.company.hibernate.dataaccess.model.Dual" table="DUAL">
        <id name="dummy" type="java.lang.String">
            <column name="DUMMY" />
            <generator class="identity" />
        </id>
    </class>

    <sql-query name="callDdbmsSessionSetIdentifier">
 <return alias="dummy" class="com.company.hibernate.dataaccess.model.Dual"/>
 <![CDATA[CALL DBMS_SESSION.SET_IDENTIFIER(:userid)]]>
    </sql-query>

</hibernate-mapping>

Please note: The call to DBMS_SESSION.SET_IDENTIFIER is in a CDATA block, as ought to be any other CALL like this. (I have Mkyong to thank for this hint: http://www.mkyong.com/hibernate/hibernate-named-query-examples/ ) Also note the <class> stuff is not optional. This solution will not work without it. (If you do a "select * from DUAL" while logged into Oracle, you will get one column back named "DUMMY" and one row back with a single field holding a value of "X". So you need to declare the "DUMMY" field as shown. And, don't worry about the <generator class="identity" /> reference, as you are never going to be saving anything to DUAL.)

If you are using annotations, do the annotion equivalent of the above in your "Dual.java" file, which is what you'll need no matter if you use annotations or .hbm.xml mappings. This file is discussed next. Adding the correct reference to your hibernate.cfg.xml file will be the last step.

Step 2: Create a "Dual.java" file As shown above, for example purposes, we assume a package of com.company.hibernate.dataaccess.model:

package com.company.hibernate.dataaccess.model;

public class Dual implements java.io.Serializable {
    private String dummy = "";

    public void setDummy (String s) {
        dummy = s;
    }

    public String getDummy () {
        return dummy;
    }

}

That's it! Now if you are using annotations, you'll have to add those to suit.

---

Update 12/10/13: Went over to Hib 3.6.3 and now can use annotations OK for this, so we ditched the .hbm files. The Dual.java file now in use looks like this:

 package {whatever};

 import javax.persistence.*; // Better to name each entity, but using * for brevity
 import org.hibernate.annotations.NamedNativeQueries;
 import org.hibernate.annotations.NamedNativeQuery;

 @NamedNativeQueries({
    @NamedNativeQuery(
    name = "callDdbmsSessionSetIdentifier",
    query = "CALL DBMS_SESSION.SET_IDENTIFIER(:userid)",
    resultClass = Dual.class
    )
 })
 @Entity
 @Table( name = "DUAL", schema = "SYS" )
 public class Dual implements java.io.Serializable {

 private DualId id;

 public Dual() {
 }

 public Dual( DualId id ) {
    this.id = id;
 }

 @EmbeddedId
 @AttributeOverrides( {
  @AttributeOverride( name = "dummy", column = @Column( name = "DUMMY", nullable = false, length = 1 ) ),
   } )

 public DualId getId() {
    return this.id;
 }

 public void setId( DualId id ) {
    this.id = id;
 }
}

In case you care, DualId.java looks like this:

 package {whatever};
 import javax.persistence.Column;
 import javax.persistence.Embeddable;

 @Embeddable
 public class DualId implements java.io.Serializable {

 private String dummy;

 public DualId() {
 }

 public DualId( String dummy ) {
    this.dummy = dummy;
 }

 // Bean compliance only; 'DUMMY' can't be changed in DUAL and 
 // why you'd care to get it when you know already it's just an "X",
 // dunno.  But these get.. and set.. methods are needed anyway.
 @Column( name = "DUMMY", nullable = false, length = 1 )
 public String getDummy() {
    return this.dummy;
 }

 public void setDummy( String dummy ) {
 }

 public boolean equals( Object other ) {
    if ( ( this == other ) )
        return true;
    if ( ( other == null ) )
        return false;
    if ( !( other instanceof DualId ) )
        return false;
    DualId castOther = ( DualId ) other;

    return ((this.getDummy() == castOther.getDummy() ) || ( this.getDummy() != null && castOther.getDummy() != null && this
            .getDummy().equals( castOther.getDummy())));
 }

 public int hashCode() {
    int result = 17;
    result = 37 * result + ( getDummy() == null ? 0 : this.getDummy().hashCode() );
    return result;
 }

}

---

Step 3: Update your hibernate.cfg.xml file

Add this line to your list of mapping resource entries, if using .hbm.xml files, adjusting paths or package refs to suit:

<mapping resource="com/company/hibernate/hbm/Dual.hbm.xml" />

If using annotations, add this line:

<mapping class="com.company.hibernate.dataaccess.model.Dual" />

Almost done! Everything saved? Great.

In whatever source file you have that you want to call the SP from, the code would, at least in my case, look like this:

Session session = getSession(); // somehow...
String userid = "<got this someplace>";
Query query = session.getNamedQuery( "callDdbmsSessionSetIdentifier" ).
                setParameter( "userid", userid );
try {
  query.list();
} catch (Exception e) { }

OK, what's going on? Note the named query is 'callDdbmsSessionSetIdentifier'. This is what I used to label the actual query defined in the .hbm.xml file (see above; look at the <sql-query name="callDdbmsSessionSetIdentifier"> element). Now, note I am catching the exception thrown by calling query.list() and consuming it. Normally, this'd be a huge no-no, right? Well, you can report it if you want to. You can report it by just logging its message and not the entire trace if you want to keep your logs from filling up with loads of junk messages. The exception you will get will be stuff like:

(date-time) - JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions Cannot perform fetch on a PLSQL statement: next ... (date-time) - SystemErr R org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82) ... Caused by: java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:240) ...

Note the common theme: Hibernate can't get a record back of any kind. This is not the kind of exception you need to worry about if all you are doing is executing an SP and do not want to retrieve records from it.

As always, test and test some more... make sure it is indeed doing what you want it to. Whether you want to do any error logging of any kind or just consume the errors is up to you.

FINALLY, what would you do if, like me, you wanted to use the DBMS_SESSION.SET_IDENTIFIER() stored procedure in Oracle so you could then get some logged-in user's user ID in a database-side trigger or SP? Here's the database-side PL/SQL for it, simple enough:

USERNAME VARCHAR2(50) := NULL;
...
select SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')
    INTO USERNAME
from DUAL;
...

In my particular case, I use this in a trigger that serves to record any changes to a certain table for auditing purposes. But changes could come from anywhere: a desktop user of SQL*Plus, an application that may or may not use the DBMS_SESSION.SET_IDENTIFIER() procedure, etc., and thus there may not be a value set for CLIENT_IDENTIFIER in USERENV for any given session connection. If that is the case, USERNAME would come back as null. So I have this extra block right after the one above that gets the connection ID in case the CLIENT_IDENTIFIER is null:

IF USERNAME IS NULL THEN
   SELECT USER INTO USERNAME FROM DUAL;
END IF;

so I have something to put in the ID field in the audit table.

Done. I hope this is of help to someone out there. Feel free to comment.

Answer 2

Query query = session.createSQLQuery(
    "CALL procedureName(:parameter)")
    .addEntity(ClassName.class)
    .setParameter("parameter", "parameterValue");

Answer 3

Use the call keyword. Hibernate does not understand execute.

Query query = session.createSQLQuery( "CALL do_build()") .addEntity(Boolean.class);

I am not sure about the addEntity in conjunction with boolean class since I use to return a Entity for this. But basically it is about createSQLQuery and use CALL. Check out if this helps. If not try Execute in conjunction with createSQLQuery.