Friday, October 31, 2008

Spring, Hibernate and Oracle Stored Procedures

Motivation: While there are a few resources available online for calling stored procedures from Hibernate, it took me a while to stumble across one that mostly captures what I need. The intention of this blog entry is to put a similar example into my own words, to extend it slightly and hopefully to help anyone not experienced with Hibernate and Oracle to integrate Stored Procedures and Functions into an application quickly.

Setup Oracle 10g


For this example, we will be using Oracle 10g. We can initialize our schema user with SQLPlus with the following commands:
  • sqlplus connect as sysdba
  • create user my_orcl identified by my_orcl;
  • grant create session to my_orcl;
  • grant resource to my_orcl;
  • grant create table to my_orcl;


Setup a Project For Spring and Hibernate


We will download spring-framework-2.5.5-with-dependencies.zip, hibernate-distribution-3.3.1.GA-dist.zip and hibernate-annotations-3.4.0.GA.zip. We can create a standard project layout of src, test and lib folders with the following jars on the classpath:

  • spring-framework-2.5.5/dist/spring.jar
  • spring-framework-2.5.5/dist/modules/spring-test.jar
  • spring-framework-2.5.5/lib/jakarta-commons/commons-logging.jar
  • spring-framework-2.5.5/lib/jakarta-commons/commons-dbcp.jar
  • spring-framework-2.5.5/lib/jakarta-commons/commons-pool.jar
  • spring-framework-2.5.5/lib/jakarta-commons/commons-collections.jar
  • spring-framework-2.5.5/lib/dom4j/dom4j-1.6.1.jar
  • spring-framework-2.5.5/lib/log4j/log4j-1.2.15.jar
  • spring-framework-2.5.5/lib/slf4j/slf4j-api-1.5.0.jar
  • spring-framework-2.5.5/lib/slf4j/slf4j-log4j12-1.5.0.jar
  • spring-framework-2.5.5/lib/j2ee/*.jar
  • hibernate-annotations-3.4.0.GA/hibernate-annotations.jar
  • hibernate-annotations-3.4.0.GA/lib/hibernate-commons-annotations.jar
  • hibernate-distribution-3.3.1.GA/hibernate3.jar
  • hibernate-distribution-3.3.1.GA/lib/required/javassist-3.4.GA.jar
  • hibernate-distribution-3.3.1.GA/lib/required/slf4j-api-1.5.2.jar


Because we will be using Oracle Stored Procedures, we will also need a database driver such as

  • oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar


Create Domain Objects


We can setup our domain using annotated Java. For these examples, we need one simple domain Object.


package spring.hibernate.oracle.stored.procedures.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {

private static final long serialVersionUID = 8676058601610931698L;
private int id;
private String firstName;
private String lastName;

@Id
@Column(name = "ID", nullable = false)
public int getId() {
return this.id;
}

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

@Column(name = "FIRST_NAME", nullable = false, length = 50)
public String getFirstName() {
return this.firstName;
}

public void setFirstName(final String firstName) {
this.firstName = firstName;
}

@Column(name = "LAST_NAME", nullable = false, length = 50)
public String getLastName() {
return this.lastName;
}

public void setLastName(final String lastName) {
this.lastName = lastName;
}
}



Create a DAO


Now that we have a domain Object, we can create a DAO for a simple operation, such as looking up Authors by last name. Fortunately, Spring provides a convenient base class for DAO operations.


package spring.hibernate.oracle.stored.procedures.dao;

import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import spring.hibernate.oracle.stored.procedures.domain.Author;

public class AuthorDAO extends HibernateDaoSupport {

@SuppressWarnings("unchecked")
public List<Author> findByLastNameUsingHQL(final String lastName) {
return getHibernateTemplate().find("from Author author where author.lastName = ?", lastName);
}
}



The Spring Application Context Configuration


The Spring applicationContext.xml can reside directly at the root of our src classpath, and it will contain information for configuring Spring to manage our Hibernate sessions, transactions and datasources, as well as our DAO.


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"
default-autowire="constructor">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />
<property name="username" value="my_orcl" />
<property name="password" value="my_orcl" />
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:/hibernate.cfg.xml" />
<property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
<prop key="hibernate.hbm2ddl.auto">create</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<bean name="authorDAO"
class="spring.hibernate.oracle.stored.procedures.dao.AuthorDAO">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
</beans>



The Hibernate Configuration


The hibernate.cfg.xml can also reside directly in our src/ folder. The primary purpose of this file is to let Hibernate know about our domain class.


<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<mapping class="spring.hibernate.oracle.stored.procedures.domain.Author" />
</session-factory>
</hibernate-configuration>



Testing the Setup


Now that our project is setup, we can write a simple test to very that all of our configuration files can be properly loaded and that all of our connections work. We will begin by setting up some simple test data in the database, and then we can call our DAO method to find Authors by their last names. Again, we can take advantage of a convenient Spring base class for our test cases.


package spring.hibernate.oracle.stored.procedures.dao;

import org.junit.Test;
import org.springframework.test.AbstractTransactionalDataSourceSpringContextTests;

public class AuthorDAOTest extends AbstractTransactionalDataSourceSpringContextTests {

private AuthorDAO authorDAO;

@Override
protected void onSetUp() throws Exception {
super.onSetUp();
createAuthor(1, "Jules", "Verne");
createAuthor(2, "Charles", "Dickens");
createAuthor(3, "Emily", "Dickinson");
createAuthor(4, "Henry", "James");
createAuthor(5, "William", "James");
createAuthor(6, "Henry", "Thoreau");
}

@Test
public void testFindByLastNameUsingHQL() throws Exception {
assertEquals(2, getAuthorDAO().findByLastNameUsingHQL("James").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Verne").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Dickinson").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Dickens").size());
assertEquals(0, getAuthorDAO().findByLastNameUsingHQL("Whitman").size());
}

@Override
protected String[] getConfigLocations() {
return new String[] { "applicationContext.xml" };
}

public AuthorDAO getAuthorDAO() {
return authorDAO;
}

public void setAuthorDAO(final AuthorDAO authorDAO) {
this.authorDAO = authorDAO;
}

private void createAuthor(final int id, final String firstName, final String lastName) {
jdbcTemplate.execute(String.format("insert into author (id, first_name, last_name) values (%d, '%s', '%s')", id,
firstName, lastName));
}
}



Write a Stored Procedure


Because we have specified <prop key="hibernate.hbm2ddl.auto">create</prop> in our Spring configuration for Hibernate, the AUTHOR table now exists in the database. We can write a simple stored procedure to query this table.


CREATE OR REPLACE PROCEDURE findByLastName
( res OUT SYS_REFCURSOR,
vLastName IN author.last_name%type ) AS
BEGIN
OPEN res FOR
SELECT * FROM author WHERE last_name = vLastName;
END findByLastName;



Call The Stored Procedure From Hibernate


Now that we have a PL/SQL Stored Procedure, we will need a way to reference it from Hibernate. We can annotate the domain Object with such a named query.


....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {
....



Now we can add a method to our AuthorDAO for calling this Stored Procedure.


....
@SuppressWarnings("unchecked")
public List<Author> findByLastNameUsingStoredProcedure(final String lastName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByLastName") //
.setParameter("vLastName", lastName) //
.list();
}
});
}
....



Finally, we will add a test case for calling the new DAO method.


....
@Test
public void testFindByLastNameUsingStoredProcedure() throws Exception {
assertEquals(2, getAuthorDAO().findByLastNameUsingStoredProcedure("James").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Verne").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Dickinson").size());
assertEquals(1, getAuthorDAO().findByLastNameUsingStoredProcedure("Dickens").size());
assertEquals(0, getAuthorDAO().findByLastNameUsingStoredProcedure("Whitman").size());
}
....



Write a PL/SQL Function


We can similarly call an Oracle Function. Here, we will use a function that locates Authors by their first names.


CREATE OR REPLACE FUNCTION findByFirstName
( vFirstName IN author.first_name%type )
RETURN SYS_REFCURSOR AS
res SYS_REFCURSOR;
BEGIN
OPEN res FOR
SELECT * FROM author WHERE first_name = vFirstName;
RETURN res;
END findByFirstName;



Call The Function From Hibernate


We can reference this function using an org.hibernate.annotations.NamedNativeQuery, but we can also make the scenario a little more interesting by instead using the javax.persistence.NamedNativeQuery annotation in conjunction with the javax.persistence.QueryHint annotation. By using this annotation, note how we have two named queries declared on a single domain Object. Also note the braces that are necessary for the query syntax of the function but are not necessary for the stored procedure call.


....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@javax.persistence.NamedNativeQuery(name = "findByFirstName", query = "{ ? = call findByFirstName(:vFirstName) }", resultClass = Author.class, hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {
....



Again, we will access this PL/SQL function through our DAO.


....
@SuppressWarnings("unchecked")
public List<Author> findByFirstNameUsingFunction(final String firstName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByFirstName") //
.setParameter("vFirstName", firstName) //
.list();
}
});
}
....



And finally, we can add another simple test for this function call.


....
@Test
public void testFindByFirstNameUsingFunction() throws Exception {
assertEquals(0, getAuthorDAO().findByFirstNameUsingFunction("James").size());
assertEquals(2, getAuthorDAO().findByFirstNameUsingFunction("Henry").size());
}
....



Hopefully, this step-by-step process gives a good starting point for creating more complex stored procedure and function calls using Spring, Hibernate and Oracle.

Next: Hibernate updates and stored procedures ->

43 comments:

Unknown said...

Be carefull,

As hiberbnate newbie, i just try the hibernate configuration from the example, and in fact i drope and recreate my table
dont set the property
prop key="hibernate.hbm2ddl.auto

if you dont want to create the table from hibernate

Tim Myer said...

Hi Chaussette,

Thanks for the comment and for pointing out that particular flag. For this example, the "create" flag was explicitly used because I wanted the table to be created and persisted automatically by Hibernate from a POJO (and not dropped, as would happen in the "create-drop" case). My point was to call out specifically that Hibernate would be the creator of this particular table. Also, for the purpose of clearing the data in the table for the testing examples, the "create" flag was appropriate for this example.

For more information about the other possible settings for this flag, you might want to look
here.

Thanks again.

-----Tim------

Hari Mallepally said...

HI,

I am finding problem in making the stored procedure calls using hibernate annotations.

I have configured

@ javax.persistence.NamedQuery(name = "test1", query = "from Jackpot" )


I have configured all my hibernate annotated beans.




Annotations are working fine for normal database operations, but when it comes to accessing the NamedQueries i mentioned in one of my hibernate bean are not working.

I am always getting 'org.springframework.orm.hibernate3.HibernateSystemException: Named query not known:' exception.

Do we need to do any specific configuration to enable NamedQueries or is there any alternative to invoke stored procedure with hibernate while annotations are used for my beans.

Tim Myer said...

Hi Harinath,
Thank you for posting.
Is the bean annotated with the @NamedQuery mapped in your hibernate.cfg.xml?
As the examples are intended for use with @NamedNativeQueries, I cannot be sure of exactly what specifically is happening in your application. The @NamedQuery you indicated appears to use HQL, and does not invoke a stored procedure.
If I have answered your question, please let me know. If not, could you please re-state it.
Thanks.
-----Tim-----

tactusoft said...

Hi Tim great article. My name is Carlos Arturo and I'm from Colombia. I have a question: why the parameter of the cursor if it is not work for me in the first position.

Tim Myer said...

Hi Carlos,

Thank you for the feedback. I am not quite sure I understand the question. If you are asking why the first parameter of the stored procedure must be the ref cursor, I do not know the specifics, only that it is a Hibernate limitation. Perhaps this documentation will provide more insight: http://www.hibernate.org/hib_docs/reference/en/html/querysql-namedqueries.html#sp_query

I hope that helps. Thanks.

---Tim---

ProggerPete said...

Hi Tim,
I've been meaning to work throw the example you've got there for a while and have finally got around to it.

I've managed to get everything working except the oracle function example.

I'm getting

13:23:10,234 DEBUG JDBCExceptionReporter:92 - could not execute query [{ ? = call findByFirstName(?) }]
java.sql.SQLException: ORA-00900: invalid SQL statement

The function is in the db and runs without error. I can call it with

select findByFirstName('James') from dual;

and it doesn't throw an error, so it looks like it's in the mapping somewhere.

Any ideas?

Tim Myer said...

Hi Pete,

Thanks for the comment. From the exception that you posted, I am wondering if the JDBC driver is trying to execute the named query as a callable statement or as straight SQL. Could you also perhaps post the annotated header from your Author class?
Thanks.

---Tim---

ProggerPete said...

Hi Tim,
Here's what I've got.

@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@javax.persistence.NamedNativeQuery(name = "findByFirstName", query = "{ ? = call findByFirstName(:vFirstName) }", resultClass = Author.class, hints = {
@QueryHint(name = "org.hibernate.callable", value = "true")})
@SQLUpdate(sql = "call update_author(:vFirstName, :vLastName, :vId)")
@SQLDelete(sql = "call delete_author(:vId)")
//@SQLDeleteAll(sql = "call delete_all_author()")
@SQLInsert(sql = "{call insert_author(:vFirstName, :vLastName, :vId)}", callable = true)
@Table(name = "AUTHOR", schema = "PETE_ORCL")
public class Author {

Tim Myer said...

Hi Pete,

Very interesting. I used your header and do not seem to be having any trouble. Are you using the ojdbc14 driver, and if so, could you check the Manifest and let me know which version you are using?

Also, what does your DAO method for calling the function look like? Is it calling session#getNamedQuery()#setParameter(...)#list()?

Thanks.
---Tim---

ProggerPete said...

Hi, hope you had a great Easter!

I'm using ojdbc14.jar (version 10.2.0.1.0 I think)

My calling method is a direct copy of yours.

@SuppressWarnings("unchecked")
public List<Author> findByFirstNameUsingFunction(final String firstName) {
return (List<Author>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException, SQLException {
return session.getNamedQuery("findByFirstName") //
.setParameter("vFirstName", firstName) //
.list();
}
});
}

Kay said...

This is a great post. Thanks for this example.
I am using a similar thing in my web service.Using Oracle, Spring and Hibernate.I am getting the ref cursor back using the stored proc in an array list object.
When I try to use the arraylist it just shows me first object and rest come out as blank.
So my ques is how do I move through the cursor/array list and when done close the cursor.

Tim Myer said...

Hi Kay,

Thanks very much for the feedback. Would you happen to have a code snippet for the query that you are running and the way that you are getting the list back from the query?

Thanks.
---Tim---

Kay said...

i think i resolved that issue myself. I have another problem. My sp has a simple query

OPEN getbatch_record_cursor FOR
SELECT PYMT_BATCH_ID,OPRID,BATCH_NUM,BATCH_TYP,CHANNL,CORP,IN_USE,
IS_BATCH_FULL_CLSD,IS_OPR_LCKD,
TOT_AMT_PROCESSED,TOT_TXN_PROCESSED,
LST_USD_TIMESTAMP,DATE_CREATED
FROM PYMT_BATCH WHERE CORP=P_CORPID
AND CHANNL=P_CHANNEL
AND BATCH_TYP=P_BATCHTYPE FOR UPDATE NOWAIT;

P_RECORDS_RESULT := getbatch_record_cursor;

and this is the ref cursor being returned.

My code to invoke this sp is
lPymtBatch=(ArrayList) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException
{
Query q = session.getNamedQuery("PROC_PYMT_BATCHES_SP");
//TO DO add arguments
q.setString("corpId", scorp);
q.setString("channel", schannel);
q.setBigDecimal("amt", samt);
q.setString("batchType", sbatchType);
q.setInteger("pmtProcessCode", pmtProcessCode);
q.setString("pReturnAll", pReturnAll);
log.debug(q);
return q.list();
}
});

}

If I do not use FOR UPDATE stmt in cursor, I get the results fine.But if I use for update and then I loop through the array list, I get the following error

01 May 2009 16:42:10,765 [] [] [] [bb7199f0-0500-568b-5ee4-ae324b7dd416] [] DEBUG [main] com.comcast.billing.payment.pmt.dao.hibernate.PymtBatchHibernateDao
Hibernate: {call pmtowner.PROC_PYMT_BATCHESKtest(? , ? ,?,?,?,?,?) }
org.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
org.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence

I do need to update the rows I am getting.What is the best way to do this.

Tim Myer said...

Hi Kay,

I am guessing that you are iterating through the returned List<IPymtBatch> and you are setting some values on each IPymtBatch object in Java. Is that correct?
If that is the case, I am not sure why you would not be able to eliminate the "FOR UPDATE" in your procedure, modify an IPymtBatch and then call hibernateTemplate.saveOrUpdate(iPymtBatch) on each modified IPymtBatch object.
My suggestion would be to make sure that whatever methods you are using to call the stored procedure and do the updates are annotated with @Transactional so that the entire event happens within a transactional context. Just make sure that a transaction manager is declared in your Spring application context (one line in the file, described in the post). I would think that using a transactional context for both the execution of the stored procedure and for the updates should eliminate the need for the "FOR UPDATE" declaration in your query.
Hope that helps.

---Tim---

Kay said...

I tried that and the update was not getting reflected in the database. My guess is its because of transaction not being committed. Let me check that.

Thanks for your help tho'. I really appreciate it.

Kay said...

That worked! Now I have another issue. I need something like an error code or status code along with a result set. Since hibernate allows only one out parameter and that has to be a refcursor, i am adding another field. so if my origina table has columns a and b , i m just retugnig the refcursor as select errcode, a , b form table. Now I am not sure how to handle this. Do, I have to create a new pojo, hbm and everyhting. or in the same hbm I can add sp call. I tried adding in the same hbm but I am getting some issues. Do u have any example for this?

Tim Myer said...

Hi Kay,
Great that it worked.
As for mapping a Pojo onto a View or onto a selection of individual columns or onto some other structure that does not physically exist as a table in a database, I have done that successfully but have not posted an example anywhere. I simply created an annotated Pojo, as you suggested, registered it with the hbm config, and in the Java code used the Hibernate session to create a SQLQuery where the Pojo is specified as the output for the refcursor by a call to SQLQuery#.addEntity(MyPojo.class).
I hope that was clear enough. This seems to be a good case for a blog entry, but I won't be able to post anything for a couple of days.
Let me know if this worked.
Thanks.
---Tim---

Kay said...

Could you email me the example that you have?

Tim Myer said...
This comment has been removed by the author.
Tim Myer said...

For an example of how to map a hibernate entity to something other than a table, please see this post:

http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html

---Tim---

aberchane said...

this tuto isn't correct!!!!!!!!!!!!!!!!
how we can give de second parametre to our procedure
there is a probleme with number and type of parametres

Tim Myer said...

Hi Seam,
Thank you for the feedback.
Could you please be more specific about the issue that you are seeing and perhaps provide an example?
---Tim---

Balqis Umairs said...

hi...

thanks for sharing the info...
really help me lot.
but i think you miss i lib...
JUnit Lib...

Tim Myer said...

Hi Puteri,

Thanks very much for the feedback. You are quite right about the junit library omission. Of course, now, I would probably recommend using a maven2 setup rather than downloading and copying the jars directly. In that case, adding the junit dependency would just be a matter of adding a few lines to the pom.

---Tim---

Unknown said...

Hi I am new to hibernate... I just tried one example.

Mapping File :





{ call clr0051_merch_profit_summary(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }


{? = call APP_SECURITY.WS$$HIERARCHY.GETWHERECLAUSEFORUSER(?, ?, ?, ?, ?) }


Java Class

package de.laliluna.example;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.JDBCException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import de.laliluna.hibernate.SessionFactoryUtil;

public class TestExample {

final static Logger logger = LoggerFactory.getLogger(TestExample.class);

/**
* @param args
*/
public static void main(String[] args) {

Transaction tx = null;
Session session = SessionFactoryUtil.getInstance().openSession();
try {
tx = session.beginTransaction();
List merList = new ArrayList();
Query query = session.getNamedQuery("excelGetter");
query.setLong(0, 336790);
query.setLong(1, 845);
query.setString(2, "NORTH");
query.setString(3, "BUSINESS");
query.setString(4, null);
merList = query.list();
System.out.println("Size" + merList.size());
tx.commit();
} catch (JDBCException e) {
System.out.println(e.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
session.close();
}

}
}


