Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Hi All,

Dose anybody know how to work with Oracle DBLink with HQL and Hibernate, or dose Hibernate support Oracle DBLink (yet)?

Thanks a lot!
John

As in "CREATE DBLINK"? Sure, that is exposed transparently through Oracle's jdbc driver; just make sure your schema owner is the DBLINK owner (or use an appropriate SYNONYM).

Thank you Steve!

I am asking our DBA to 'merge' the dblink_schema to be the same as my_schema, so I can query something like select * from my_schema.abc@dblink.

If DBlink is transparent, I do not need to map my table as:
<class name="Abc" table="Abc@dblink"> in abc.hbm.xml file, correct?

Before I can test, I have Parent/Child relationship from my_schema.ParentTable to dblink_schema.ChildTable, if I set up the relationship mapping in hbm.xmls, can Hibernate take care of them in cascade-insert/delete etc. (within one session)?

Thank a lot.
John
jzhao@amfm.com

No you would need to use the tablename@dblinkname syntax.

The best way is to use the SYNONYM approach. Just create a SYNONYM named 'tablename' (under the schema owner) for 'tablename@dblinkname'. Then you do not need to reference it using the '@' syntax.

Thank you Steve!

My DBA is not willing to go the SYNONYM approach, since there will be too many objects to maintain. If we still have other syntex issues, I will try to push this approach again.

My second question from last email, if DBlink is totally transparent, then session/transaction (2PC maybe)/RI for the two data sources can be handled by Hibernate just like in one (unified) data source??

Thanks,
John

Absolutely. This is a really great thing in Oracle. Essentially the oracle engine takes over resposibility for coordinating transactions between the two schemas (relative to the explicit transactions). I have never tried, but as far as I understand RI can also be defined across a DBLINK.

The CREATE SYNONYM command is painfully easy (CREATE SYNONYM synonymname FOR actualobjectname)... Don't quite understand the objection here; we used these quite extensively at my last job with blessings from three very good Oracle DBAs.

Steve,

Can we say that DBLink is an effective strategy to deal with transactions with multiple data sources?

John

AFAIKL, yes. I'd certainly want to ask my DBA how well Oracle handles transactions across DBLINKs; I would guess it handles it great, but I'm not an Oracle DBA...

Also, if you do a lot of joining/subquerying across DBLINKs, keep in mind that in my experience Oracle tends to have a hard time coming up with optimal execution plans. Specifically, remember the "DRIVING_SITE" query hint as sometimes that is the only remedy for badly performing queries involving DBLINKs...

Thanks a lot Steve, Hibernate is great so are you!