1

I am writing an oracle stored procedure to synch data between our databases. We have dev, test, stage, and performance databases. I plan to use DB Links.

The problem I'm trying to solve is I want to be able to synch data into the dev database from any of the other 3 databases. I can create 3 DB Links in the dev database (pointing at test, stage, and performance) and pull data from the other 3 as needed. The problem is I don't know how I can dynamically change the db link in my stored proc. I want to avoid trying to use dynamic sql with execute immediate - that just leads to ugly string concatenation code. As an example, I have to either have 1 proc per db link or I have to have ugly if else blocks like this (keeping in mind this is just an example and I have many long queries in the procedure):

if (parm_env = 'test') then
    insert into table A select * from table A@test_link
elsif (parm_env = 'stage') then
    insert into table A select * from table A@stage_link
elsif (parm_env = 'performance') then
    insert into table A select * from table A@performance_link
end if;

...

if (parm_env = 'test') then
    insert into table B select * from table B@test_link
elsif (parm_env = 'stage') then
    insert into table B select * from table B@stage_link
elsif (parm_env = 'performance') then
    insert into table B select * from table B@performance_link
end if;

...

if (parm_env = 'test') then
    insert into table C select * from table C@test_link
elsif (parm_env = 'stage') then
    insert into table C select * from table C@stage_link
elsif (parm_env = 'performance') then
    insert into table C select * from table C@performance_link
end if;

I would rather have something like this where I resolve the environment specific DB link one time and use it throughout the entire procedure:

if (parm_env = 'test') then
    db_link := @test_link;
elsif (parm_env = 'stage') then
    db_link := @stage_link;
elsif (parm_env = 'performance') then
    db_link := @performance_link;
end if;


insert into table A select * from table A@db_link;
...
insert into table B select * from table B@db_link;
...
insert into table C select * from table C@db_link;

Any thoughts how I might be able to achieve this?

Improve this question
6
  • 1
    A link is an object identifier, and as such must be a literal, not a variable. Dynamic SQL solves this. Drop your requirement to not use execute immediate and use it anyway. That's the obvious answer. If you struggle with escaping single quotes and don't like it for this reason, try the alternate quoting mechanism instead, q'{ ... }'. Commented May 15 at 19:09
  • 1
    Assuming that you are only ever going to run this procedure in a single session at a time, one option would be to create synonyms for each table using dynamic SQL in one proc and then reference those synonyms in your procedure. That assumes that your refresh procedure is doing some amount of logic, not just doing a blind insert from the remote side. If you're doing a blind insert from the remote side, it doesn't make sense to write code rather than just using the import and export utilities. Commented May 15 at 20:26
  • Using synonyms is of course a possibility, but involves a dictionary change (DDL) which takes out various row and library cache locks and causes invalidations that can hamper concurrency and can throw errors. For occasional redirections this might be okay, but inadvisable for something that will change frequently and/or programmatically. Using dynamic SQL is a lot safer. Commented May 16 at 12:02
  • thanks for the replies and thought put into them! I self impose the requirement to not use dynamic sql. Its tempting but my question is of course an over simplification and its going to be a few thousand lines of code. I don't want so many dynamic sql queries. It quickly becomes unreadable and unmanagable in my experience. If I had to go that route I will instead just bite the bullet and change the db link every time I need to pull from a different database. What about dynamically generating the DB link at the start of the proc? Im guessing that would cause issues or invalidate the proc? Commented May 17 at 0:22
  • ie what if I ran execute immediate "CREATE DATABASE LINK dynamic_dblink USING 'host_name.abc_xyz.com:1521/srvc_name.abc_xyx.com" and the rest of the proc references table@dynamic_dblink . Im not opposed to using all dynamic sql - just dont want to use it a hundred times. Once at the start of the proc is not terrible Commented May 17 at 0:28
Related questions
1869
Insert results of a stored procedure into a temporary table
471
SQL Server SELECT into existing table
435
SQL Server Insert if not exists
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.