SnowConvert AI - Oracle 문제¶

 CREATE SEQUENCE SEQUENCE1
  START WITH LIMIT VALUE;

 CREATE OR REPLACE SEQUENCE SEQUENCE1
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0001 - 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 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 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0002 - COLUMNS FROM EXPRESSION unknownTable%ROWTYPE NOT FOUND ***/!!!;
    END;
$$;

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

 SELECT * FROM
TABLE1
       !!!RESOLVE EWI!!! /*** SSC-EWI-OR0004 - THE 'OPTIONAL MODIFIED EXTERNAL' SYNTAX IN SELECT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
       EXTERNAL MODIFY (LOCATION 'file.csv' REJECT LIMIT UNLIMITED);

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

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

 SELECT SYSTIMESTAMP FROM DUAL;

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

 CREATE TYPE type6 UNDER type5(COL1 INTEGER);

 --!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
--CREATE TYPE type6 UNDER type5(COL1 INTEGER)
                                           ;

 SELECT TO_CHAR(DATE '1998-12-25','iw-iyyy') FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0008 - UNKNOWN FORMAT, MAY HAVE UNEXPECTED BEHAVIOR ***/!!!
 TO_CHAR(DATE '1998-12-25','iw-iyyy'') FROM DUAL;

 SELECT jt.phones
FROM j_purchaseorder,
JSON_TABLE(po_document, '$.ShippingInstructions'
COLUMNS
(phones VARCHAR2(100) FORMAT JSON PATH '$.Phone')) AS jt;

 SELECT jt.phones
FROM
j_purchaseorder,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0009 - JSON_TABLE IS NOT SUPPORTED ***/!!!
JSON_TABLE(po_document, '$.ShippingInstructions'
COLUMNS
(phones VARCHAR(100) FORMAT JSON PATH '$.Phone')) AS jt;

 SELECT * FROM table1 PARTITION(col1);

 SELECT * FROM
table1
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0010 - PARTITIONS CLAUSES ARE HANDLED BY SNOWFLAKE. IT REQUIRES MANUAL FIX ***/!!!
        PARTITION(col1);

 SELECT CAST('12.48' AS NUMBER, '99.99') FROM DUAL;

 SELECT
TO_NUMBER('12.48', '99.99', 38, 2)
FROM DUAL;

 SELECT CAST('FEBRUARY/18/24' as DATE, 'MONTH/DD/YY') FROM DUAL; 
SELECT CAST('FEB/MON/24' as DATE, 'MON/DAY/YY') FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0011 - THE FORMAT PARAMETER 'MONTH/DD/YY' IS NOT SUPPORTED ***/!!!
TO_TIMESTAMP ('FEBRUARY/18/24' , 'MONTH/DD/YY')
FROM DUAL;

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0011 - THE FORMAT PARAMETER 'MON/DAY/YY' IS NOT SUPPORTED ***/!!!
TO_TIMESTAMP ('FEB/MON/24' , 'MON/DAY/YY')
FROM DUAL;

 SELECT TO_NUMBER('-AusDollars100','9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.''NLS_CURRENCY= ''AusDollars''') "Amount" FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0013 - NLS PARAMETER ' NLS_NUMERIC_CHARACTERS = '',.''NLS_CURRENCY= ''AusDollars''' NOT SUPPORTED ***/!!!
TO_NUMBER('-AusDollars100', '9G999D99') "Amount" FROM DUAL;

 SELECT NLSSORT(name, 'NLS_SORT = ENGLISH') FROM products;

 // SnowConvert AI Helpers Code section is omitted.
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0014 - FUNCTION NLSSORT IS NOT SUPPORTED ***/!!!
 NLSSORT(name, 'NLS_SORT = ENGLISH') FROM
 products;

 select * from table(XMLSequence(XMLType('
<Product ProductCode="200">
 <BrandName>Notebook</BrandName>
 <ProductList>
  <Item ItemNo="200A"><Price>900</Price></Item>
  <Item ItemNo="200B"><Price>700</Price></Item>
  <Item ItemNo="200C"><Price>650</Price></Item>
  <Item ItemNo="200D"><<Price>750</Price></Item>
</ProductList>
</Product>')));

 select * from table(
                    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!
XMLSequence(
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!XMLType('
<Product ProductCode="200">
 <BrandName>Notebook</BrandName>
 <ProductList>
  <Item ItemNo="200A"><Price>900</Price></Item>
  <Item ItemNo="200B"><Price>700</Price></Item>
  <Item ItemNo="200C"><Price>650</Price></Item>
  <Item ItemNo="200D"><<Price>750</Price></Item>
</ProductList>
</Product>')));

 SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM DUAL;

 SELECT
REGEXP_INSTR('CORPORATE FLOOR','OR', -3, 2) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0020 - NEGATIVE VALUES NOT SUPPORTED FOR FUNCTION ***/!!! FROM DUAL;

 SELECT DENSE_RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM employees;

SELECT RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM employees;

SELECT PERCENT_RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM employees;

SELECT CUME_DIST(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM employees;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0023 - DENSE_RANK AGGREGATE FUNCTION SYNTAX IS NOT SUPPORTED BY SNOWFLAKE. ***/!!!
 DENSE_RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM
 employees;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0023 - RANK AGGREGATE FUNCTION SYNTAX IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM
 employees;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0023 - PERCENT_RANK AGGREGATE FUNCTION SYNTAX IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! PERCENT_RANK(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM
 employees;


SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0023 - CUME_DIST AGGREGATE FUNCTION SYNTAX IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! CUME_DIST(12000) WITHIN GROUP (ORDER BY salary DESC NULLS FIRST) FROM
 employees;

 SELECT QUERY_NAME.ROWID from TABLE1;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0026 - ROWID NOT SUPPORTED ***/!!!
 QUERY_NAME.ROWID from
 TABLE1;

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

 SELECT
PUBLIC.TO_NUMBER_UDF('2,00', 0) "Value" FROM DUAL;

 SELECT
    department_id,
    MIN(salary) KEEP (
        DENSE_RANK FIRST
        ORDER BY
            commission_pct
    ) "Worst"
FROM
    employees;

 SELECT
    department_id,
    MIN(salary)
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0030 - KEEP STATEMENT USED IN THE AGGREGATE FUNCTION IS NOT SUPPORTED ***/!!!
 KEEP (
        DENSE_RANK FIRST
        ORDER BY
            commission_pct
    ) "Worst"
FROM
 employees;

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

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0031 - 'NLS_SORT' SYS_CONTEXT PARAMETER NOT SUPPORTED IN SNOWFLAKE ***/!!!
 SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;

 SELECT TO_CHAR(DATE '1998-12-25', 'AM') FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0032 - PARAMETER USED IN THE FUNCTION 'TO_CHAR' WITH FORMAT AM IS NOT SUPPORTED ***/!!!
 TO_CHAR(DATE '1998-12-25', 'AM') FROM DUAL;

 WITH FUNCTION get_domain ( url VARCHAR2 ) RETURN VARCHAR2 IS pos BINARY_INTEGER;
len BINARY_INTEGER;
BEGIN
pos := INSTR(url, 'www.');
len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
END; SELECT aValue from aTable;

 // SnowConvert AI Helpers Code section is omitted.
WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0033 - PLDECLARATION IN WITH NOT SUPPORTED ***/!!!
 FUNCTION get_domain ( url VARCHAR2 ) RETURN VARCHAR2 IS pos BINARY_INTEGER;
len BINARY_INTEGER;
BEGIN
pos := INSTR(url, 'www.');
len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
END; SELECT aValue from
aTable;

 SELECT 
TABLE2.COLUMN_VALUES
FROM TABLE1 i, TABLE(i.groups) TABLE2;

 // SnowConvert AI Helpers Code section is omitted.
SELECT
TABLE2.COLUMN_VALUES
FROM
TABLE1 i,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0035 - TABLE FUNCTION IS NOT SUPPORTED WHEN IT IS USED AS A COLLECTION OF EXPRESSIONS ***/!!! TABLE(i.groups) TABLE2;

 SELECT
    SYSDATE,
    SYSDATE + '1',
    SYSDATE + 'A'
from
    dual;

 SELECT
    CURRENT_TIMESTAMP(),
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Date AND String ***/!!!
    CURRENT_TIMESTAMP() + '1',
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Date AND String ***/!!!
    CURRENT_TIMESTAMP() + 'A'
from
    dual;

 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
) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0038 - SEARCH CLAUSE REMOVED FROM THE WITH ELEMENT STATEMENT ***/!!!
SELECT aValue from
atable;

 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
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0039 - NOCYCLE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
CONNECT BY
      A.C = 0 START WITH A.B = 1;

 SELECT
   employee_id,
   salary
FROM
   employees
MODEL
DIMENSION BY (employee_id)
MEASURES (salary)
();

 SELECT
   employee_id,
   salary
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0042 - MODEL CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
MODEL
DIMENSION BY (employee_id)
MEASURES (salary)
();

 SELECT CAST(' $123.45' as number, 'L999.99') FROM DUAL;
SELECT CAST('$123.45' as number, 'FML999.99') FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!!
 CAST(' $123.45' as NUMBER(38, 18) , 'L999.99') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!! CAST('$123.45' as NUMBER(38, 18) , 'FML999.99') FROM DUAL;

 ALTER TABLE SOMENAME DEFAULT COLLATION SOMENAME;

ALTER TABLE SOMENAME ROW ARCHIVAL;

ALTER TABLE SOMENAME MODIFY CLUSTERING;

ALTER TABLE SOMENAME DROP CLUSTERING;

ALTER TABLE SOMENAME SHRINK SPACE COMPACT CASCADE;

 !!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **
ALTER TABLE SOMENAME
DEFAULT COLLATION SOMENAME;

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
ROW ARCHIVAL;

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
MODIFY CLUSTERING;

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
DROP CLUSTERING;

!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SOMENAME" **

ALTER TABLE SOMENAME
SHRINK SPACE COMPACT CASCADE;

 select TO_NCHAR(sysdate,'DY','nls_date_language=english') from dual

 select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0047 - TO_NCHAR TRANSFORMED TO TO_VARCHAR, IT MAY NOT BE COMPILABLE IN SNOWFLAKE ***/!!!
TO_VARCHAR(CURRENT_TIMESTAMP(),'DY','nls_date_language=english') from dual;

 SELECT TO_NCHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0047 - TO_NCHAR TRANSFORMED TO TO_VARCHAR, IT MAY NOT BE COMPILABLE IN SNOWFLAKE ***/!!!
TO_VARCHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD') FROM dual;

 CREATE OR REPLACE PACKAGE MY_PACKAGE
AS
    TYPE COLLECTIONTYPEDEFINITION IS TABLE OF BULKCOLLECTTABLE%ROWTYPE;
END;

 CREATE SCHEMA IF NOT EXISTS MY_PACKAGE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

!!!RESOLVE EWI!!! /*** SSC-EWI-OR0049 - PACKAGE TYPE DEFINITIONS in stateful package MY_PACKAGE are not supported yet ***/!!!
TYPE COLLECTIONTYPEDEFINITION IS TABLE OF BULKCOLLECTTABLE%ROWTYPE;

 SELECT CAST('123,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12.34567891234567891234567891234567891267+' AS NUMBER, '99.999999999999999999999999999999999999S') FROM DUAL;
SELECT CAST('12.34567891234567891234567891234567891267' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
select cast(' 1.0E+123' as number, '9.9EEEE') from dual;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '123,456E+40' ***/!!!
 CAST('123,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267+' ***/!!! CAST('12.34567891234567891234567891234567891267+' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999S') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267' ***/!!! CAST('12.34567891234567891234567891234567891267' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999') FROM DUAL;

select
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE ' 1.0E+123' ***/!!! cast(' 1.0E+123' as NUMBER(38, 18) , '9.9EEEE') from dual;

 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
AS
$$
  DECLARE
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!!
    NEW_EXCEPTION EXCEPTION;
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!
    PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
    NEW_EXCEPTION2 EXCEPTION (-20100, '');
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - 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;
$$;

 -- Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_SAMPLE AUTHID DEFINER IS
  NEW_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
BEGIN

  IF true THEN
    RAISE NEW_EXCEPTION;
  END IF;

EXCEPTION
    WHEN NEW_EXCEPTION THEN
        --Handle Exceptions
END;

 CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_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.

  try {
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ***/!!!
    /*   NEW_EXCEPTION EXCEPTION */
    ;
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!
    /*   PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63) */
    ;
    if (true) {
      RAISE(-63,`NEW_EXCEPTION`,`NEW_EXCEPTION`);
    }
  } catch(error) {
    switch(error.name) {
      case `NEW_EXCEPTION`: {
        break;
      }
      default: {
        throw error;
        break;
      }
    }
  }
  //Handle Exceptions
  ;
$$;

 SELECT CAST('12sdsd3,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12345sdsd' AS NUMBER, '99999') FROM DUAL;

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12sdsd3,456E+40' ***/!!!
 CAST('12sdsd3,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12345sdsd' ***/!!! CAST('12345sdsd' AS NUMBER(38, 18) , '99999') FROM DUAL;

CREATE OR REPLACE function FOO1 RETURN INTEGER AS
    FUNCTION FOO2 RETURN INTEGER AS
    BEGIN
        RETURN 123;
    END;
BEGIN
    RETURN FOO2() + 456;
END;

 !!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0046 - NESTED FUNCTION/PROCEDURE DECLARATIONS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
CREATE OR REPLACE PROCEDURE FOO1 ()
RETURNS INTEGER
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED FUNCTION IS NOT SUPPORTED IN THIS SCENARIO ***/!!!
        FUNCTION FOO2 RETURN INTEGER AS
        BEGIN
            RETURN 123;
        END;
    BEGIN
        RETURN FOO2() + 456;
    END;
$$;

 ALTER TABLE TABLE1 ADD (
  CONSTRAINT TABLE1_PK
  PRIMARY KEY
  (ID)
  ENABLE VALIDATE,
  CONSTRAINT TABLE1_FK foreign key(ID2)
  references TABLE2 (ID) ON DELETE CASCADE);

 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0067 - MULTIPLE CONSTRAINT DEFINITION IN A SINGLE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
ALTER TABLE TABLE1
ADD (
  CONSTRAINT TABLE1_PK
  PRIMARY KEY
  (ID) ,
  CONSTRAINT TABLE1_FK foreign key(ID2)
  references TABLE2 (ID) ON DELETE CASCADE);

 CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;

 CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;

 CREATE OR REPLACE SEQUENCE SEQUENCE1
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0068 - 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
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0068 - 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"}}';

 select seq1.currval from dual;

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

 -- Unsupported operation: EXCEPT DISTINCT
SELECT someValue MULTISET EXCEPT DISTINCT multiset_except FROM customers_demo;

 -- Unsupported operation: EXCEPT DISTINCT
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0070 - BINARY OPERATION MULTISET EXCEPT IS NOT SUPPORTED ***/!!!
 someValue MULTISET EXCEPT DISTINCT multiset_except FROM
 customers_demo;

 SELECT location_id  FROM locations 
MINUS ALL 
SELECT location_id  FROM departments;

 SELECT location_id  FROM
locations
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0071 - QUANTIFIER 'ALL' NOT SUPPORTED FOR THIS SET OPERATOR, RESULTS MAY DIFFER ***/!!!
MINUS
SELECT location_id  FROM
departments;

 -- Additional Params: -t JavaScript
CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE1
   IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    NULL;
END;

 --** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
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
$$
   // SnowConvert AI Helpers Code section is omitted.

   !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER PRAGMA DECLARATION NOT SUPPORTED. ***/!!!
   /*    PRAGMA AUTONOMOUS_TRANSACTION */
   ;
   null;
$$;

 --Additional Params: -t JavaScript
CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE1 
IS
BEGIN
    -- procedure body
    EXIT loop_b;
    -- procedure body continuation
END;

--Additional Params: -t JavaScript
--** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    // REGION SnowConvert AI Helpers Code
    var RAISE = function (code,name,message) {
        message === undefined && ([name,message] = [message,name])
        var error = new Error(message);
        error.name = name
        SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
        throw error;
    };
    var SQL = {
        FOUND : false,
        NOTFOUND : false,
        ROWCOUNT : 0,
        ISOPEN : false
    };
    var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
    var getObj = (_rs) => Object.assign(new Object(),_rs);
    var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
    var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);
    var EXEC = function (stmt,binds,opts) {
        try {
            binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
            opts = opts || new Object();
            binds = binds ? binds.map(fixBind) : binds;
            _RS = snowflake.createStatement({
                    sqlText : stmt,
                    binds : binds
                });
            _ROWS = _RS.execute();
            if (opts.sql !== 0) {
                var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
                var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
                SQL.FOUND = affectedRows != 0;
                SQL.NOTFOUND = affectedRows == 0;
                SQL.ROWCOUNT = affectedRows;
            }
            if (opts.row === 2) {
                return _ROWS;
            }
            var INTO = function (opts) {
                if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
                    return _ROWS.getColumnValue(1);
                }
                if (opts.rec instanceof Object && _ROWS.next()) {
                    var recordKeys = Object.keys(opts.rec);
                    Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
                    return opts.rec;
                }
                return fetch(_RS,_ROWS,opts.row);
            };
            var BULK_INTO_COLLECTION = function (into) {
                for(let i = 0;i < _RS.getRowCount();i++) {
                    FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
                }
                return into;
            };
            if (_ROWS.getRowCount() > 0) {
                return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
            }
        } catch(error) {
            RAISE(error.code,error.name,error.message)
        }
    };
    var FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
        for(let i = 0;i < collections.length;i++) {
            collections[i].push(fetchValues[i]);
        }
    };
    var IS_NULL = (arg) => !(arg || arg === 0);
    var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
    var fixBind = function (arg) {
        arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
        return arg;
    };
    // END REGION

    /*     -- procedure body
        EXIT loop_b */
    // procedure body
    // procedure body
    ;
    // procedure body continuation
    ;
$$;

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

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_RAW' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS CAST_TO_RAW,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_UTILITY.GET_TIME' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS GET_TIME
FROM DUAL;

 --Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1 AS
BEGIN
    EXECUTE IMMEDIATE 'NOT A VALID SQL STATEMENT';
END;

 CREATE OR REPLACE PROCEDURE PROC1 ()
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
$$
    // SnowConvert AI Helpers Code section is omitted.

    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0078 - UNABLE TO PARSE DYNAMIC SQL STATEMENT ***/!!!
    /*EXEC(`NOT A VALID SQL STATEMENT`)*/
    ;
$$;

 CREATE TYPE type1 AS OBJECT (
  attribute1 VARCHAR2(20),
  attribute2 NUMBER
);
CREATE TYPE type2 AS OBJECT (
  property1 type1,
  property2 DATE
);
CREATE TABLE my_table (
  id NUMBER PRIMARY KEY,
  column1 type2
);
INSERT INTO my_table VALUES (
  1, type2(type1('value1', 100), SYSDATE)
);
SELECT column1.property1.attribute1, column1.property2
FROM my_table;

 !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type1 AS OBJECT (
  attribute1 VARCHAR2(20),
  attribute2 NUMBER
)
;

!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type2 AS OBJECT (
  property1 type1,
  property2 DATE
)
;

CREATE OR REPLACE TABLE my_table (
  id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
  column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'type2' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
;

CREATE OR REPLACE VIEW my_table_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
AS
SELECT
  id,
  column1:property1:attribute1 :: VARCHAR AS attribute1,
  column1:property1:attribute2 :: NUMBER AS attribute2,
  column1:property2 :: DATE AS property2
FROM
  my_table;

INSERT INTO my_table
VALUES (
  1, type2(type1('value1', 100) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'type1' NODE ***/!!!, CURRENT_TIMESTAMP()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'type2' NODE ***/!!!
);

SELECT column1.property1.attribute1,
  column1.property2
FROM
  my_table;