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:

   EXIT WHEN NR = 0;




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?

  • 4
    I think using execute immediate will work, if you're OK with using dynamic SQL. – FrustratedWithFormsDesigner Jun 30 '14 at 15:16
  • I agree execute immediate using dynamic SQL will work. Dynamic SQL can be frustrating to work with, but very flexible and robust. To help debug your Dynamic SQL you can always write it to a temp table. – user53794 Dec 10 '14 at 16:13
  • Can you keep the name consistent and just redefine the link to point elsewhere when you need to? – LoztInSpace Aug 10 '17 at 10:04

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.

| improve this answer | |

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?

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.