Skip to Main Content
  • Questions
  • How to redirect a SQL query to a different database or add database links automatically?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Andy.

Asked: July 01, 2022 - 5:11 am UTC

Last updated: July 06, 2022 - 10:19 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

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?

and Chris said...

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 link

Instead of doing this, could you get the appropriate modules to connect to a service that uses the standby database?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good synonym idea, but the application cannot connect as a different user

Andy Haack, July 02, 2022 - 10:25 am UTC

Thanks for the synonym idea, Chris.
Unfortunately, the application can only connect with the same user as the ERP application.
I was hoping that there might be a trick or lesser known DB feature, such as SQL Translation, that could achive this, but it sounds like we will have to build a SQL parser.
Chris Saxon
July 04, 2022 - 4:24 pm UTC

Ah yes, I'd forgotten about the SQL Translation Framework!

You can use that to intercept and rewrite SQL statements:

https://docs.oracle.com/en/database/oracle/oracle-database/21/drdaa/SQL-translation-framework-overview.html

This is primarily designed to help you migrate from other database systems, so you can map their syntax to Oracle's. I'd be wary of using this to change normal application code, particularly when it's provided by another vendor - regardless of whether that's Oracle or someone else.

Change current_schema

emaN, July 06, 2022 - 5:20 am UTC

create view remote_schema.fnd_user as select * from fnd_user@db_link;

alter session set current_schema=remote_schema;
select * from fnd_user;
alter session set current_schema=local_schema;


Chris Saxon
July 06, 2022 - 10:19 am UTC

Good suggestion, could be worth looking into

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database