Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In our product we have a main application with an oracle 11g DB and an optional module with another DB.

We want the two to communicate through a dblink and I was told to put some code for the communication stored in the main application. In practice, since the optional module may not actually exists at the time the main application is deployed, this means writing code for tables that don't exist yet, but of which we know the structure, the ones in the optional module.

We can encapsulate the dblink inside materialized views in the main module and restrain from calling remote procedures from there, however, to my understanding, we can't actually define materialized views on non-existant dblinks.

Perhaps we could define dummy materialized views, e.g. empty ones defined on dual, that get properly redefined once the optional module is installed and the necessary dblink is created, with a procedure calling dynamic plsql.

What do you think about it? How would achieve that?



EDIT: currently I have to evaluate the opportunity of having the communication code in the main application without having the optional module already deployed. So I'm really interested in hearing how I can get to create materialized views over yet-to-be-defined dblinks and whether my proposed solution is reasonable.


share|improve this question
Why don't you install those optional procedures/objects only if/when you install the optional DB? – Mat Feb 15 '13 at 9:29
Answer in the edit. – user881430 Feb 15 '13 at 9:47
Create a loopback DB link that points to the current database – Phil Feb 15 '13 at 10:06
And then I'd need to recreate the mview when the optional module is deployed, right? – user881430 Feb 15 '13 at 11:19
Yup, that'd work. – Phil Feb 15 '13 at 11:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.