Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

JPA Calling an Oracle function with OUT parameter

1,858 views
Skip to first unread message

Stanimir Stamenkov

unread,
Jun 12, 2008, 9:29:39 AM6/12/08
to
[Followup-To: comp.lang.java.databases]

I'm relatively new to JPA (Java Persistence API). I'm given an
Oracle function I need to execute through an EntityManager. The
function has an OUT parameter (the first one) declared in addition
to returning a value. I'm trying the following code:

EntityManager em;
...
Query query = em.createNativeQuery("{ ? = "
+ "call MY_FUNCTION(?,?,?,?,?,?,?) }");

query.setParameter(1, null); // XXX: registerOutParameter ?
query.setParameter(2, ...);
...
query.setParameter(7, ...);

List result = query.getResultList();

I don't know how I can register (and later get) the first parameter
as OUT parameter, pretty much like one could do with JDBC
(java.sql.CallableStatement), and I'm getting an exception as:

java.sql.SQLException: Missing IN or OUT parameter at index:: 9
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1680)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)

How could I workout the given case?

FWIW, I'm working with JBoss 4.2.2.GA and Seam 2.0.2.SP1.

--
Stanimir

Stanimir Stamenkov

unread,
Jun 12, 2008, 9:49:32 AM6/12/08
to
Thu, 12 Jun 2008 16:29:39 +0300, /Stanimir Stamenkov/:

> Query query = em.createNativeQuery("{ ? = "
> + "call MY_FUNCTION(?,?,?,?,?,?,?) }");
>
> query.setParameter(1, null); // XXX: registerOutParameter ?
> query.setParameter(2, ...);
> ...
> query.setParameter(7, ...);
>
> List result = query.getResultList();
>
> I don't know how I can register (and later get) the first parameter as
> OUT parameter, pretty much like one could do with JDBC
> (java.sql.CallableStatement), and I'm getting an exception as:
>
> java.sql.SQLException: Missing IN or OUT parameter at index:: 9

The last line actually states:

java.sql.SQLException: Missing IN or OUT parameter at index:: 8

The "index:: 9" comes from an experiment I've made adding one more
",?" to the function parameters, but seems no matter how many
question marks I add it always asks for an additional OUT one. If I
don't add additional ",?" and try to set:

query.setParameter(8, ...);

I get an "java.lang.IndexOutOfBoundsException: Remember that ordinal
parameters are 1-based!" at that line.

--
Stanimir

Owen Jacobson

unread,
Jun 12, 2008, 10:04:12 AM6/12/08
to
On Jun 12, 9:29 am, Stanimir Stamenkov <s7a...@netscape.net> wrote:
> [Followup-To: comp.lang.java.databases]
>
> I'm relatively new to JPA (Java Persistence API).  I'm given an
> Oracle function I need to execute through an EntityManager.  The
> function has an OUT parameter (the first one) declared in addition
> to returning a value.  I'm trying the following code:
>
>     EntityManager em;
>     ...
>     Query query = em.createNativeQuery("{ ? = "
>             + "call MY_FUNCTION(?,?,?,?,?,?,?) }");
>
>     query.setParameter(1, null);   // XXX: registerOutParameter ?
>     query.setParameter(2, ...);
>     ...
>     query.setParameter(7, ...);
>
>     List result = query.getResultList();
>
> I don't know how I can register (and later get) the first parameter
> as OUT parameter, pretty much like one could do with JDBC
> (java.sql.CallableStatement), and I'm getting an exception as:
>
> java.sql.SQLException: Missing IN or OUT parameter at index:: 9
>         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
>         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
>         at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePr eparedStatement.java:1680)
>         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedSt atement.java:3279)
>         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedState ment.java:3328)
>         at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(Wrapp edPreparedStatement.java:236)

>         at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>         at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
>         at org.hibernate.loader.Loader.doQuery(Loader.java:674)
>         at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j ava:236)

>         at org.hibernate.loader.Loader.doList(Loader.java:2220)
>
> How could I workout the given case?
>
> FWIW, I'm working with JBoss 4.2.2.GA and Seam 2.0.2.SP1.

If there's any support for it at all, it'll be a JPA-vendor-specific
feature. The JPA provider for JBoss 4 is Hibernate; as far as I'm
aware, there's no general purpose support for stored procedure calls,
only some special-case support for a few specific forms. Check the
hibernate docs for details:
<http://www.hibernate.org/hib_docs/reference/en/html/
querysql.html#sp_query>

16.2.2.1 has some specific notes for Oracle sprocs.

-o

Jason King

unread,
Jun 12, 2008, 10:54:34 AM6/12/08
to
If you can rewrite the pl/sql side as a function that might get you out
of hell.

from
--pl/sql
create procedure my_function( arg1 in out varchar2 , arg2 varchar2)
to
create function my_function(arg2 varchar2) return varchar2

at that point you should be able to
select my_function('arg1') from dual

Stanimir Stamenkov

unread,
Jun 12, 2008, 12:39:16 PM6/12/08
to
Thu, 12 Jun 2008 09:54:34 -0500, /Jason King/:

> Owen Jacobson wrote:
>
>> If there's any support for it at all, it'll be a JPA-vendor-specific
>> feature. The JPA provider for JBoss 4 is Hibernate; as far as I'm
>> aware, there's no general purpose support for stored procedure calls,
>> only some special-case support for a few specific forms. Check the
>> hibernate docs for details:
>> <http://www.hibernate.org/hib_docs/reference/en/html/querysql.html#sp_query>
>>
>> 16.2.2.1 has some specific notes for Oracle sprocs.
>
> If you can rewrite the pl/sql side as a function that might get you out
> of hell.

Thank you both Owen and Jason for your suggestions. I've followed
the Hibernate documentation and obtained a JDBC connection through
|session.connection()| preparing a CallableStatement which works,
but I'll most probably write an additional PL/SQL function (I can't
modify the existing one) to "normalize" the result, later.

--
Stanimir

0 new messages