SSC-EWI-OR0123

Database Link connections not supported

Severity

Medium

Description

A database link connection reference was removed from the object name because the database links and its references are not supported in Snowflake. The only part that is kept is the name before the @ character.

Example Code

Input Code:

IN -> Oracle_01.sql

-- Creation of the database link
CREATE DATABASE LINK mylink
    CONNECT TO user1 IDENTIFIED BY password1
    USING 'connection_str';

-- Statements that use the database link we created
SELECT * FROM employees@mylink;

INSERT INTO employees@mylink
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', '[e-mail]', SYSDATE, 'SH_CLERK');

UPDATE employees@mylink SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM employees@mylink
    WHERE employee_id = 999;

Output Code:

OUT -> Oracle_01.sql

---- Creation of the database link
----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **
--CREATE DATABASE LINK mylink
--    CONNECT TO user1 IDENTIFIED BY password1
--    USING 'connection_str'

    -- Statements that use the database link we created
SELECT * FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
    employees;

INSERT INTO
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
employees
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', '[e-mail]', CURRENT_TIMESTAMP(), 'SH_CLERK');

UPDATE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
employees
    SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
    employees
    WHERE employee_id = 999;

Recommendations

• It is important to check that all DB Links have different names, if two DB Links share the same and the code is migrated multiple times, then the EWI can change de information based on what DB Link is processed first.

• Move the database objects from the database link reference into the same database instance that is being used in Snowflake.

• If you need more support, you can email us at [e-mail]