SnowConvert: Oracle Functional Differences¶

 CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;

 CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;

 CREATE OR REPLACE SEQUENCE SEQUENCE1
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH 9223372036854775808
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

 CREATE OR REPLACE SEQUENCE SEQUENCE2
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH -9223372036854775809
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

 WITH dup_hiredate(eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS 
(SELECT aValue from atable) SEARCH DEPTH FIRST BY hire_date SET order1 SELECT aValue from atable;

 WITH dup_hiredate(eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS
(
SELECT aValue from
atable
) /*** SSC-FDM-OR0003 - SEARCH CLAUSE REMOVED FROM THE WITH ELEMENT STATEMENT ***/
SELECT aValue from
atable;

 SELECT LEVEL,
       LPAD(' ', 2 * (LEVEL - 1)) || NAME AS FORMATTED_NAME,
       JOB_TITLE
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY NAME;

 SELECT LEVEL,
       NVL(
       LPAD(' ', 2 * (
                      !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!LEVEL - 1)) :: STRING, '') || NVL(NAME :: STRING, '') AS FORMATTED_NAME,
       JOB_TITLE
FROM
       EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY
       PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER BY
       NAME /*** SSC-FDM-OR0004 - SIBLINGS KEYWORD REMOVED FROM ORDER BY CLAUSE BECAUSE SNOWFLAKE DOES NOT SUPPORT IT ***/;

 CREATE TABLE TABLE1
(
    COLUMN1 NUMBER
);

CREATE OR REPLACE SYNONYM B.TABLE1_SYNONYM FOR TABLE1;
SELECT * FROM B.TABLE1_SYNONYM WHERE B.TABLE1_SYNONYM.COLUMN1 = 20;

 CREATE OR REPLACE TABLE TABLE1
    (
        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.TABLE1_SYNONYM FOR TABLE1
                                                         ;
SELECT * FROM
    TABLE1
    WHERE
    TABLE1.COLUMN1 = 20;

 CREATE TABLE Table1(
  col1 INT NOT NULL RELY
);

 CREATE OR REPLACE TABLE Table1 (
    col1 INT NOT NULL /*** SSC-FDM-OR0006 - CONSTRAINT STATE RELY REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
  ;

 CREATE OR REPLACE EDITIONABLE PROCEDURE FUN1 (n number)is
l_result number;
begin
    DELETE FROM employees;
end;

 --** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE FUN1 (n NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        l_result NUMBER(38, 18);
    BEGIN
        DELETE FROM
            employees;
    END;
$$;

 SELECT location_id  FROM locations 
MINUS ALL 
SELECT location_id  FROM departments;

 SELECT location_id  FROM
locations
--** SSC-FDM-OR0008 - QUANTIFIER 'ALL' NOT SUPPORTED FOR THIS SET OPERATOR, RESULTS MAY DIFFER **
MINUS
SELECT location_id  FROM
departments;

-- Additional Params: -t JavaScript
--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE AUTHID DEFINER IS
  stmt_no  POSITIVE;
BEGIN
  IF SQL%ROWCOUNT = 0 THEN
   EXIT ;
  END IF;
  IF SQL%ISOPEN THEN
   EXIT ;
  END IF;
  IF SQL%FOUND THEN
   EXIT ;
  END IF;
  IF SQL%NOTFOUND THEN
   EXIT ;
  END IF;
END;

 -- Additional Params: -t JavaScript
--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "12/16/2024",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
  !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
  //AUTHID DEFINER
  null
  // SnowConvert Helpers Code section is omitted.

  let STMT_NO = new POSITIVE();
  if (SQL.ROWCOUNT /*** SSC-FDM-OR0009 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/ == 0) {
    break;
  }
  if (SQL.ISOPEN) {
    break;
  }
  if (SQL.FOUND) {
    break;
  }
  if (SQL.NOTFOUND) {
    break;
  }
$$;

 CREATE TABLE SampleNumberTable(Col1 NUMBER(4, 5));

INSERT INTO SampleNumberTable (Col1)
VALUES (0.00009);

INSERT INTO SampleNumberTable (Col1)
VALUES (0.000021);

INSERT INTO SampleNumberTable (Col1)
VALUES (0.012678912);

SELECT * FROM SampleNumberTable;

 CREATE OR REPLACE TABLE SampleNumberTable (Col1 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** 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"}}'
;

INSERT INTO SampleNumberTable(Col1)
VALUES (0.00009);

INSERT INTO SampleNumberTable(Col1)
VALUES (0.000021);

INSERT INTO SampleNumberTable(Col1)
VALUES (0.012678912);

SELECT * FROM
SampleNumberTable;

 CREATE OR REPLACE FUNCTION TEST(SAMPLE_A      IN NUMBER DEFAULT NULL,
                               SAMPLE_B       IN NUMBER DEFAULT NULL)
  RETURN NUMBER
 AS
BEGIN
    raise_application_error(-20001, 'First exception message', FALSE);
  RETURN 1;
END TEST;

 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE TEST(SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
                              SAMPLE_B NUMBER(38, 18) DEFAULT NULL)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'FIRST EXCEPTION MESSAGE');
 BEGIN
  --** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT FALSE WAS REMOVED. **
  RAISE FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
  RETURN 1;
 END;
$$;

 COMMIT;
ROLLBACK;

 --** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;

--** SSC-FDM-OR0012 - ROLLBACK REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
ROLLBACK;

 CREATE OR REPLACE FORCE NONEDITIONABLE VIEW VIEW01 AS
SELECT
      UNIQUE A.*
FROM
      TABLITA A
WHERE
      A.X = A.C CONNECT BY NOCYCLE A.C = 0 START WITH A.B = 1
HAVING
      X = 1
GROUP BY
      A.C;

 CREATE OR REPLACE VIEW VIEW01
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
SELECT DISTINCT
      A.*
FROM
      TABLITA A
WHERE
      A.X = A.C
GROUP BY
      A.C
HAVING
      X = 1
--** SSC-FDM-OR0013 - CYCLE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
CONNECT BY
      A.C = 0 START WITH A.B = 1;

 CREATE TABLE "MyDb"."MyTable"
(
    "COL1" NUMBER,
    CONSTRAINT "PK" PRIMARY KEY ("COL1")
);

CREATE TABLE "MyDb"."MyTable1"
(   
    "COL1" NUMBER(*,0),
    CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1")
);

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

    CREATE OR REPLACE TABLE "MyDb"."MyTable1"
    (
        "COL1" NUMBER(38) /*** 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"}}'
    ;

    ALTER TABLE "MyDb"."MyTable1"
    ADD
    --** SSC-FDM-OR0014 - FOREIGN KEY DATA TYPE MISMATCH **
    CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1");

 CREATE TABLE char_table
(
	char_column1 CHAR(15)
);

INSERT INTO char_table VALUES ('Hello world');

SELECT char_column1, LENGTHB(char_column1), LENGTH('Hello world') FROM char_table;

CREATE OR REPLACE TABLE char_table
(
	char_column1 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO char_table
VALUES ('Hello world');

SELECT char_column1,
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/, LENGTH('Hello world') FROM
char_table;

 COMMIT WORK FORCE '22.57.53';
ROLLBACK WORK FORCE '22.57.53';

 --** SSC-FDM-OR0016 - COMMIT OPTIONS REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM **
--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT WORK;

--** SSC-FDM-OR0016 - ROLLBACK OPTIONS REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM **
--** SSC-FDM-OR0012 - ROLLBACK REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
ROLLBACK WORK;

 SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE DBTIMEZONE FROM DUAL;

 SELECT
--** SSC-FDM-OR0017 - DBTIMEZONE WAS REMOVED TO USE THE DEFAULT VALUE OF THE TIMESTAMP **
TO_TIMESTAMP_LTZ( TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;

 MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT MATCHED THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr';

 --** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "people_target", "people_source" **
MERGE INTO people_target pt
USING people_source ps
ON    (pt.person_id = ps.person_id)
      WHEN MATCHED AND pt.title  = 'Mrs.' THEN
        DELETE
      WHEN MATCHED THEN
        UPDATE SET
          pt.first_name = ps.first_name,
               pt.last_name = ps.last_name,
               pt.title = ps.title
      WHEN NOT MATCHED AND ps.title = 'Mr' THEN
        INSERT
        (pt.person_id, pt.first_name, pt.last_name, pt.title)
        VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

 SELECT
SUM(C_BIRTH_DAY)
OVER (
    ORDER BY C_BIRTH_COUNTRY
    ROWS UNBOUNDED PRECEDING) AS MAX1
FROM WINDOW_TABLE;

 SELECT
SUM(C_BIRTH_DAY)
OVER (
    ORDER BY C_BIRTH_COUNTRY ROWS UNBOUNDED PRECEDING /*** SSC-FDM-OR0019 - WINDOW FRAME OUTPUT MAY NOT BE EQUIVALENT ***/) AS MAX1
FROM
WINDOW_TABLE;

 CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_SAMPLE AUTHID DEFINER IS
  NEW_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
  NEW_EXCEPTION2 EXCEPTION;
  PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 );
BEGIN

  IF true THEN
    RAISE NEW_EXCEPTION;
  END IF;

EXCEPTION
    WHEN NEW_EXCEPTION THEN
        --Handle Exceptions
        NULL;
END;
/

 CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_SAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/!!!
AS
$$
  DECLARE
    --** SSC-FDM-OR0023 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS **
    NEW_EXCEPTION EXCEPTION;
    --** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
    PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
    NEW_EXCEPTION2 EXCEPTION (-20100, '');
    --** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
  PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 );
  BEGIN
    IF (true) THEN
      RAISE NEW_EXCEPTION;
    END IF;
    EXCEPTION
        WHEN NEW_EXCEPTION THEN
            --Handle Exceptions
            NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE p1
AS
BEGIN
FOR i NUMBER(5,1) IN 1.2 .. 5.7 LOOP
    NULL;
END LOOP;
END;

 CREATE OR REPLACE PROCEDURE p1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-FDM-OR0021 - FOR LOOP WITH FLOAT NUMBER AS LOWER OR UPPER BOUND MAY NOT BEHAVE CORRECTLY IN SNOWFLAKE SCRIPTING **
        FOR i IN 1.2 TO 5.7 LOOP
            NULL;
        END LOOP;
    END;
$$;

 CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
    NULL;
END LOOP; 
END;

 CREATE OR REPLACE PROCEDURE P3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-FDM-OR0022 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
        FOR i IN REVERSE 1 TO 3 LOOP
            NULL;
        END LOOP;
    END;
$$;

 CREATE OR REPLACE PROCEDURE procedure_exception
IS
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception, -19000 );
BEGIN
    NULL; 
