Variable DB name in SQL script

Question

I'm trying to write an SQL test script that accepts four variables and produces an SQL query that I can easily copy/paste into the target DB. I'm using a merge for this and am trying to make the 4 variables dynamic:

declare
  from_id       VARCHAR2(242);
  to_id         VARCHAR2(242);
  from_db       VARCHAR2(242);
  to_db         VARCHAR2(242);
  admin_account VARCHAR2(242);
begin
 from_id       := '123';
 to_id         := '234';
 from_db       := 'db1';
 to_db         := 'db2';

MERGE INTO (select * from tablename@to_db
              where id = to_id) T
 USING (SELECT * from tablename@from_db
         where id = from_id) S
  ON ( .... )
  WHEN MATCHED THEN
    UPDATE
    SET ....
  WHEN NOT MATCHED THEN
    INSERT
      (...)
    VALUES
      (...);

The problem is that tablename@to_db and tablename@from_db is giving me errors, saying that table does not exist. It appears as if it's not transforming "tablename@to_db" to "tablename@db2" like it is transforming "to_id" to "234".

Anyone know how to make the remote db name dynamic as well?

Thank you

UPDATE:

Using "to_db = tablename@db2" and putting "FROM to_db" in the SQL query also doesn't work. Same error.

If i remember right in mysql (and in others SQL env) is not possible use variable object name (table or column name) — ScaisEdge, Jan 08 '18 at 15:03
Merge is not supported in mysql https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql — P.Salmon, Jan 08 '18 at 15:10
mysql or plsql? Not the same thing. Please adjust your tags appropriately. — ADyson, Jan 08 '18 at 15:33
In case this is Oracle, dynamic SQL requires special handling: https://docs.oracle.com/database/121/LNPLS/dynamic.htm — William Robertson, Jan 08 '18 at 17:07

score 1 · Answer 1 · answered Jan 08 '18 at 17:47

If you are using Oracle, you might need a dynamic DML using EXECUTE IMMEDIATE.

from_db and to_db variables need to be concatenated (||) to the quoted sql.

declare
  from_id       VARCHAR2(242);
  to_id         VARCHAR2(242);
  from_db       VARCHAR2(242);
  to_db         VARCHAR2(242);
  admin_account VARCHAR2(242);
BEGIN
 from_id       := '123';
 to_id         := '234';
 from_db       := 'db1';
 to_db         := 'db2';

EXECUTE IMMEDIATE 'MERGE INTO (select * from tablename@'||to_db||
              'where id = to_id) T
 USING (SELECT * from tablename@'||from_db||
         'where id = from_id) S
  ON (....)
  WHEN MATCHED THEN
    UPDATE
    SET ....
  WHEN NOT MATCHED THEN
    INSERT
      (...)
    VALUES
      (...)';

 END;

Variable DB name in SQL script

1 Answers1