When I run it I am getting :

org.hibernate.exception.SQLGrammarException: could not execute query


ORA-06550: line 1, column 13:
PLS-00201: identifier 'APP_SECURITY.WS$$HIERARCHY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can You please help me finding where I am wrong ?

Unknown said...

hi, sorry to bother you, but i always get a
org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value

what exactly is wrong with it ?

Harishankar Naganathan said...

Hi Tim,
I have one doubt in calling a oracle stored procedure which is returning more than one out_parameter(sys_refcursor), I would like to know how we will use it in hibernate for calling this procedure or how can we do it in jav with the help of hibernate.

Tim Myer said...

Hi Harishankar,

Thank you for the feedback and for the question. I am not entirely clear on what you are looking to do. If you would like to have Hibernate call a stored procedure that uses more than one OUT parameter, then I believe this is not supported, but I might be incorrect. Perhaps the information you seek is here:
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query

I hope that helps!
---Tim---

NoMansLand said...

"hi, sorry to bother you, but i always get a
org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value

what exactly is wrong with it ?"

I mhaving the same problem, what exactly is wrong with it ?

Tim Myer said...

Hi NoMansLand,
Thanks for the question.
Have you taken a look at this thread:

http://forums.oracle.com/forums/thread.jspa?threadID=698746&tstart=-2

