Create Database Link

Currently, Create Database Link statement is not being converted but it is being parsed. Also, if your source code hascreate database link statements, these are going to be accounted for in the Assessment Report.

Example of a Source Code

IN -> Oracle_01.sql

CREATE PUBLIC DATABASE LINK db_link_name
CONNECT TO CURRENT_USER
USING 'connect string'

CREATE DATABASE LINK db_link_name2
CONNECT TO user_name IDENTIFIED BY user_password
USING 'connect string'

CREATE PUBLIC DATABASE LINK db_link_name3

Snowflake output

OUT -> Oracle_01.sql

----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **
--CREATE PUBLIC DATABASE LINK db_link_name
--CONNECT TO CURRENT_USER
--USING 'connect string'

----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **
--CREATE DATABASE LINK db_link_name2
--CONNECT TO user_name IDENTIFIED BY user_password
--USING 'connect string'

----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **

--CREATE PUBLIC DATABASE LINK db_link_name3

Database Link References

If in your input code you use objects from the database link the output code will keep the name of these objects but the name of the database link that they are using will be removed.

Example of a Source Code

IN -> Oracle_02.sql

-- CREATE DATABASE LINK STATEMENTS
CREATE DATABASE LINK mylink1
    CONNECT TO user1 IDENTIFIED BY password1
    USING 'my_connection_string1';

CREATE DATABASE LINK mylink2
    CONNECT TO user2 IDENTIFIED BY password2
    USING 'my_connection_string2';

-- SQL statements that use the database links
SELECT * FROM products@mylink1;

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

UPDATE jobs@mylink2 SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM employees@mylink2
    WHERE employee_id = 999;

-- SQL statement where it uses an object from
-- a database link that is not created
SELECT * FROM products@mylink;

Snowflake output

OUT -> Oracle_02.sql

---- CREATE DATABASE LINK STATEMENTS
----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **
--CREATE DATABASE LINK mylink1
--    CONNECT TO user1 IDENTIFIED BY password1
--    USING 'my_connection_string1'

----** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **

--CREATE DATABASE LINK mylink2
--    CONNECT TO user2 IDENTIFIED BY password2
--    USING 'my_connection_string2'

-- SQL statements that use the database links
SELECT * FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink1 | USER: user1/password1 | CONNECTION: 'my_connection_string1' ] ***/!!!
    products;

INSERT INTO
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink2 | USER: user2/password2 | CONNECTION: 'my_connection_string2' ] ***/!!!
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 : mylink2 | USER: user2/password2 | CONNECTION: 'my_connection_string2' ] ***/!!!
jobs
    SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink2 | USER: user2/password2 | CONNECTION: 'my_connection_string2' ] ***/!!!
    employees
    WHERE employee_id = 999;

-- SQL statement where it uses an object from
-- a database link that is not created
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "mylink" **
SELECT * FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: / | CONNECTION:  ] ***/!!!
    products;

Related EWIs

1. SSC-EWI-OR0123: Db Link connections not supported.

2. SSC-FDM-0007: Element with missing dependencies.