aspose file tools*
The moose likes Object Relational Mapping and the fly likes Cant select VIEW via dblink with JPA Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Cant select VIEW via dblink with JPA" Watch "Cant select VIEW via dblink with JPA" New topic
Author

Cant select VIEW via dblink with JPA

ray frid
Ranch Hand

Joined: Dec 23, 2010
Posts: 79
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

Joined: Apr 14, 2004
Posts: 10336

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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
ray frid
Ranch Hand

Joined: Dec 23, 2010
Posts: 79
Hi,
I am using jboss JPA. so inside the jpa perspective I use the drive: Oracle.jdbc.driver.oracledriver.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Dec 23, 2010
Posts: 79
But the driver that I mention is non-xa. is t?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Dec 23, 2010
Posts: 79
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!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cant select VIEW via dblink with JPA