Or at this Hibernate issue:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-3159

Which versions of the Oracle driver and Hibernate are you using, and are you referencing a deprecated type?

I hope that helps,
---Tim---

kwame said...

Very nice tuto. Thank you. But if I had to use spring jdbc instead of hibernate, please can you tell me how I can do it?
Best regards,
Kwame

Tim Myer said...

Hi Kwame,

That is an interesting question and I imagine the answer would be a bit more straightforward than using Hibernate. This resource should provide you a good starting point to using the Spring JDBC Template: http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html

I hope that helps!
---Tim---

joanoj said...

Great post, but why if I need to call a oracle function that returns a NUMBER?
I've been looking for an examples with no luck, so what do you think about?
Any idea will be wellcomed.
Thanks

joanoj said...

At last, I'm using:

Long count = (Long) (getSession().createQuery("select count(*) from Locator where valuetype = "+type).uniqueResult());

And works fine!

Tim Myer said...

Hi joanoj,

Thanks for the feedback and for the follow-up!

---Tim---

Unknown said...

I need to call stored procedure using hibernate
Could you please give the sample code to be written in DAO and maping file

Unknown said...

Need to have the sample code to call stored procedure in DAO(hibernate) and in mapping file too

Unknown said...

continuation to above question..

i am using SQL server 2000 DB

