Oracle Interface

Question

I am building an interface between two Oracle databases (A and B) on separate pieces of hardware. I am soliciting advice on the following options (or looking for other ideas) for referencing columns from database B in code on database A (I am using the %ROWTYPE feature in Oracle for all of my variables)

*Do it the hard way SCHEMA.TABLE.COLUMN@LINK%ROWTYPE

Pros

     Documents the code very well

Cons

     Ugly

     TOAD debugger has issues with database links.

Use a view in database A of the object in database B VIEW.COLUMN%ROWTYPE

Pros

     Clean looking 
     It’s the way we have always done it

Con

     Building on top of View can get overly complicated
     It’s the way we have always done it

Create a synonym in database A of the location of the object in B SYNONYM%ROWTYPE

Pro

    It’s different

Con

    It’s different

Answer 1

Have you thought about using database replication such as the materialized views?

http://www.orafaq.com/wiki/Advanced_Replication_FAQ

This way, neither database going down will effect the other as they will have their own stored snapshot of data.

Answer 2

I would go with the synonymn approach.

A synonymn is syntactic sugar designed to avoid such problems as having to use database.schema.table everywhere. If anyone is wondering what the synonymn does, it's right there in the data dictionary for them to query so it's pretty straightforward.

That said, this approach is not much different from using a view. Why is building on top of the view more complicated than on top of the table? I'm assuming the view basically selects all the columns and all the rows from the remote db without joining to anything.

Answer 3

I'd tend towards a view or materialized view. That way the column definitions (data type, length) are on the local database and you won't get massive problems if the remote database is unavailable.

11g has added some features so that code isn't recompiled as often. This means there's some dependencies down at the column level, rather than just the object level. It would be interesting to see how that works with a synonym on an object over a database link.

Answer 4

I would use synonyms, you can start every synonym with a certain prefix, so people will know that they access something from 'the other side'. Your synonym name doesn't have to be exactly equal to the object name.

You could also use materialized views but maybe Oracle streams are even better.