SnowConvert AI - Oracle - Instructions 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', 'sclaus@oracle.com', 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;

---- 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', 'sclaus@oracle.com', 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;

CREATE TABLE TEST_TABLE2 (
    col2 INTEGER,
    CONSTRAINT constraint_name PRIMARY KEY (col2)
);

CREATE TABLE OTHER_TABLE (
    other_col INTEGER REFERENCES TEST_TABLE2 (col2)
);

DROP TABLE TEST_TABLE2 CASCADE CONSTRAINTS PURGE;

CREATE OR REPLACE TABLE TEST_TABLE2 (
       col2 INTEGER,
       CONSTRAINT constraint_name PRIMARY KEY (col2)
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   CREATE OR REPLACE TABLE OTHER_TABLE (
          other_col INTEGER REFERENCES TEST_TABLE2 (col2)
      )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   DROP TABLE TEST_TABLE2 CASCADE;

select seq1.nextval from dual;
select seq1.currval from dual;

select seq1.nextval from dual;

select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0069 - THE SEQUENCE CURRVAL PROPERTY IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! seq1.currval from dual;

alter session set nls_date_format = 'DD-MM-YYYY';

ALTER SESSION SET DATE_INPUT_FORMAT = 'DD-MM-YYYY' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

----** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                       ;

CREATE TABLE TABLITA
(
    COLUMN1 NUMBER
);

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

SELECT * FROM B.TABLITA_SYNONYM WHERE B.TABLITA_SYNONYM.COLUMN1 = 20;

CREATE OR REPLACE TABLE TABLITA
    (
        COLUMN1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

--    --** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **

--    CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                           ;

SELECT * FROM
    TABLITA
    WHERE
    TABLITA.COLUMN1 = 20;

CREATE TABLE TABLITA
(
    COLUMN1 NUMBER
);

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;
CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM;

SELECT * FROM C.TABLITA_SYNONYM2 WHERE C.TABLITA_SYNONYM2.COLUMN1 = 20;

UPDATE C.TABLITA_SYNONYM2 SET COLUMN1 = 10;

INSERT INTO C.TABLITA_SYNONYM2 VALUES (1);

CREATE OR REPLACE TABLE TABLITA
    (
        COLUMN1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

--    --** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **

--    CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                           ;

--    --** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM
                                                                  ;

SELECT * FROM
    TABLITA
    WHERE
    TABLITA.COLUMN1 = 20;

    UPDATE TABLITA
    SET COLUMN1 = 10;

    INSERT INTO TABLITA
    VALUES (1);

CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA;

CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM;

CREATE VIEW VIEW_ORGINAL AS SELECT * FROM C.TABLITA_SYNONYM2;

CREATE OR REPLACE SYNONYM VIEW_SYNONYM FOR VIEW_ORGINAL;

SELECT * FROM VIEW_SYNONYM;

----** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM B.TABLITA_SYNONYM FOR TABLITA
                                                       ;

----** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
--CREATE OR REPLACE SYNONYM C.TABLITA_SYNONYM2 FOR B.TABLITA_SYNONYM
                                                                  ;

CREATE OR REPLACE VIEW VIEW_ORGINAL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT * FROM
TABLITA;

----** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **

--CREATE OR REPLACE SYNONYM VIEW_SYNONYM FOR VIEW_ORGINAL
                                                       ;

SELECT * FROM
VIEW_ORGINAL;