Using Oracle database links without unreadable dynamic SQL

Question

How can I write a neat PL/SQL stored procedure that can execute against a given database link?

It gets really messy, writing stuff like this:

   PROCEDURE my_proc(aDbLink IN VARCHAR2)
   IS
   BEGIN       
       EXECUTE IMMEDIATE '
       SELECT mycolumn, anothercolumn
       FROM MYTABLE@' || aDbLink || '
       WHERE such-and-such...'
   END

as the query gets bigger.

What else might I do? I'm stuck using stored procedures, and expect that my procedures will execute against one of several db links.

score 6 · Accepted Answer · answered Jun 02 '11 at 00:37

6

The simplest way to avoid using dynamic SQL would be to create synonyms.

CREATE OR REPLACE SYNONYM MyTableRemote
   FOR MyTable@database_link

Your stored procedures would then simply refer to the synonym MyTableRemote. You could then have a separate method that took the database link name as a parameter and changed all the synonyms to point at the database link.

PROCEDURE replace_synonyms( p_db_link IN VARCHAR2 )
AS
BEGIN
  -- Adjust the query to identify all the synonyms that you want to recreate
  FOR syn IN (SELECT *
                FROM user_synonyms
               WHERE db_link IS NOT NULL)
  LOOP
    EXECUTE IMMEDIATE 
      'CREATE OR REPLACE SYNONYM ' || syn.synonym_name ||
      '   FOR ' || syn.table_owner || '.' || syn.table_name || '@' || p_db_link;
  END LOOP;
END;

answered Jun 02 '11 at 00:37

Justin Cave

227,342
24
367
384

3

The synonyms are a good idea. You could extend it and have a local schema corresponding to each remote DB and synonyms pointing to those object on the remote DB. Then an invoker rights procedure and an ALTER SESSION SET CURRENT_SCHEMA could resolve without needing dynamic SQL. – Gary Myers Jun 02 '11 at 00:45
Be aware that changing a synonym is DDL, and will cause the database to commit any unsaved work beforehand. – eaolson Jun 02 '11 at 01:01
1

I hadn't considered the possibility of updating a synonym. But could that possibly work for an enterprise application, where many simultaneous processes will call sprocs, using different db links? – Ladlestein Jun 08 '11 at 01:55
1

@Ladlestein - If each session is going to use the same Oracle user account and are simultaneously going to need to access a different database, then creating and replacing synonyms is not viable. Gary's suggestion of setting the CURRENT_SCHEMA for each session along with fixed synonyms would work in that scenario. – Justin Cave Jun 08 '11 at 20:49

score 1 · Answer 2 · answered Jun 02 '11 at 01:15

If you don't want to use the synonym idea, you could try this method - use REPLACE and your own syntax to generate the SQL - I find this method makes debugging dynamic SQL a breeze:

PROCEDURE my_proc(aDbLink IN VARCHAR2)
IS
BEGIN       
    EXECUTE IMMEDIATE REPLACE('
    SELECT mycolumn, anothercolumn
    FROM MYTABLE@#DBLINK#
    WHERE such-and-such...'
       ,'#DBLINK#', aDbLink);
END

Using Oracle database links without unreadable dynamic SQL

2 Answers2

Linked