END;

CREATE OR REPLACE PROCEDURE procedure_exception ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-FDM-OR0023 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS **
        my_exception EXCEPTION;
        --** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
        PRAGMA EXCEPTION_INIT ( my_exception, -19000 );
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE record_unknown_table_proc
AS
    unknownTable_variable_rowtype unknownTable%ROWTYPE;
BEGIN
    INSERT INTO MyTable values unknownTable_variable_rowtype;
END;

 CREATE OR REPLACE PROCEDURE record_unknown_table_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        unknownTable_variable_rowtype OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
    BEGIN
        INSERT INTO MyTable
        SELECT
            null /*** SSC-FDM-OR0024 - COLUMNS FROM EXPRESSION unknownTable%ROWTYPE NOT FOUND ***/;
    END;
$$;

 CREATE OR REPLACE PROCEDURE PROC04
IS
 var3 FLOAT NOT NULL := 100;
BEGIN
NULL;
END;

 CREATE OR REPLACE PROCEDURE PROC04 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  var3 FLOAT := 100 /*** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
 BEGIN
  NULL;
 END;
$$;

 select cast(' $123.45' as number, 'L999.99') from dual;

 select
--** SSC-FDM-OR0026 - CAST TYPE NOT SUPPORTED **
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0011 - THE FORMAT PARAMETER ' $123.45' IS NOT SUPPORTED ***/!!!
 cast(' $123.45' as NUMBER(38, 18) , 'L999.99') from dual;

 SELECT TO_NUMBER('2,00' DEFAULT 0 ON CONVERSION ERROR) "Value" FROM DUAL;

 SELECT
