Geek Answers Handbook

Using Oracle Database Links without Unreadable Dynamic SQL

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

It gets very dirty, writing things 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 request grows.

What else can I do? I am stuck using stored procedures and expect my procedures to be executed with one of several db links.

+4
source share
2 answers

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

 CREATE OR REPLACE SYNONYM MyTableRemote FOR MyTable@database _link

Your stored procedures will simply reference the synonym MyTableRemote . Then you can have a separate method in which the name of the database link would be a parameter and all synonyms for the link to the database link would be changed.

 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;
+6
source

If you don't want to use the idea of ​​synonyms, you can try this method - use REPLACE and your own syntax to generate SQL - I find that this method makes debugging dynamic SQL easy:

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

More articles:


All Articles