JPA Calling an Oracle function with OUT parameter

[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.process CompletedBindRow(OraclePreparedStatement.java:1680 )
at oracle.jdbc.driver.OraclePreparedStatement.execute Internal(OraclePreparedStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.execute Query(OraclePreparedStatement.java:3328)
at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(WrappedPreparedStatement.java: 236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:67 4)
at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:222 0)

How could I workout the given case?

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

--
Stanimir

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

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.process CompletedBindRow(OraclePr eparedStatement.java:1680)
> * * * * at oracle.jdbc.driver.OraclePreparedStatement.execute Internal(OraclePreparedSt atement.java:3279)
> * * * * at oracle.jdbc.driver.OraclePreparedStatement.execute Query(OraclePreparedState ment.java:3328)
> * * * * at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(Wrapp edPreparedStatement.java:236)
> * * * * at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:186)
> * * * * at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1787)
> * * * * at org.hibernate.loader.Loader.doQuery(Loader.java:67 4)
> * * * * at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.j ava:236)
> * * * * at org.hibernate.loader.Loader.doList(Loader.java:222 0)
>
> 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

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
Owen Jacobson wrote:
> 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.process CompletedBindRow(OraclePr eparedStatement.java:1680)
>> at oracle.jdbc.driver.OraclePreparedStatement.execute Internal(OraclePreparedSt atement.java:3279)
>> at oracle.jdbc.driver.OraclePreparedStatement.execute Query(OraclePreparedState ment.java:3328)
>> at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(Wrapp edPreparedStatement.java:236)
>> at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:186)
>> at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1787)
>> at org.hibernate.loader.Loader.doQuery(Loader.java:67 4)
>> at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.j ava:236)
>> at org.hibernate.loader.Loader.doList(Loader.java:222 0)
>>
>> 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

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