Tim Myer said...

Hi Vijji,

I imagine you would use the same process as in this blog posting to call the SQL Server stored procedure. The annotated Java syntax should be similar. As for the stored procedure syntax, it has been quite a while since I have written T-SQL, so I am sure you could find more knowledgeable resources out there.

Best of luck!
---Tim---

bisaya said...

cool, very informative post! i also wanna share this very spoon fed version of Spring + Hibernate + JPA combo

http://www.adobocode.com/spring/spring-with-hibernate-annotations

tnx

Unknown said...

Hi,
My requirement is to pass an blob object to an oracle procedure.
I am able to save & retrieve a blob object using hibernate in a table when the blob is inside a Entity.
However when i try to pass a blob to a procedure i get the exception
"java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB"

My code consist of:
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{call ORACLE_STORED_PROCEDURE(?)}");
call.setBlob(1, Hibernate.createBlob(byteData));// byteData is a byte[]
call.execute();
}
});

I am using Hibernate 3.3.2.GA & oracle 10g-ojdbc14.jar.

The stacktrace is:
java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB
at oracle.jdbc.driver.OraclePreparedStatement.setBlobInternal(OraclePreparedStatement.java:5749)
at oracle.jdbc.driver.OracleCallableStatement.setBlob(OracleCallableStatement.java:4361)
at com.MyDAO$1.execute(MyDAO.java:1437)
at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1858)
at MyDAO.method1(MyDAO.java:1434)

