Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #179 > unrolled thread

JPA Calling an Oracle fun

Back to article view | Back to comp.lang.java.databases

Contents

  JPA Calling an Oracle fun "Stanimir Stamenkov" <stanimir.stamenkov@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: JPA Calling an Oracle "Owen Jacobson" <owen.jacobson@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

#179 — JPA Calling an Oracle fun

  To: comp.lang.java.databases,
[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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#181 — Re: JPA Calling an Oracle

  To: comp.lang.java.databases
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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web