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.


  • 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
  • 4
    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
  • 1
    Yup, that'd work. – Philᵀᴹ Feb 15 '13 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.