Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Cant select VIEW via dblink with JPA

 
ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, Using jboss5.1.x, EJB3.0 ,JPA3

I am trying to do 'select' query from view which is connected via dblink to another database.

this is the view which is created in the oracle database:

CREATE OR REPLACE VIEW INF_METER_ECMS_VW
(ID, METER_NUM, MODEM_ID, MODEM_TYPE, PORT)
AS
select INFMETERID, INFMETERNUM, INFMODEMIP, INFMODEMTYPE, INFMODEMPORT From INF_METER_ECMS_VW@TO_VANTIVE8
/

if I run the select in the oracle editor it works fine, but when I run it with JPA in the java code (MyEclipse) I get wierd exception.

code:

String queString = "select * From ECMS.INF_METER_ECMS_VW";
Query q1 = em.createNativeQuery(queString, InfMeterEcmsVw.class);
InfMeterEcmsVw newCall = null;
List results = q1.getResultList();
if (!results.isEmpty())
newCall = (InfMeterEcmsVw) results.get(0);
}
this is the exception:

15:27:06,625 WARN [JDBCExceptionReporter] SQL Error: 24777, SQLState: 99999

15:27:06,625 ERROR [JDBCExceptionReporter] ORA-24777: use of non-migratable database link not allowed

Also tried this code, and I got the same exception:

ArrayList<InfMeterEcmsVw> counterCalls = (ArrayList<InfMeterEcmsVw>) em.createQuery("from InfMeterEcmsVw").getResultList();
Any idea how could I solve it?

thanks, ray.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That error code means you can't perform an operation with a remote DB without opening a connection to it (which is what your Oracle editor is probably doing behind the scenes). I've never tried what you are attempting so the next statement is a guess, but at the bare minimum I'd expect you to need an XA driver. Are you using one?
 
ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am using jboss JPA. so inside the jpa perspective I use the drive: Oracle.jdbc.driver.oracledriver.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:That error code means you can't perform an operation with a remote DB without opening a connection to it (which is what your Oracle editor is probably doing behind the scenes). I've never tried what you are attempting so the next statement is a guess, but at the bare minimum I'd expect you to need an XA driver. Are you using one?

With database link the connection is opened and maintained automatically by the database, transparently to the application (just not all operations are possible over a database link). No need to specifically open or close it.

According to this article the problem is caused by using an XA driver. This article seems especially promising (offers three possible solutions), but there are lots of other materials when googling for the error code.
 
ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But the driver that I mention is non-xa. is t?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:According to this article the problem is caused by using an XA driver. This article seems especially promising (offers three possible solutions), but there are lots of other materials when googling for the error code.


Ah. Incorrect guess then.
 
ray frid
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:
Paul Sturrock wrote:That error code means you can't perform an operation with a remote DB without opening a connection to it (which is what your Oracle editor is probably doing behind the scenes). I've never tried what you are attempting so the next statement is a guess, but at the bare minimum I'd expect you to need an XA driver. Are you using one?

With database link the connection is opened and maintained automatically by the database, transparently to the application (just not all operations are possible over a database link). No need to specifically open or close it.

According to this article the problem is caused by using an XA driver. This article seems especially promising (offers three possible solutions), but there are lots of other materials when googling for the error code.



Thanks for the link it helped!! I used shared connection!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic