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

Groups > comp.lang.java.databases > #182

Re: JPA Calling an Oracle

Show key headers only | View raw

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

Back to comp.lang.java.databases | Previous | Next | Find similar

Thread

Re: JPA Calling an Oracle "Jason King" <jason.king@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

csiph-web