Dynamic DBLink in Oracle

Question

I'm trying to run a set of queries over a DBLink that is determined at runtime (from configuration or other input). I have seen many examples suggesting to use dynamic SQL and EXECUTE IMMEDIATE. I would like to try and avoid this at all costs, since my queries are lengthy and likely to change so placing them in a string would make maintenance much more difficult.

I tried having my queries reference a synonym and then just updating the synonym prior to executing the query:

EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM my_tbl_s FOR my_tbl_t' || p_dblink_name;

SELECT * FROM my_tbl_s;

This works, however I want to use this in a package, so when I execute the create synonym the package is invalidated. I have also tried separating my code into different packages and recompling after the synonym update:

EXECUTE IMMEDIATE 'ALTER PACKAGE my_pck COMPILE';   

However, the original calling package is still invalidated. Can anyone think of a way to achieve dynamic DB Links without using dynamic SQL?

Answer 1

I would change my attitude to dynamic SQL if I were you. What you are trying to do instead is not going to make your life any easier than storing queries in a string.

Dynamic DDL might (in theory) keep your SQL static, however, it has one major flaw which is a deal breaker alone - it is not transactional, which means, each time you invoke execute immediate 'create or replace something' - you commit.

Ok, say you somehow manage to deal with it using autonomous transactions - and then we immediately face concurrency issue: what happens when two concurrent transactions will try to overwrite the same synonym? What happens when there are 10 of them? You get bottleneck.

And I didn't even mention dependency invalidation which you already encountered.

Or you can replace your table with a view, like:

create view tab as 
select 1 as dblink, t.* as tab@dblink1 union all
select 2 as dblink, t.* as tab@dblink2

In this case, chances are, you get an N-tier distributed transaction each time you query that view, and have to be sure that all dblinks are online, and their number has to be less than OPEN_LINKS parameter, which controls maximum number of open connections per session.

Lots of sophisticated stuff to consider, which you are actually not supposed to consider by design of DBMS, lots opportunities for something to break. String handling is way easier, and more importantly, this is what you are supposed to do by design in these cases.

Answer 2

I use also synonyms for objects in another database, just for clarity and documentation. And we do change the database links, but from a script and not from PL/SQL, so we haven't run into package recompilation problems.

Having said that, I'd think about splitting this into two packages.

1. The package that uses the database link, in other words, the package you already have.

2. A "link changer package", with a procedure that changes the link (with EXECUTE IMMEDIATE), and then recompiles package 1 (or all invalid packages or all packages that depend on the link).

Will this work or are we running into a invalidation dependency cycle?