passing a Cursor as a parameter using hibernate query object to a stored procedure

I have a stored procedure which takes a cursor as parameter. I have a namedquery defined in hibernate mappings to call the stored procedure… I need to make a call it from a DAO using hibernateTemplate (spring, hibernate)

This is how I am doing it..

Query q =  (Query) hibernateTemplate.findByNamedQuery("getFromGTT", OracleTypes.CURSOR);

but it gives me some exception…

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(
    at org.hibernate.exception.JDBCExceptionHelper.convert(
    at org.hibernate.loader.Loader.doList(
    at org.hibernate.loader.Loader.listIgnoreQueryCache(
    at org.hibernate.loader.Loader.list(
    at org.hibernate.loader.custom.CustomLoader.list(
    at org.hibernate.impl.SessionImpl.listCustomQuery(
    at org.hibernate.impl.AbstractSessionImpl.list(
    at org.hibernate.impl.SQLQueryImpl.list(
    at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(
    at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(
    at dao.GTTDaoImpl$1.doInHibernate(
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(
    at dao.GTTDaoImpl.getDataFromGTT(
    at test.TestOracleProcedures.test(
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(
    at java.lang.reflect.Method.invoke(
    at org.junit.internal.runners.TestMethodRunner.executeMethodBody(
    at org.junit.internal.runners.TestMethodRunner.runUnprotected(
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(
    at org.junit.internal.runners.TestMethodRunner.runMethod(
    at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(
    at org.junit.internal.runners.TestClassRunner$1.runUnprotected(
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_FROM_GTT'

need a solution.. Thanks


You should post your procedure spec for people to better help you, but since you mention passing in a cursor, you should know there are restrictions on calling stored procedures from Hibernate:

For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

If you can’t meet these requirements, you can still invoke the procedure by invoking a native JDBC call.

Further, OracleTypes.CURSOR is a JDBC type, its not a bind variable. If your procedure needs a value to execute, you need to pass that actual value in to findByNamedQuery.

Leave a Reply

Your email address will not be published. Required fields are marked *