Hi Team,
While using a connection to database A, is it possible to automatically redirect a SQL query to execute on database B, as if the query was modified to have database links added to all objects?
We have a reporting application for an ERP system called Oracle E-Business Suite, and our reporting application uses the same database user and connection as the ERP system itself (APPS database user).
To reduce the load on the system, we would like to automatically redirect some of the queries to a standby database, which is accessible through a database link.
Is it possible to redirect a SQL query to execute on a remote database as if there were @db_links added to all of the SQL's objects and functions automatically?
For example, instead of a query like this:
select fu.user_name, xxen_util.client_time(fu.creation_date) creation_date, fu.user_id from fnd_user fu
we would like to execute this:
select fu.user_name, xxen_util.client_time@db_link(fu.creation_date) creation_date, fu.user_id from fnd_user@db_link fu
Our current assumption is that we would need to build a SQL parser that detects all of the SQL's functions and table or view objects automatically to add @db_link to them, but maybe there is a better way?
You could create a synonym or view over the database link:
create synonym fnd_user for fnd_user@db_link
You would need to create this in a different user and connect using that user. I have little knowledge of E-Business Suite, so can't really comment on the practicality of this.
we would like to automatically redirect some of the queries to a standby database, which is accessible through a database linkInstead of doing this, could you get the appropriate modules to connect to a service that uses the standby database?