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
> 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
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
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
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