How to avoid hardcoding a database link name in a package body

Question

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a single table causes me lots of headache. It is a char(1) field that is used as a bit vector. Because the encodings are different, it gets reencoded and scrambled, so I have to update it using DBMS_HS_PASSTHROUGH:

C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@remotedb1;
DBMS_HS_PASSTHROUGH.PARSE@remotedb1(C, 'SELECT PKEY, HEX(FLAGS) FROM TABLE1' ) ;
LOOP
   NR := DBMS_HS_PASSTHROUGH.FETCH_ROW@remotedb1(C);
   EXIT WHEN NR = 0;

   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 1, M_PKEY);
   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 2, M_FLAGS);

   UPDATE TABLE1
   SET FLAGS = M_FLAGS
   WHERE PKEY = M_PKEY;

END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@remotedb1(C);

Now I have a specific db-link name, remotedb1, in my package body, while I need to retarget it to another db-link rather often. I tried creating a synonym for a db-link, but that wouldn't work. Is there another way around it other than using a &&variable and recompiling from source? Will wrapping each call in EXECUTE IMMEDIATE work?

score 1 · Answer 1 · answered Sep 05 '15 at 06:20

Changing each call of a component of the DBMS_HS_PASSTHROUGH to a dynamic sql statement (EXECUTE IMMEDIATE) is a possibility but I would prefer the following approach.

For tables and views of the remote database one creates views on the local database that wraps this object.
For packages, functions and procedures one creates packages, functions and procedures on the local database that wrap these object.

You already implemented this approach by creating the views for the remote tables. Conceptually the dbms_hs_passthrough package is a package on the remote database. Therefore its component are called with the database link appended and not with the database link supplied as string paramter. So you should write a wrapper package for the dbms_hs_passthrough package. The definition of the dbms_hs_passthrough-components can be found in the Database PL/SQL Packages and Types Reference or you can get it from the database dictionary (e.g. by issuing a describe command in sqlplus). If you change the database link name you only have to change your views and the small body of the wrapper package.

score 1 · Answer 2 · edited Jul 04 '18 at 19:58

Maybe you can create a synonym for the remote package and switch it out when you want to point to a different one?

SQL> execute dbms_workload_repository.create_snapshot@test;

PL/SQL procedure successfully completed.

SQL> create synonym testp for dbms_workload_repository@test;

Synonym created.

SQL> execute testp.create_snapshot;

PL/SQL procedure successfully completed.

You mentioned a synonym for the link but what about a synonym for the remote package?

score 0 · Answer 3 · answered Mar 01 '17 at 17:58

Don't repeat yourself is a good principle in coding that will prevent duplicate debugging, unit testing, bug fixes, etc., however it shouldn't be considered in isolation. Dynamic SQL has the downside (among others) of not showing up in the dependency chain. Package wrappers have the downside (among others) of requiring additional objects.

In a case like this, as long as you don't have too many different links you switch to, it may be preferable to simply repeat the small block of code for the other database link and wrap them both in an if/case statement that uses a parameter to determine which database link should be used. This way you don't have to recompile any code to change which link is used.

You could of course combine this method with miracle173's and implement a similar strategy for the view.

How to avoid hardcoding a database link name in a package body

3 Answers3