Pls provide a resolution.

Manish said...

Hi,i am getting this error "org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL usp_LoginDetails(?, :p_login)]"

My SP file:
create or replace PROCEDURE usp_LoginDetails

(

p_out IN OUT SYS_REFCURSOR,
p_login IN VARCHAR2

)
AS
login VARCHAR2(50);
BEGIN

--login := p_login;


open p_out for
select ms.moduleId,ms.parentModule,ms.headorder,ms.menuname,ms.clsname,ms.headId,ms.parentId,ms.sessiontime,ms.menuorder
,ms.ctx,ms.toolbartype,ms.css,ms.windowsize,ms.webclsname
from usermapping um inner join modulestructure ms on um.moduleId=ms.moduleId
where um.loginname=p_login and ms.status='A';

END;

My HBM file








My Dao implimentation file :

public LoginProcedure getLoginProcedureByNamedQuery(String p_login) {
//throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.

try {
ServiceRegistryBuilder().applySettings(configuration.getProperties()).build();

sessionFactory.openSession().getNamedQuery("dd").setParameter("p_login", p_login);

Query lquery = getHibernateTemplate().getSessionFactory().openSession().getNamedQuery("dd");
lquery.setParameter("p_login", p_login);

List ll= lquery.list();

System.out.println("value of ll"+ll);




}catch(Exception e){
e.printStackTrace();
}

return null;
}



}