Code Conversion - 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;

 [ RELY | NORELY | RELY DISABLE | RELY ENABLE | VALIDATE | NOVALIDATE ]

 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 AI 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;

 !!!RESOLVE EWI!!! /*** SSC-EWI-0068 - 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": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/02/2025",  "domain": "no-domain-provided" }}'
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;
$$;

 ALTER SESSION SET AUTOCOMMIT = false;

 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()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        var1 VARCHAR DEFAULT '';
        var2 VARCHAR DEFAULT '';
        var3 VARCHAR DEFAULT '';
    BEGIN
        --Loop 1
        FOR i IN 1.2 TO 5.2 DO
            var1 := var1 || ' ' || i::VARCHAR;
        END FOR;
        
        --Loop 2
        FOR i IN 1.7 TO 5.5 DO
            var2 := var2 || ' ' || i::VARCHAR;
        END FOR;
        
        --Loop 3
        FOR i IN 1.5 TO 5.8 DO
            var3 := var3 || ' ' || i::VARCHAR;
        END FOR;
        RETURN  ' Loop1: ' || var1 ||
                ' Loop2: ' || var2 ||
                ' Loop3: ' || var3;
    END;
$$;

CALL p1();

 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": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/02/2025",  "domain": "no-domain-provided" }}'
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 **
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN 1.2 TO 5.7
                            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                            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
IS
var1 NUMBER;
BEGIN
    SELECT DBMS_RANDOM.VALUE() INTO var1 FROM DUAL;

    SELECT DBMS_RANDOM.VALUE(2,10) INTO var1 FROM DUAL; 
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
        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 * 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
                                   );

 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;

 CREATE OR REPLACE TABLE orders (
   order_id INT,
   created_date VARCHAR(30) DEFAULT TO_VARCHAR(TRUNC(CURRENT_TIMESTAMP(), 'DD')) /*** SSC-FDM-OR0048 - TRANSFORMATION OF DATE/TIMESTAMP TO VARCHAR DEPENDS ON THE OUTPUT_FORMAT SESSION PARAMETERS, SET THEM TO MATCH THE ORACLE VALUES ***/
);

 SELECT 
    LAG(salary, 2, '0') OVER (ORDER BY salary) AS salary_two_steps_back
FROM 
    employees;

 SELECT
    --** SSC-FDM-OR0049 - LAG FUNCTION MIGHT FAIL IF DEFAULT VALUE TYPE DIFFERS FROM THE EXPRESSION TYPE. **
    LAG(salary, 2, '0')
    OVER (ORDER BY salary) AS salary_two_steps_back
FROM
    employees;

CREATE OR REPLACE PROCEDURE calculate_division_with_nocopy (
    p_numerator IN NUMBER,
    p_denominator IN NUMBER,
    p_result OUT NOCOPY NUMBER
)
IS
    PROCEDURE calculate_division(result OUT NOCOPY NUMBER)
    AS
    BEGIN
    result := 20;
    result := p_numerator / p_denominator;
    END calculate_division;
BEGIN
    calculate_division(p_result);
        EXCEPTION
        WHEN OTHERS THEN
            p_result := p_result;
END calculate_division_with_nocopy;