--** SSC-FDM-OR0027 - DEFAULT ON CONVERSION ERROR NOT SUPPORTED IN SNOWFLAKE IN SNOWFLAKE **
TO_NUMBER('2,00') "Value" FROM DUAL;

 SELECT SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;

 SELECT
--** SSC-FDM-OR0028 - 'NLS_SORT' SYS_CONTEXT PARAMETER NOT SUPPORTED IN SNOWFLAKE **
SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;

 ALTER SESSION SET SQL_TRACE TRUE;

 ----** SSC-FDM-OR0029 - THIS ALTER SESSION CONFIGURATION IS NOT SUPPORTED IN SNOWFLAKE **
--ALTER SESSION SET SQL_TRACE TRUE
                                ;

 SELECT ROWID FROM T1;

 SELECT
--** SSC-FDM-OR0030 - ROWID PSEUDOCOLUMN IS NOT SUPPORTED IN SNOWFLAKE, IT WAS CONVERTED TO NULL TO AVOID RUNTIME ERRORS **
'' AS ROWID
FROM
T1;

 MERGE INTO people_target pt 
USING people_source ps ON (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title
LOG ERRORS;

 MERGE INTO people_target pt
USING people_source ps ON (pt.person_id = ps.person_id)
  WHEN MATCHED THEN
    UPDATE
    SET pt.first_name = ps.first_name,
        pt.last_name = ps.last_name,
        pt.title = ps.title
--  --** SSC-FDM-OR0031 - THE ERROR LOGGING CLAUSE IN DML STATEMENTS IS NOT SUPPORTED BY SNOWFLAKE **
--LOG ERRORS
          ;

 SELECT STANDARD_HASH(1+1) FROM DUAL;

SELECT
--** SSC-FDM-OR0032 - STANDARD HASH FUNCTION WITH INPUT NON-STRING PARAMETER GENERATES A DIFFERENT RESULT IN SNOWFLAKE **
SHA1(1+1)
FROM DUAL;

 CREATE OR REPLACE PROCEDURE built_in_package_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
    BEGIN
        SELECT
            --** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
            DBMS_RANDOM.VALUE_UDF() INTO
            :var1
        FROM DUAL;

        SELECT
            --** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
            DBMS_RANDOM.VALUE_UDF(2,10) INTO
            :var1
        FROM DUAL;
    END;
$$;

 CREATE SEQUENCE SEQUENCE1
  START WITH LIMIT VALUE;

 CREATE OR REPLACE SEQUENCE SEQUENCE1
  --** SSC-FDM-OR0034 - SEQUENCE START VALUE WITH 'LIMIT VALUE' IS NOT SUPPORTED BY SNOWFLAKE. **
  START WITH LIMIT VALUE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

 CREATE OR REPLACE PROCEDURE builtin_package_call
IS
BEGIN
	DBMS_OUTPUT.PUT_LINE(1);
	DBMS_OUTPUT.PUT_LINE("Test");
END;

 CREATE OR REPLACE PROCEDURE builtin_package_call ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
		CALL DBMS_OUTPUT.PUT_LINE_UDF(1);
		--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
		CALL DBMS_OUTPUT.PUT_LINE_UDF("Test");
	END;
$$;

 CREATE OR REPLACE PROCEDURE built_in_package_proc
IS
w_file UTL_FILE.FILE_TYPE;
BEGIN
    w_file:= UTL_FILE.FOPEN('MY_DIR','test.txt','W',32760);
    UTL_FILE.PUT_LINE(w_file,'New line');    
END;

 CREATE OR REPLACE PROCEDURE built_in_package_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
    BEGIN
        --** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
        CALL UTL_FILE.FOPEN_UDF('test.txt', 'W');
        SELECT
            *
        INTO
            w_file
        FROM
            TABLE(RESULT_SCAN(LAST_QUERY_ID()));
        --** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
        CALL UTL_FILE.PUT_LINE_UDF(:w_file, 'New line');
    END;
$$;

 SELECT * FROM TABLE1 EXTERNAL MODIFY (LOCATION 'file.csv' REJECT LIMIT UNLIMITED);

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **
SELECT * FROM
TABLE1
--       --** SSC-FDM-OR0037 - THE 'OPTIONAL MODIFIED EXTERNAL' SYNTAX IN SELECT IS NOT SUPPORTED IN SNOWFLAKE **
--       EXTERNAL MODIFY (LOCATION 'file.csv' REJECT LIMIT UNLIMITED)
                                                                   ;

 CREATE OR REPLACE PROCEDURE cursor_attributes_proc
IS
    is_open_attr BOOLEAN;
    found_attr BOOLEAN;
    my_record table1%ROWTYPE;
    CURSOR my_cursor IS SELECT * FROM table1;
BEGIN
    OPEN my_cursor;
    LOOP
        FETCH my_cursor INTO my_record;
        EXIT WHEN my_cursor%NOTFOUND;
        is_open_attr := my_cursor%ISOPEN;
        found_attr := my_cursor%FOUND;
    END LOOP;
    CLOSE my_cursor;
END;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "table1" **
CREATE OR REPLACE PROCEDURE cursor_attributes_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        is_open_attr BOOLEAN;
        found_attr BOOLEAN;
        my_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
        my_cursor CURSOR
        FOR
            SELECT
                OBJECT_CONSTRUCT( *) sc_cursor_record FROM
                table1;
    BEGIN
        OPEN my_cursor;
        LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
            FETCH my_cursor INTO
                :my_record;
            IF (my_record IS NULL) THEN
                EXIT;
            END IF;
            is_open_attr := null /*my_cursor%ISOPEN*/ /*** SSC-FDM-OR0038 - BOOLEAN CURSOR ATTRIBUTE %ISOPEN IS NOT SUPPORTED IN SNOWFLAKE ***/;
            found_attr := my_record IS NOT NULL;
        END LOOP;
        CLOSE my_cursor;
    END;
$$;

 CREATE TYPE type6 UNDER type5(COL1 INTEGER);

 ----** SSC-FDM-OR0039 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE **
--CREATE TYPE type6 UNDER type5(COL1 INTEGER)
                                           ;

 ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

 ----** SSC-FDM-OR0040 - NUMERIC CHARACTERS CANNOT BE ALTERED IN SNOWFLAKE. THE DECIMAL SEPARATOR IN SNOWFLAKE IS THE DOT CHARACTER. **
--ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
                                               ;

 SELECT
UTL_RAW.CAST_TO_RAW('some magic here'),
DBMS_UTILITY.GET_TIME
FROM DUAL;

 SELECT
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_RAW' IS NOT CURRENTLY SUPPORTED. **
'' AS CAST_TO_RAW,
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_UTILITY.GET_TIME' IS NOT CURRENTLY SUPPORTED. **
'' AS GET_TIME
FROM DUAL;

 CREATE TABLE "PUBLIC"."TABLE1"
(
    "CREATED_DATE" DATE,
    "UPDATED_DATE" DATE
);

 CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
    (
        "CREATED_DATE" TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        "UPDATED_DATE" TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

 -- Additional Params: --disableDateAsTimestamp
CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
    (
        "CREATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        "UPDATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

 SELECT TO_CLOB('Lorem ipsum dolor sit amet') FROM DUAL;

 SELECT
--** SSC-FDM-OR0043 - BFILE/BLOB PARAMETERS ARE CONSIDERED BINARY, FORMAT MAY BE NEEDED. **
TO_VARCHAR('Lorem ipsum dolor sit amet')
FROM DUAL;

 SELECT last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;

 SELECT last_name
FROM
hr.employees
WHERE
--** SSC-FDM-OR0044 - REGEXP_LIKE_UDF MATCH PARAMETER MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
PUBLIC.REGEXP_LIKE_UDF(last_name, '([aeiou])\\1', 'i')
ORDER BY last_name;

 SELECT SYSTIMESTAMP FROM DUAL;

 SELECT
CURRENT_TIMESTAMP() /*** SSC-FDM-OR0047 - YOU MAY NEED TO SET TIMESTAMP OUTPUT FORMAT ('DD-MON-YY HH24.MI.SS.FF AM TZH:TZM') ***/
FROM DUAL;

 SELECT * FROM TABLITA PARTITION(col1);

 SELECT * FROM
TABLITA
--        --** SSC-FDM-OR0045 - PARTITIONS CLAUSES ARE HANDLED BY SNOWFLAKE **
--        PARTITION(col1)
                       ;

 SELECT * FROM LATERAL(SELECT * FROM TABLITA WITH READ ONLY CONSTRAINT T);

 SELECT * FROM LATERAL(SELECT * FROM
TABLITA
--        --** SSC-FDM-OR0046 - THE SUBQUERY RESTRICTION IS NOT POSSIBLE IN SNOWFLAKE **
--        WITH READ ONLY CONSTRAINT T
                                   );