Is it OK to use dynamic SQL for database links

Question:

As a part of a data warehousing application I have to move data collected from different tables from one database to another via a database link. The database link name can change, so I am using dynamic SQL to INSERT data into destination statements. On average this program has to handle around 100,000 records per day. Is this a good approach, or are there better ones that don't require hard-coding of the database link name?

Answer:

Dear Joseph: Looks like you really do need to use dynamic SQL, except for this possibility: Is the name of the database link truly unknowable in advance or is it simply that there is a choice of, say, 10 different DB links? If it is a known set of database links, then you might consider generating all the insert statements you need in advance. Then you can use a CASE statement to select the appropriate insert at runtime. This approach will have less overhead.
One other thing to consider: you can achieve a certain level of indirection with the TNS alias in a database link. Consider the following statement:
CREATE DATABASE LINK x
CONNECT TO u IDENTIFIED BY p
USING 'the_alias'
As long as the username and password are the same from target to target, you can just change the tnsnames.ora on the database server to make 'the_alias' point to a new target. (Of course, this approach requires unambiguous DBA authority.) Even better: if the code were properly managed, you could even edit the tnsnames.ora using UTL_FILE!