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
– ScaisEdge, Commented Jan 8, 2018 at 15:03
Merge is not supported in mysql stackoverflow.com/questions/42663074/… — P.Salmon
– P.Salmon, Commented Jan 8, 2018 at 15:10
mysql or plsql? Not the same thing. Please adjust your tags appropriately. — ADyson
– ADyson, Commented Jan 8, 2018 at 15:33
In case this is Oracle, dynamic SQL requires special handling: docs.oracle.com/database/121/LNPLS/dynamic.htm — William Robertson
– William Robertson, Commented Jan 8, 2018 at 17:07

Kaushik Nayak · Accepted Answer · 2018-01-08 17:47:26Z

1

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;

answered Jan 8, 2018 at 17:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variable DB name in SQL script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related