I'm building an interface between two Oracle databases (A and B) on separate bits of hardware. I'm taking suggestions about the next options (or searching for other ideas) for referencing posts from database B in code on database A (I'm while using %ROWTYPE feature in Oracle its my variables)
*Get it done hard way SCHEMA.TABLE.COLUMN@LINK%ROWTYPE
Documents the code very well
Ugly TOAD debugger has issues with database links.
Make use of a view in database A from the object in database B VIEW.COLUMN%ROWTYPE
Clean looking It’s the way we have always done it
Building on top of View can get overly complicated It’s the way we have always done it
Produce a synonym in database A of the position of the object in B SYNONYM%ROWTYPE
I'd opt for the synonymn approach.
A synonymn is syntactic sugar made to avoid such problems as needing to use database.schema.table everywhere. If anybody is wondering exactly what the synonymn does, it's immediately within the data dictionary to allow them to query therefore it is pretty straightforward.
Nevertheless, this method isn't very different by using a view. Exactly why is building on the top from the view more difficult than on the top on the table? I am presuming the vista essentially chooses all of the posts and all sorts of the rows in the remote db without joining to anything.
I'd tend towards a view or materialized view. This way the column definitions (data type, length) take presctiption the neighborhood database and also you will not get massive problems when the remote database is not available.
11g has added some features to ensure that code is not recompiled as frequently. What this means is there's some dependencies lower in the column level, as opposed to just the item level. It might be interesting to determine how that actually works having a synonym with an object on the database link.
Have you contemplated using database replication like the materialized sights?
By doing this, neither database heading down will effect another as they're going to have their very own saved snapshot of information.
I'd use synonyms, you can begin every synonym having a certain prefix, so individuals will realize that they access something from 'the other side'. Your synonym title does not need to be exactly comparable to the item title.
You might use materialized sights but maybe Oracle streams are better still.