CREATE OR REPLACE PROCEDURE calculate_division_with_nocopy (p_numerator NUMBER(38, 18), p_denominator NUMBER(38, 18), p_result OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/23/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        calculate_division PROCEDURE(result
        --** SSC-FDM-OR0050 - EXCEPTIONS WITH NOCOPY PARAMETERS MAY LEAD TO DATA INCONSISTENCY. **
        NUMBER(38, 18))
        RETURNS NUMBER
        AS
            BEGIN
                result := 20;
                result := :p_numerator / :p_denominator;
                RETURN result;
            END;
        call_results NUMBER;
    BEGIN
        call_results := (
            CALL
            calculate_division(:p_result)
        );
        p_result := :call_results;
        EXCEPTION
        WHEN OTHER THEN
            p_result := :p_result;
        END;
$$;

CREATE TYPE PhoneNumbers AS VARRAY(10) OF VARCHAR2(20);

--** SSC-FDM-OR0051 - ARRAY SIZE LIMIT '10' WAS REMOVED. SNOWFLAKE ARRAYS ARE DYNAMICALLY SIZED. **
CREATE TYPE PhoneNumbers AS ARRAY ( VARCHAR(20) );

CREATE TABLE test_table (raw_col RAW(16), varchar_col VARCHAR2(100));

SELECT TRIM(raw_col) FROM test_table;

CREATE OR REPLACE TABLE test_table (
  raw_col BINARY,
  varchar_col VARCHAR(100)
)
;

SELECT
--** SSC-FDM-OR0055 - TRIM/LTRIM/RTRIM IS NOT SUPPORTED ON BINARY DATA TYPES IN SNOWFLAKE. THE ARGUMENT HAS BEEN WRAPPED WITH TO_VARCHAR/TO_BINARY TO PRESERVE FUNCTIONALITY. **
  TO_BINARY(NULLIF(TRIM(TO_VARCHAR(raw_col, 'HEX')), ''), 'HEX')
FROM
  test_table;

SELECT TRIM(LEADING '0' FROM raw_col) FROM test_table;

SELECT
--** SSC-FDM-OR0055 - TRIM/LTRIM/RTRIM IS NOT SUPPORTED ON BINARY DATA TYPES IN SNOWFLAKE. THE ARGUMENT HAS BEEN WRAPPED WITH TO_VARCHAR/TO_BINARY TO PRESERVE FUNCTIONALITY. **
  TO_BINARY(NULLIF(LTRIM(TO_VARCHAR(raw_col, 'HEX'), '0'), ''), 'HEX')
FROM
  test_table;

SELECT LTRIM(raw_col) FROM test_table;

SELECT
--** SSC-FDM-OR0055 - TRIM/LTRIM/RTRIM IS NOT SUPPORTED ON BINARY DATA TYPES IN SNOWFLAKE. THE ARGUMENT HAS BEEN WRAPPED WITH TO_VARCHAR/TO_BINARY TO PRESERVE FUNCTIONALITY. **
  TO_BINARY(NULLIF(LTRIM(TO_VARCHAR(raw_col, 'HEX')), ''), 'HEX')
FROM
  test_table;

        CREATE OR REPLACE PACKAGE BODY schema_test.pkg_test AS
            PROCEDURE process_employees IS
            BEGIN
                FOR rec IN pkg_test.c_emp_above_salary_70k LOOP
                    DBMS_OUTPUT.PUT_LINE(rec.emp_name);
                END LOOP;
            END process_employees;
        END pkg_test;

        CREATE OR REPLACE PROCEDURE SCHEMA_TEST_PKG_TEST.process_employees ()
        RETURNS VARCHAR
        LANGUAGE SQL
        EXECUTE AS CALLER
        AS
        $$
          BEGIN
            LET "SCHEMA_TEST_PKG_TEST.C_EMP_ABOVE_SALARY_70K.for_1" RESULTSET;
            "SCHEMA_TEST_PKG_TEST.C_EMP_ABOVE_SALARY_70K.for_1" := (
              EXECUTE IMMEDIATE PACKAGE_CURSOR.GET_CURSOR_DEFINITION('"SCHEMA_TEST_PKG_TEST.C_EMP_ABOVE_SALARY_70K"')
            );
            --** SSC-FDM-OR0056 - ORACLE PACKAGE-LEVEL CURSOR 'C_EMP_ABOVE_SALARY_70K' IS BOUNDED TO THE FOR...IN LOOP BLOCK (OPENS ON ENTRY, CLOSES ON EXIT), SO IT BEHAVES AS A LOCAL CURSOR AND WAS CONVERTED ACCORDINGLY. **
            FOR rec IN "SCHEMA_TEST_PKG_TEST.C_EMP_ABOVE_SALARY_70K.for_1" DO
              CALL DBMS_OUTPUT.PUT_LINE(rec.emp_name);
            END FOR;
          END;
        $$;

        CREATE OR REPLACE PACKAGE BODY schema_test.pkg_test AS
            CURSOR c_emp_by_dept (p_dept VARCHAR2) IS
                SELECT emp_id FROM employees WHERE dept = p_dept;
            PROCEDURE process_eng IS
            BEGIN
                OPEN c_emp_by_dept(p_dept => 'ENG');
            END process_eng;
        END pkg_test;

        CALL PACKAGE_CURSOR.CREATE_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP_BY_DEPT"', 'SELECT emp_id FROM schema_test.employees WHERE dept = @@p_dept@@', 'SELECT emp_id, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS _row_num FROM schema_test.employees WHERE dept = @@p_dept@@');

        CREATE OR REPLACE PROCEDURE SCHEMA_TEST_PKG_TEST.process_eng ()
        RETURNS VARCHAR
        LANGUAGE SQL
        EXECUTE AS CALLER
        AS
        $$
          BEGIN
            --** SSC-FDM-OR0057 - CHECK UDF IMPLEMENTATION FOR PACKAGE_CURSOR.OPEN_CURSOR. **
            CALL PACKAGE_CURSOR.OPEN_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP_BY_DEPT"', OBJECT_CONSTRUCT('p_dept', 'ENG'));
          END;
        $$;

SELECT COLLECT(DISTINCT cust_last_name ORDER BY cust_id) FROM dual;

SELECT ARRAY_AGG(DISTINCT cust_last_name)
  /*** SSC-FDM-OR0059 - SNOWFLAKE DOES NOT SUPPORT ORDERING A DISTINCT ARRAY AGGREGATION BY A NON-AGGREGATED EXPRESSION; THE ORDER BY WAS DROPPED. ***/
FROM dual;

SELECT COLLECT(cust_last_name ORDER BY cust_id) FROM dual;

SELECT ARRAY_AGG(cust_last_name) WITHIN GROUP (ORDER BY cust_id)
  /*** SSC-FDM-OR0060 - NULL VALUES ARE EXCLUDED BY SNOWFLAKE'S ARRAY_AGG, WHEREAS ORACLE COLLECT RETAINS THEM. ***/
FROM dual;

CREATE OR REPLACE PROCEDURE xmlstore_proc
IS
    l_ctx NUMBER;
BEGIN
    DBMS_XMLSTORE.setUpdateColumn(l_ctx, 'PROJ_ID');
    DBMS_XMLSTORE.setRowTag(l_ctx, 'XMLDATA');
    DBMS_XMLSTORE.closeContext(l_ctx);
END;

CREATE OR REPLACE PROCEDURE xmlstore_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    l_ctx NUMBER(38, 18);
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_XMLSTORE.setUpdateColumn' IS NOT CURRENTLY SUPPORTED. ***/!!!
    DBMS_XMLSTORE.setUpdateColumn(:l_ctx, 'PROJ_ID');
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_XMLSTORE.setRowTag' IS NOT CURRENTLY SUPPORTED. ***/!!!
    DBMS_XMLSTORE.setRowTag(:l_ctx, 'XMLDATA');
--    --** SSC-FDM-OR0061 - BUILT-IN PACKAGE 'DBMS_XMLSTORE.closeContext' IS NOT REQUIRED IN SNOWFLAKE AND WAS REMOVED. **
--    DBMS_XMLSTORE.closeContext(:l_ctx)
  END;
$$;

SELECT XMLTYPE('<root><val>1</val></root>') FROM DUAL;

SELECT
--** SSC-FDM-OR0062 - XMLTYPE PARSED AS A VARIANT VIA PARSE_XML. RESULTS MAY DIFFER FOR DOWNSTREAM XMLTYPE METHOD CALLS. **
  PARSE_XML('<root><val>1</val></root>')
FROM DUAL;

SELECT EXTRACTVALUE(xml_col, '/root/val') FROM t;

SELECT
--** SSC-FDM-OR0063 - EXTRACTVALUE TRANSLATED TO GET/XMLGET FOR A PLAIN ELEMENT PATH. NAMESPACES, AXES AND PREDICATES ARE NOT SUPPORTED. **
  GET(XMLGET(PARSE_XML(TO_VARCHAR(xml_col)), 'val'), '$')::STRING
FROM t;

SELECT XMLELEMENT("EmpName", emp_name) FROM employees;

SELECT
--** SSC-FDM-OR0064 - XMLELEMENT TRANSLATED TO STRING CONCATENATION. SPECIAL CHARACTERS IN VALUES MAY PRODUCE INVALID XML. **
  '<EmpName>' || COALESCE(emp_name::STRING, '') || '</EmpName>'
FROM employees;

SELECT XMLFOREST(emp_name AS "Name", salary AS "Salary") FROM employees;

SELECT
--** SSC-FDM-OR0065 - XMLFOREST TRANSLATED TO STRING CONCATENATION. NULL COLUMNS ARE OMITTED FROM OUTPUT (ORACLE BEHAVIOR PRESERVED). SPECIAL CHARACTERS IN VALUES MAY PRODUCE INVALID XML. **
  IFF(emp_name IS NULL, '', '<Name>' || emp_name || '</Name>')
  || IFF(salary IS NULL, '', '<Salary>' || salary || '</Salary>')
FROM employees;

SELECT XMLAGG(xml_col ORDER BY id) FROM t;

SELECT
--** SSC-FDM-OR0066 - XMLAGG TRANSLATED TO LISTAGG. RESULT IS CAPPED AT 16MB. **
  LISTAGG(TO_VARCHAR(xml_col), '') WITHIN GROUP (ORDER BY id)
FROM t;

SELECT
   trim(COLUMN_VALUE) currency
FROM
   my_table,
   xmltable(('"' || REPLACE(my_table.currency, ',', '","') || '"'));

SELECT
  NULLIF(trim(COLUMN_VALUE), '') currency
FROM
  my_table,
  --** SSC-FDM-OR0067 - STRTOK_SPLIT_TO_TABLE DOES NOT XML-ENCODE SPECIAL CHARACTERS OR GUARANTEE ROW ORDER (USE ORDER BY INDEX), UNLIKE ORACLE'S XMLTABLE. **
  TABLE(STRTOK_SPLIT_TO_TABLE(my_table.currency, ',')) AS COLUMN_VALUE_TABLE (
    SEQ,
    INDEX,
    COLUMN_VALUE
  );

SELECT
   COLUMN_VALUE
FROM
   my_table,
   xmltable(('"' || REPLACE(my_table.currency, ', ', '", "') || '"'));

SELECT
  COLUMN_VALUE
FROM
  my_table,
  --** SSC-FDM-OR0068 - SPLIT_TO_TABLE KEEPS EMPTY TOKENS, DOES NOT XML-ENCODE SPECIAL CHARACTERS, AND DOES NOT GUARANTEE ROW ORDER (USE ORDER BY INDEX), UNLIKE ORACLE'S XMLTABLE. **
  TABLE(SPLIT_TO_TABLE(my_table.currency, ', ')) AS COLUMN_VALUE_TABLE (
    SEQ,
    INDEX,
    COLUMN_VALUE
  );

CREATE OR REPLACE PROCEDURE RowtypeTypeProc
AS
	record_rowtype_variable record_table%ROWTYPE;
BEGIN
	record_rowtype_variable := null;
END;

CREATE OR REPLACE PROCEDURE RowtypeTypeProc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		--** SSC-FDM-OR0069 - %ROWTYPE VARIABLE REPRESENTED AS OBJECT. FIELD ASSIGNMENTS EMIT OBJECT_INSERT; FIELD READS EMIT SEMI-STRUCTURED ACCESS. **
		record_rowtype_variable OBJECT := OBJECT_CONSTRUCT();
	BEGIN
		record_rowtype_variable := null;
	END;
$$;

CREATE OR REPLACE PROCEDURE record_unknown_table_proc
AS
    unknownTable_variable_rowtype unknownTable%ROWTYPE;
BEGIN
    unknownTable_variable_rowtype.binds_html_table_capt := NULL;
    INSERT INTO MyTable values unknownTable_variable_rowtype;
END;

CREATE OR REPLACE PROCEDURE record_unknown_table_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-FDM-OR0069 - %ROWTYPE VARIABLE REPRESENTED AS OBJECT. FIELD ASSIGNMENTS EMIT OBJECT_INSERT; FIELD READS EMIT SEMI-STRUCTURED ACCESS. **
        unknownTable_variable_rowtype OBJECT := OBJECT_CONSTRUCT();
    BEGIN
        unknownTable_variable_rowtype := OBJECT_INSERT(unknownTable_variable_rowtype, 'BINDS_HTML_TABLE_CAPT', NULL, true);
        INSERT INTO MyTable
        --** SSC-FDM-OR0070 - INSERT COLUMN LIST INFERRED FROM %ROWTYPE FIELD ASSIGNMENTS BECAUSE THE TARGET TABLE SCHEMA COULD NOT BE RESOLVED. VERIFY THE INFERRED COLUMN ORDERING MATCHES THE TARGET TABLE. **
        SELECT
            :unknownTable_variable_rowtype:BINDS_HTML_TABLE_CAPT;
    END;
$$;

CREATE OR REPLACE PROCEDURE set_id_literal_proc
IS
BEGIN
    DBMS_SESSION.SET_IDENTIFIER('my-app-user');
END;

CREATE OR REPLACE PROCEDURE set_id_literal_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    --** SSC-FDM-OR0071 - TRANSLATED 'DBMS_SESSION.SET_IDENTIFIER' TO 'ALTER SESSION SET QUERY_TAG'. ORACLE'S SESSION CLIENT IDENTIFIER FEEDS VPD/RLS POLICIES AND AUDITING — THE DROPPED VALUE IS NOT RECOVERABLE IN SNOWFLAKE. **
    ALTER SESSION SET QUERY_TAG = 'my-app-user';
  END;
$$;

               CREATE OR REPLACE PROCEDURE p_strong
               IS
                   TYPE rc IS REF CURSOR RETURN cursortable1%ROWTYPE;
                   c rc;
               BEGIN
                   OPEN c FOR SELECT col1 FROM cursortable1;
                   CLOSE c;
               END;

                   CREATE OR REPLACE PROCEDURE p_strong ()
                   RETURNS VARCHAR
                   LANGUAGE SQL
                   EXECUTE AS CALLER
                   AS
                   $$
                     DECLARE
                   --    --** SSC-FDM-OR0072 - REF CURSOR TYPE DEFINITION 'PL REF CURSOR TYPE DEFINITION' IS INLINED. REF CURSOR VARIABLES OF THIS TYPE ARE TRANSFORMED TO RESULTSET AND THEIR OPEN FOR/FETCH/CLOSE USAGE IS HANDLED BY OTHER RULES, SO THE TYPE DEFINITION LINE ITSELF IS COMMENTED OUT. **
                   --    TYPE rc IS REF CURSOR RETURN cursortable1%ROWTYPE;
                       c_res RESULTSET;
                     BEGIN
                       LET c CURSOR
                       FOR
                         SELECT
                           col1
                         FROM
                           cursortable1;
                       OPEN c;
                       CLOSE c;
                     END;
                   $$;

CREATE OR REPLACE PROCEDURE SC3973837_HANDLER_READS_SQLCODE (p_table IN varchar2, p_msg OUT varchar2) as
  NO_SUCH_TABLE exception;
  pragma exception_init (NO_SUCH_TABLE, -942);
begin
  execute immediate 'drop table "' || upper(p_table) || '"';
  p_msg := 'OK';
exception
  when NO_SUCH_TABLE then
    p_msg := 'ERR ' || to_char(SQLCODE) || ' ' || substr(SQLERRM, 1, 40);
end;

CREATE OR REPLACE PROCEDURE SC3973837_HANDLER_READS_SQLCODE (p_table VARCHAR, p_msg OUT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER IS OUTSIDE THE SNOWFLAKE SCRIPTING RANGE (-20998 TO -20001). REMAP IT INTO THAT RANGE IF YOU CONTROL ALL REFERENCES, OR USE 'WHEN OTHER' WITH A SQLCODE CHECK. ***/!!!
    NO_SUCH_TABLE EXCEPTION;
--    --** SSC-FDM-OR0073 - PRAGMA EXCEPTION_INIT 'NO_SUCH_TABLE' IS INLINED: THE NEW EXCEPTION WILL ONLY CATCH ERRORS RAISED THROUGH THAT NAME - NOT OTHER ERRORS THAT SHARE THE SAME ORACLE ERROR NUMBER. **
--    pragma exception_init(NO_SUCH_TABLE, -942);
  BEGIN
    EXECUTE IMMEDIATE 'drop table "' || NVL(NVL(upper(p_table) :: STRING, '') :: STRING, '') || '"';
    p_msg := 'OK';
  exception
    when NO_SUCH_TABLE then
      p_msg := 'ERR ' || NVL(to_char(SQLCODE) /*** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR SQLCODE. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/ :: STRING, '') || ' ' || NVL(substr(SQLERRM, 1, 40) :: STRING, '');
  END;
$$;

CREATE OR REPLACE PROCEDURE p (vjson IN CLOB)
IS
    jsonobj  pljson;
    vd       pljson;
    vtext    VARCHAR2(4000);
    vsize    NUMBER;
BEGIN
    jsonobj := pljson(vjson);
    vd := jsonobj.get('d');
    vtext := jsonobj.to_char();
    vsize := jsonobj.count;
END;

CREATE OR REPLACE PROCEDURE p (vjson VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    jsonobj VARIANT;
    vd VARIANT;
    vtext VARCHAR(4000);
    vsize NUMBER(38, 18);
  BEGIN
    jsonobj := PARSE_JSON(:vjson);
    vd := :jsonobj:"d";
    vtext := TO_JSON(:jsonobj) /*** SSC-FDM-OR0074 - PLJSON TO_CHAR TRANSLATED TO TO_JSON. SNOWFLAKE SERIALIZES OBJECT KEYS ALPHABETICALLY AND WITHOUT PRETTY-PRINTING, SO THE OUTPUT STRING MAY DIFFER FROM ORACLE PL/JSON. ***/;
    vsize := ARRAY_SIZE(OBJECT_KEYS(:jsonobj));
  END;
$$;

CREATE OR REPLACE PROCEDURE p_loop
AS
TYPE num_tab IS TABLE OF NUMBER;
v_arr num_tab := num_tab(10, 20, 30);
v_x NUMBER;
BEGIN
  FOR i IN v_arr.FIRST..v_arr.LAST LOOP
    v_x := v_arr(i);
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE p_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
--    --** SSC-FDM-OR0053 - EMBEDDED COLLECTION TYPE DEFINITION 'PL COLLECTION TYPE DEFINITION' WAS REMOVED. COLLECTION VARIABLES ARE TRANSFORMED TO ARRAY BY OTHER RULES. **
--    TYPE num_tab IS TABLE OF NUMBER;
    v_arr ARRAY /*** SSC-FDM-OR0084 - COLLECTION TYPE 'num_tab' WAS MAPPED TO A SNOWFLAKE ARRAY; ELEMENT INDEXING WAS ADJUSTED FROM 1-BASED TO 0-BASED. ***/ := ARRAY_CONSTRUCT(10, 20, 30);
    v_x NUMBER(38, 18);
  BEGIN
    --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    FOR i IN 1 /*** SSC-FDM-OR0075 - COLLECTION 'FIRST' WAS TRANSLATED ASSUMING A DENSE ARRAY. ORACLE 'FIRST' RETURNS NULL FOR AN EMPTY COLLECTION WHILE THE SNOWFLAKE EQUIVALENT DOES NOT. ***/ TO ARRAY_SIZE(:v_arr) /*** SSC-FDM-OR0075 - COLLECTION 'LAST' WAS TRANSLATED ASSUMING A DENSE ARRAY. ORACLE 'LAST' RETURNS NULL FOR AN EMPTY COLLECTION WHILE THE SNOWFLAKE EQUIVALENT DOES NOT. ***/
                                                                                                                                                                                                                                                                                                                                                                                                       --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                                                                                                                                                                                                                                                                                                                                                                                       LOOP
                                                                                                                                                                                                                                                                                                                                                                                                         v_x := :v_arr[:i - 1];
                                                                                                                                                                                                                                                                                                                                                                                                       END LOOP;
  END;
$$;

SELECT DBMS_UTILITY.FORMAT_ERROR_STACK() FROM DUAL;

SELECT
SQLERRM /*** SSC-FDM-OR0076 - 'FORMAT_ERROR_STACK' WAS TRANSLATED TO SQLERRM, WHICH RETURNS ONLY THE TOP-MOST ERROR MESSAGE; ANY CHAINED MESSAGES AND ORA-06512 BACKTRACE LINES FROM THE ORACLE ERROR STACK ARE DROPPED. ***/
FROM
DUAL;

DECLARE
  v DBMS_SQL.VARCHAR2A;
  n NUMBER;
BEGIN
  SELECT COUNT(*) INTO n FROM TABLE(v);
END;