SnowConvert: Oracle Issues

SSC-EWI-OR0001

Description

This error occurs when the value in the START WITH statement is set to LIMIT VALUE.

In Oracle, this clause is only used with the ALTER TABLE statement.

The START WITH LIMIT VALUE option, which is only available for identity_options, can only be used with the ALTER TABLE MODIFY command. When you use START WITH LIMIT VALUE, Oracle Database will:

  1. Lock the table

  2. Find either:

    • The maximum identity column value (for increasing sequences)

    • The minimum identity column value (for decreasing sequences)

  3. Set this value as the sequence generator’s high water mark

  4. Calculate the next sequence value by:

    • For increasing sequences: high water mark + INCREMENT BY integer

    • For decreasing sequences: high water mark - INCREMENT BY integer

ALTER TABLE ORACLE

Example Code

Input Code:

CREATE SEQUENCE SEQUENCE1
  START WITH LIMIT VALUE;
Copy

Output Code:

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"}}';
Copy

Recommendations

SSC-EWI-OR0050

Severity

Medium

Description

This error occurs when attempting to cast a value that exceeds the allowed range. This means the precision value is not supported in Snowflake, resulting in the code being commented out and this message being added.

Example Code:

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0110

Parts of the code have been removed to make it easier to understand.

Severity

High

Description

There is no direct equivalent for the FOR UPDATE clause in Snowflake’s Snowscript language. The tool will add an Error, Warning, or Information (EWI) message and comment out this clause during conversion.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE for_update_clause
AS
    update_record f_employee%rowtype;
    CURSOR c1 IS SELECT * FROM f_employee FOR UPDATE OF employee_number nowait;
BEGIN
    FOR CREC IN C1 LOOP
	UPDATE f_employee SET employee_number = employee_number + 1000 WHERE CURRENT OF c1;
	IF crec.id = 2 THEN
	    DELETE FROM f_employee WHERE CURRENT OF c1;
	    EXIT;
	END IF;
    END LOOP;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE for_update_clause ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		update_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
		c1 CURSOR
		FOR
			SELECT * FROM
				f_employee
			!!!RESOLVE EWI!!! /*** SSC-EWI-OR0110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
			FOR UPDATE OF employee_number nowait;
	BEGIN
		OPEN C1;
		--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
		FOR CREC IN C1 DO
			!!!RESOLVE EWI!!! /*** SSC-EWI-OR0136 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
			UPDATE f_employee
				SET employee_number =
 				                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! employee_number + 1000 WHERE CURRENT OF c1;
			IF (crec.id = 2) THEN
--				!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'CURRENT OF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--				DELETE FROM
--					f_employee
--				WHERE CURRENT OF c1
				                   ;
				EXIT;
			END IF;
		END FOR;
		CLOSE C1;
	END;
$$;
Copy

Recommendations

  • Handle column updates in UPDATE/DELETE queries. For more information, please refer to error code SSC-EWI-0075.

  • For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0035

Parts of the output code have been removed to make it easier to understand.

Severity

Medium

Description

The TABLE function cannot be used in Snowflake when attempting to create a collection of expressions. This functionality is not currently supported.

Example Code

Input Code:

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

Output Code:

// SnowConvert 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;
Copy

Recommendations

SSC-EWI-OR0097

Severity

Low

Description

Oracle’s additional properties in CREATE PROCEDURE statements are not supported in Snowflake’s CREATE PROCEDURE syntax and can be safely omitted during migration.

Example Code

Input Code Oracle:

CREATE OR REPLACE PROCEDURE PROC01
DEFAULT COLLATION USING_NLS_COMP
AUTHID CURRENT_USER
ACCESSIBLE BY (PROCEDURE PROC03)
AS
BEGIN
    NULL;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE PROC01 ()
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
$$
    BEGIN
        NULL;
    END;
$$;
Copy

Recommendations

SSC-EWI-OR0087

Severity

Low

Parts of the code have been omitted to keep the example clear and concise.

Description

This error occurs when there is a problem converting Oracle’s outer join syntax using the (+) operator to ANSI JOIN clauses. Specifically, it happens when the original query contains circular table references in the WHERE clause, where tables are joined in a cyclical pattern.

When this Early Warning Indicator (EWI) appears, JOIN operations might fail because they are not in the correct sequence.

Example Code

Input Code Oracle:

SELECT
l.location_id, l.state_province,
r.region_id, r.region_name,
c.country_id, c.country_name
FROM
hr.countries c,  hr.regions r,  hr.locations l, hr.departments d WHERE
l.location_id (+) = c.region_id AND
c.region_id (+) = r.region_id AND
r.region_id (+) = c.region_id AND
l.location_id (+) = d.location_id;
Copy

Output Code:

SELECT
l.location_id, l.state_province,
r.region_id, r.region_name,
c.country_id, c.country_name
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0087 - ORDERING THE OUTER JOINS FAILED. QUERY MAY NOT BEHAVE CORRECTLY ***/!!!
hr.departments d
LEFT OUTER JOIN
hr.locations l
ON
l.location_id = c.region_id
AND
l.location_id = d.location_id
LEFT OUTER JOIN
hr.countries c
ON
c.region_id = r.region_id
LEFT OUTER JOIN
hr.regions r
ON
r.region_id = c.region_id;
Copy
  • Verify that your query is syntactically correct and check that you’re not joining tables to themselves unnecessarily.

  • If the problem persists, specify the table name for each column in the WHERE clause (for example, use table1.column_name instead of just column_name).

  • For additional assistance, contact our support team at snowconvert-support@snowflake.com

SSC-EWI-OR0100

Severity

Low

Description

Oracle’s FOR LOOP can have multiple conditions, but Snowflake Scripting’s FOR LOOP only supports a single condition. When converting from Oracle to Snowflake, only the first condition will be migrated, and any additional conditions will be ignored.

Example Code

Input Code:

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

Output Code:

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
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0100 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN REVERSE 1 TO 3 LOOP
            NULL;
        END LOOP;
    END;
$$;
Copy

Recommendations

  • Break down complex FOR LOOP statements into multiple simpler loops, or rewrite the loop condition to make it clearer.

  • For additional assistance, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-OR0011

Severity

Medium

Description

The Cast function in Snowflake does not support the format parameter when using “MONTH” or “DAY” within DATE or TIMESTAMP formats.

"MONTH/DD/YYYY" or "MM/DAY/YY" ...
Copy

When using the CAST function with NUMBER data type in Snowflake, you need to specify four arguments to properly display decimal values. Currently, the output code doesn’t provide all the required arguments for the TO_NUMBER function. You will need to manually add the missing arguments to ensure proper decimal formatting.

Example Code

Input Code:

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

Output Code:

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

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0031

Severity

Low

Description

This error occurs when you use an unsupported parameter in the SYS_CONTEXT function. Snowflake provides similar context functions that you can use instead. For more information about supported context functions, visit the Snowflake documentation.

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • The function is converted to a user-defined function (UDF) template that you can customize to replicate the behavior of the SYS_CONTEXT parameter.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0005

This Early Warning Indicator (EWI) is no longer in use. Please see SSC-FDM-OR0043 for current documentation.

Severity

Low

Description

This error occurs when converting a TO_CLOB function to TO_VARCHAR. When working with BFILE or BLOB parameters, you may need to specify a format parameter for proper conversion.

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • Verify that the output results match between the source code and the converted code. Add a format parameter if required.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0104

Severity

High

Description

Oracle collections are currently not supported by SnowConvert. Any variables using collection types and their associated operations will be commented out in the converted code.

Generate Procedures and Macros in JavaScript by adding either the -t JavaScript flag or --PLTargetLanguage JavaScript option to your command.

Example Code

Input Code Oracle:

-- Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE collection_variable_sample_proc
IS
    TYPE POPULATION IS TABLE OF NUMBER INDEX BY VARCHAR2(64); --Associative array
    city_population POPULATION := POPULATION();
    i  VARCHAR2(64);
BEGIN
	city_population('Smallville')  := 2000;
    city_population('Midland')     := 750000;

    i := city_population.FIRST;
    i := city_population.NEXT(1);
END;
Copy

Output Cod

CREATE OR REPLACE PROCEDURE collection_variable_sample_proc ()
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 Helpers Code section is omitted.

	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!
	/*     TYPE POPULATION IS TABLE OF NUMBER INDEX BY VARCHAR2(64) */
	;
	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
	/*     city_population POPULATION := POPULATION() */
	;
	let I;
	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
	/* 	city_population('Smallville')  := 2000 */
	;
	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
	/*     city_population('Midland')     := 750000 */
	;
	I =
		!!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
		/*city_population.FIRST*/
		null;
	I =
		!!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
		/*city_population.NEXT(1)*/
		null;
$$;
Copy

Recommendations

SSC-EWI-OR0070

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to understand.

Description

A binary operation cannot be directly converted. To resolve this, we have added a user-defined function.

Example Code

Oracle:

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

Snowflake Scripting:

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

Recommendations

SSC-EWI-OR0030

Severity

Medium

Description

The error indicates that the KEEP FIRST or KEEP LAST clause, which is used to select only the first or last values when using aggregate functions, is not currently supported.

Example Code

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0121

Severity

Medium

Description

When migrating Oracle BFILE columns to Snowflake, they are converted to VARCHAR columns that store the file name as a string. This means that using the SUBSTR function in Snowflake on these migrated columns will operate on the file name itself, not the file contents. This differs from Oracle’s DBMS_LOB.SUBSTR function, which operates on the actual contents of the file. For more details, see BFILE data type.

Example Code

Input Code:

CREATE TABLE table1
(
    bfile_column BFILE
)
SELECT
DBMS_LOB.SUBSTR(bfile_column, 15, 1)
FROM table1;
Copy

Output Code:

CREATE OR REPLACE TABLE table1
    (
        bfile_column
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
    VARCHAR
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;
    SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0121 - USING DBMS_LOB.SUBSTR ON BFILE SOURCE COLUMN IS NOT SUPPORTED ON SNOWFLAKE ***/!!!
    SUBSTR(bfile_column, 1, 15)
    FROM
    table1;
Copy

Recommendations

SSC-EWI-OR0092

Severity

Low

Description

This message appears when attempting to use a negative scale with a NUMBER data type for rounding purposes. Since Snowflake does not support negative scales, the scale parameter has been removed from the conversion.

Example Code

Input Code Oracle:

CREATE TABLE number_table
(
	col1 NUMBER(38),
	col2 NUMBER(38, -1),
	col3 NUMBER(*, -2)
);

INSERT INTO number_table(col1, col2, col3) VALUES (555, 555, 555);

SELECT * FROM number_table;
Copy
COL1|COL2|COL3|
-+-+-+
 555| 560| 600|
Copy

Output Code:

CREATE OR REPLACE TABLE number_table
	(
		col1 NUMBER(38) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
		col2 NUMBER(38) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/!!! /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
		col3 NUMBER(38) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/!!! /*** 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 number_table(col1, col2, col3) VALUES (555, 555, 555);

	SELECT * FROM
	number_table;
Copy
|COL1|COL2|COL3|
|-|-|-|
|555 |555 |555 |
Copy

Recommendations

SSC-EWI-OR0004

Parts of the code have been removed to make the example clearer and easier to understand.

Description

This warning appears when a SELECT statement contains a clause that Snowflake does not support. The following clauses are not supported in Snowflake:

  • Container objects that store data

  • Hierarchical data structures for organizing information

  • External modifications to data sources

  • Data partitions or segments

Example Code

Input Code:

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

Output Code:

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);
Copy

Recommendations

SSC-EWI-OR0014

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Medium

Description

The NLSSORT function cannot be used within the SELECT statement at this time.

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • NLSSORT is transformed into a user-defined function (UDF/Stub), which you can customize to replicate its behavior.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0105

Severity

Low

Description

The BFILE datatype is converted to VARCHAR during migration. This conversion allows storage of the file path and name. Note that Oracle’s built-in functions for handling BFILE types are not currently supported. For more details about handling file paths and names, please refer to the BFILENAME_UDF documentation.

The BUILD_STAGE_FILE_URL function provides a secure way to access files stored in Snowflake stages. When called, it generates a URL that points to a specific file in a stage. For more details, refer to the function documentation here.

Example Code

Input Code Oracle:

CREATE TABLE bfiletable ( bfile_column BFILE );

INSERT INTO bfiletable VALUES ( BFILENAME('mydirectory', 'myfile.png') );
Copy

Output Code:

CREATE OR REPLACE TABLE bfiletable ( bfile_column
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO bfiletable
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png') );
Copy

Recommendations

  • Use the BUILD_STAGE_FILE_URL function and other file functions to manage and manipulate files in Snowflake.

CREATE OR REPLACE TABLE bfiletable ( bfile_column
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO bfiletable
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png') );
Copy
URL                                                                                                   |
+
https://thecompany.snowflakecomputing.com/api/files/CODETEST/PUBLIC/MY_STAGE/%2Fmydirectory%2Fmyfile.jpg|
Copy

This function is compatible with various cloud storage providers. For information about using files from your local system with stages, please refer to this documentation.

SSC-EWI-OR0045

Severity

Medium

Description

This message appears when attempting to use FML or L format casting, which is not available in Snowflake. In such cases, the code is commented out and this warning message is displayed.

Example Code:

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0082

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Medium

Description

This error occurs when a query attempts to access an attribute within a column that was defined as a custom type. While automatic conversion is not possible, you can easily convert these queries manually.

Example Code:

Input Code Oracle:

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

Output Code:

!!!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":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.my_table_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
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
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0082 - CANNOT CONVERT NESTED TYPE ATTRIBUTE EXPRESSION ***/!!! column1.property1.attribute1,
  column1.property2
FROM
  my_table;
Copy

Recommendations

  • To fix the code manually, replace the ‘.’ (dot) operator with ‘:’ (colon) when accessing type columns.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0071

Severity

Low

Description

The use of the ‘all’ quantifier is not supported in Snowflake. When converting your code, this modifier will be removed and a warning message will be generated. Please note that this modification may cause unexpected behavior in your code.

Example Code

Input Code:

SELECT location_id  FROM locations
MINUS ALL
SELECT location_id  FROM departments;
Copy

Output Code:

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

In Snowflake, the INTERSECT and MINUS/EXCEPT operators automatically eliminate duplicate values from the result set.

Recommendations

Check alternative methods in Snowflake to replicate the “all” quantifier functionality. Here’s how to work around the limitations of MINUS ALL and EXCEPT ALL operations.

SELECT location_id FROM
(
    SELECT location_id, ROW_NUMBER()OVER(PARTITION BY location_id ORDER BY 1) rn
    FROM locations
    MINUS
    SELECT number_val, ROW_NUMBER()OVER(PARTITION BY location_id ORDER BY 1) rn
    FROM departments
);
Copy

SSC-EWI-OR0020

Severity

Moderate

Description

Snowflake’s implementation of this function does not accept negative values, which will result in different behavior compared to the source database when executed.

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • Create a User Defined Function (UDF) that can process negative values as input parameters, or explore alternative solutions.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0051

Severity

Low

Description

This warning appears when a PRAGMA EXCEPTION_INIT function is used within a procedure. In the original code, exception names and SQL codes are defined using the RAISE function. During conversion to Snowflake Scripting, these SQL codes are included in the exception declaration. However, some of these SQL code values might not be compatible with Snowflake Scripting.

Example Code

Input Code:

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;
/
Copy

Output Code:

Snowflake script

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;
$$;
Copy

Recommendations

SSC-EWI-OR0075

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to understand.

Generate Procedures and Macros in JavaScript by adding either the flag -t JavaScript or --PLTargetLanguage JavaScript to your command.

Description

Labels in code blocks cannot be used as references in statements.

Example Code

Oracle:

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

Snowflake Scripting:

--** 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":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    /* ** SSC-EWI-OR0075 - LABELS IN STATEMENTS ARE NOT SUPPORTED. ** */
    /*     -- procedure body
        EXIT loop_b */
    // procedure body
    ;
    // procedure body continuation
    ;
$$;
Copy

Recommendations

SSC-EWI-OR0135

Severity

Low

Description

When executing a time travel query in Snowflake, you may receive no results if you specify a time that falls outside the data retention period. For more information, please refer to Snowflake’s Time Travel.

Example code

Input code

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Copy

Output code

SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0135 - DATA RETENTION PERIOD MAY PRODUCE NO RESULTS ***/!!!
AT (TIMESTAMP =>
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS'))
WHERE last_name = 'SampleName';
Copy

Recommendations

SSC-EWI-OR0101

Severity

Low

Description

Oracle’s FOR LOOP supports additional clauses that are not available in Snowflake Scripting. These include:

  • BY clause: Controls the step size of the loop counter

  • WHILE clause: Adds a boolean condition to continue the loop

  • WHEN clause: Adds a boolean condition to continue the loop

When transforming Oracle code to Snowflake, these clauses are ignored as they are not supported in Snowflake Scripting.

Example Code

Input Code Oracle:

CREATE OR REPLACE PROCEDURE P2
AS
BEGIN
FOR i IN 1..10 WHILE i <= 5 LOOP
    NULL;
END LOOP;

FOR i IN 5..15 BY 5 LOOP
    NULL;
END LOOP;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE P2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN 1 TO 10 LOOP
            NULL;
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN 5 TO 15 LOOP
            NULL;
        END LOOP;
    END;
$$;
Copy

Recommendations

  • Break down complex FOR LOOP statements into multiple simpler loops, or rewrite the loop condition to make it clearer.

  • For additional assistance, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-OR0010

Parts of the code have been removed to make the example clearer and easier to understand.

Severity

Critical

Description

This warning appears when PARTITION and SUBPARTITION clauses are used in a query. Snowflake manages data partitioning automatically, so these clauses are not needed.

Example Code

Input Code:

SELECT * FROM table1 PARTITION(col1);
Copy

Output Code:

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

Recommendations

  • To achieve similar functionality in Snowflake, you must manually add a WHERE clause to filter specific partition rows. Note that this workaround may impact query performance.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0099

Severity

Low

Description

This error occurs when the exception declaration number is outside the allowed range for Snowflake Scripting. Exception numbers must be integers between -20000 and -20999.

Example Code

Input Code:

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

Output Code:

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
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!!
        my_exception EXCEPTION;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!
        PRAGMA EXCEPTION_INIT ( my_exception, -19000 );
    BEGIN
        NULL;
    END;
$$;
Copy

Recommendations

  • Verify that your exception code falls within Snowflake Scripting’s allowed range. If it doesn’t, use an alternative available exception number.

  • For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0089

This Early Warning Indicator (EWI) is no longer in use. Please see SSC-FDM-OR0044 for current documentation.

Severity

Low

Description

This warning appears when using Oracle’s REGEXP_LIKE function with a match parameter (third parameter). The warning indicates that the REGEXP_LIKE_UDF function in Snowflake, which replaces Oracle’s REGEXP_LIKE, may not support all match parameter characters. As a result, the query output in Snowflake might differ from Oracle.

Example Code

Input Code Oracle:

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

Output Code:

SELECT last_name
FROM
hr.employees
WHERE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0089 - 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;
Copy
  • If your REGEXP_LIKE condition contains unsupported characters in the user-defined function, consider modifying the regular expression to achieve the same matching behavior without using these characters. For details about unsupported characters, please refer to the REGEXP_LIKE_UDF documentation.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0128

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Low

Description

This message indicates that a boolean cursor attribute cannot be used in SnowScript, either because it is not supported or because there is no equivalent functionality. The table below lists the boolean cursor attributes that can be replicated in SnowScript:

| Boolean Cursor Attribute | Status | | | - | | %FOUND | Can be emulated | | %NOTFOUND | Can be emulated | | %ISOPEN | Not Supported |

Example Code

Input Code:

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

Output Code:

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*/!!!RESOLVE EWI!!! /*** SSC-EWI-OR0128 - BOOLEAN CURSOR ATTRIBUTE %ISOPEN IS NOT SUPPORTED IN SNOWFLAKE ***/!!!;
            found_attr := my_record IS NOT NULL;
        END LOOP;
        CLOSE my_cursor;
    END;
$$;
Copy

Recommendations

SSC-EWI-OR0068

Severity

Medium

Description

This error occurs when the START WITH value is larger than Snowflake’s maximum allowed value. According to Snowflake’s documentation, the sequence start value must be within the range of a 64-bit two’s complement integer: from -2^63 to 2^63-1. Specifically, the maximum allowed values are:

  • Positive numbers: 9223372036854775807

  • Negative numbers: 9223372036854775808

Example Code

Input Code:

CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;
Copy
CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;
Copy

Output Code:

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"}}';
Copy
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"}}';
Copy

Recommendations

  • We recommend resetting the sequence and adjusting how it’s used. NOTE: Make sure the target column has sufficient capacity to store this value.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0039

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Low

Description

This message appears when SnowConvert detects a query containing a NOCYCLE clause, which is currently not supported in Snowflake.

This statement indicates when a recursive operation occurs.

For additional information about the clause functionality, please refer to the documentation.

Example Code

Connect By

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0029

Description

Snowflake does not support the “Default on conversion error” feature.

Example Code

Input Code:

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

Output Code:

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0029 - DEFAULT ON CONVERSION ERROR NOT SUPPORTED IN SNOWFLAKE ***/!!! TO_NUMBER('2,00') "Value" FROM DUAL;
Copy

Recommendations

  • Consider creating a User-Defined Function (UDF) to replicate the functionality of the DEFAULT value when handling ON CONVERSION ERROR.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0078

Severity

Medium

Description

SnowConvert was unable to parse the dynamic SQL statement contained within the Execute Immediate command.

Generate Procedures and Macros in JavaScript by adding either the -t JavaScript flag or --PLTargetLanguage JavaScript option to your command.

Example Code

Oracle:

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

Snowflake Scripting:

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 Helpers Code section is omitted.

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

Recommendations

  • Verify that your dynamic SQL statement has correct syntax.

  • Check the SnowConvert documentation to confirm if your statement is supported.

  • For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0118

Severity

Medium

Description

Oracle includes built-in views and tables that Snowflake does not support. When these elements are used in queries or statements, SnowConvert will generate an error message.

Example Code

Input Code:

SELECT * FROM ALL_COL_COMMENTS;
SELECT * FROM (SELECT * FROM ALL_COL_COMMENTS);
Copy

Output Code:

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0118 - TRANSLATION FOR ORACLE BUILT-IN TABLE/VIEW 'ALL_COL_COMMENTS' IS NOT CURRENTLY SUPPORTED. ***/!!!
 * FROM
 ALL_COL_COMMENTS;

SELECT * FROM (SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0118 - TRANSLATION FOR ORACLE BUILT-IN TABLE/VIEW 'ALL_COL_COMMENTS' IS NOT CURRENTLY SUPPORTED. ***/!!! * FROM
ALL_COL_COMMENTS);
Copy

Recommendations

SSC-EWI-OR0009

Severity

High

Description

The JSON_TABLE function is currently not supported in this version.

Example Code

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0108

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Medium

Description

Oracle variable types such as cursors, collections, records, and user-defined types cannot be directly converted to Snowflake. Additionally, Snow Scripting does not currently support the transformation of placeholders, objects, and output parameters.

Converting these variables to Snowflake semi-structured data types may provide a solution in certain cases.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE pinvalidassign(out_parameter   IN OUT NUMBER)
AS
record_variable       employees%ROWTYPE;

TYPE cursor_type IS REF CURSOR;
cursor1   cursor_type;
cursor2   SYS_REFCURSOR;

TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
collection_variable     collection_type;

BEGIN
--Record Example
  record_variable.last_name := 'Ortiz';

--Cursor Example
  cursor1 := cursor2;

--Collection
  collection_variable('Test') := 5;

--Out Parameter
  out_parameter := 123;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE pinvalidassign (out_parameter NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE cursor_type IS REF CURSOR;
    cursor1_res RESULTSET;
    cursor2_res RESULTSET;
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_type' USAGE CHANGED TO VARIANT ***/!!!;
  BEGIN
    --Record Example
    record_variable := OBJECT_INSERT(record_variable, 'LAST_NAME', 'Ortiz', true);

    --Cursor Example
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      cursor1 := :cursor2;

    --Collection
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      collection_variable('Test') := 5;
    --Out Parameter
    out_parameter := 123;
    RETURN out_parameter;
  END;
$$;
Copy

Recommendations

SSC-EWI-OR0008

Severity

Low

Description

This error occurs when the system encounters date formats that are not recognized, which could lead to unexpected results.

Example Code

Input Code:

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

Output Code:

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

Note: The date format ‘iw-iyyy’ is not supported.

Recommendations

SSC-EWI-OR0109

Severity

Medium

Description

Snowflake Scripting does not support using expressions as arguments in the USING clause of EXECUTE IMMEDIATE statements, unlike Oracle where this functionality is available.

Snowflake Scripting supports variable expressions. You can replace an expression by manually assigning it to a variable, as shown in the example below.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE expression_arguments
IS
  immediate_input INTEGER := 0;
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO immediate_table VALUES (:value)' USING immediate_input+1;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    immediate_input INTEGER := 0;
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE 'INSERT INTO immediate_table
VALUES (?)' USING (
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0109 - EXPRESSIONS AS ARGUMENTS OF USING CLAUSE IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
    :immediate_input +1);
  END;
$$;
Copy

Manually migrated Execute Immediate procedure:

To resolve the compilation error while maintaining the same results as Oracle, use the procedure described above.

CREATE OR REPLACE PROCEDURE PUBLIC.expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      immediate_input INTEGER := 0;
      using_argument_variable INTEGER;
   BEGIN
      using_argument_variable := immediate_input+1;
      EXECUTE IMMEDIATE 'INSERT INTO PUBLIC.immediate_table VALUES (?)' USING (using_argument_variable );
   END;
$$;
Copy

Recommendations

SSC-EWI-OR0049

Severity

Critical

Parts of the code have been omitted to keep the example clear and concise.

Description

This warning appears when you attempt to use an unsupported member of a Stateful Package.

This feature will be available in a future release.

Example Code

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0129

Severity

Low

Parts of the code output have been removed to make the example clearer.

Description

This warning appears when the system cannot determine the data type of an item because its referenced TYPE attribute cannot be found. In such cases, the system automatically assigns the VARIANT data type to the item.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE procedure01
IS
var1 table01.col1%TYPE;
BEGIN
NULL;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE procedure01 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
var1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'table01.col1%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!;
BEGIN
NULL;
END;
$$;
Copy

Recommendations

  • Manually verify the data type of the referenced item and update the TYPE attribute in the referencing item accordingly.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0069

Severity

Medium

Description

Snowflake does not support the CURRVAL property for sequences.

Example Code

Oracle:

select seq1.currval from dual;
Copy

Snowflake Scripting:

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

Recommendations

SSC-EWI-OR0038

Severity

Low

Description

The search_clause controls how rows are processed in a SELECT statement by defining their order. While this feature allows you to specify how data should be traversed and returned in Oracle, it is not available in Snowflake.

In Oracle and similar databases, the search_clause helps control how hierarchical data is processed in recursive queries or Common Table Expressions (CTEs). When you specify columns in the search_clause, you can choose between depth-first or breadth-first traversal methods, which determines the order of row processing.

In Snowflake, a search_clause message will be generated and then removed.

Example Code

Input Code:

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

Output Code:

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

Recommendation

SSC-EWI-OR0033

Severity

Medium

Description

PL/SQL variable declarations within WITH clauses are currently not supported.

Example Code

Input Code:

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

Output Code:

// SnowConvert 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;
Copy

Recommendations

SSC-EWI-OR0007

Description

This message appears when you attempt to use a Create Type statement that Snowflake does not support.

Example Code

Input Code (Oracle):

CREATE TYPE type6 UNDER type5(COL1 INTEGER);
Copy

Output Code:

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

Recommendations

SSC-EWI-OR0116

Severity

Medium

Parts of the output code have been removed to make the example clearer.

Description

This error occurs when attempting to perform arithmetic operations exclusively with intervals. Snowflake does not support calculations between two interval values.

Example Code

Input Code:

SELECT INTERVAL '1-1' YEAR(2) TO MONTH + INTERVAL '1-1' YEAR(2) + INTERVAL '1-1' YEAR(2) TO MONTH FROM dual;

SELECT INTERVALCOLUMN + INTERVAL '1-1' YEAR(2) TO MONTH FROM INTERVALTABLE;
Copy

Output Code:

SELECT
--INTERVAL '1-1 year' + INTERVAL '1y, 1mm' + INTERVAL '1y, 1mm'
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0116 - OPERATIONS BETWEEN INTERVALS ARE NOT SUPPORTED BY SNOWFLAKE ***/!!!
null
FROM dual;


SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!! INTERVALCOLUMN + INTERVAL '1y, 1mm'
FROM
INTERVALTABLE;
Copy

Recommendations

  • If an operation’s timing is causing issues, you can fix it by adding dates or timestamps to the appropriate location.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0046

This warning is no longer in use. Please see SSC-EWI-0109 for current documentation.

Severity

Medium

Description

When a table alteration command is not compatible with Snowflake’s syntax, the code will be commented out and this notification will be added as a comment.

Example Code:

Input Code:

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

Output Code:

!!!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;
Copy

Recommendations

SSC-EWI-OR0023

Severity

High

Parts of the code have been removed to make the example clearer and easier to understand.

Description

This error occurs when an aggregate function is used

  • DENSE_RANK(): Assigns a rank to each row within a partition, with no gaps in the ranking sequence when there are ties

  • RANK(): Assigns a rank to each row within a partition, with gaps in the ranking sequence when there are ties

  • PERCENT_RANK(): Calculates the relative rank of a row within a partition as a percentage (0 to 1)

  • CUME_DIST(): Calculates the cumulative distribution of a value within a partition (0 to 1)

is not supported in Snowflake.

Example Code

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0072

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to understand.

Generate Procedures and Macros in JavaScript by adding either the flag -t JavaScript or --PLTargetLanguage JavaScript to your command.

Description

Procedural members are not currently supported in this version. Here is an example of what we mean by procedural members:

  • Constants defined in the code

  • Database cursors used for data retrieval

  • Pragma statements that control compiler behavior

  • Variables defined in the code

Example Code

Oracle:

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

Snowflake Scripting:

--** 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 Helpers Code section is omitted.

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

Recommendations

SSC-EWI-OR0052

Severity

Low

Parts of the code output have been removed to keep the example clear and concise.

Generate Procedures and Macros in JavaScript by adding either the -t JavaScript flag or --PLTargetLanguage JavaScript option to your command.

Description

Exceptions exist in both Oracle and Snowflake. However, the RAISE function handles all three tasks: declaring, assigning, and throwing the error. This is why the Exception declaration appears as a comment and generates a warning message.

Example Code

Input Code:

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

Output Code:

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 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
  ;
$$;
Copy

For better readability, some sections of the output code have been omitted.

Recommendations

SSC-EWI-OR0095

Severity

Low

Description

The data types INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are converted to VARCHAR(20) as they are not supported. As a result, you cannot perform arithmetic operations between Date Types and columns that were originally Interval Type.

Additionally, when performing operations between an Interval Type and a Date Type (specifically in that sequence), Snowflake does not support these operations and will generate this EWI message.

Example Code

Input Code:

CREATE TABLE table_with_intervals
(
    date_col DATE,
    time_col TIMESTAMP,
    intervalYearToMonth_col INTERVAL YEAR TO MONTH,
    intervalDayToSecond_col INTERVAL DAY TO SECOND
);

-- Date + Interval Y to M
SELECT date_col + intervalYearToMonth_col FROM table_with_intervals;

-- Date - Interval D to S
SELECT date_col - intervalDayToSecond_col FROM table_with_intervals;

-- Timestamp + Interval D to S
SELECT time_col + intervalDayToSecond_col FROM table_with_intervals;

-- Timestamp - Interval Y to M
SELECT time_col - intervalYearToMonth_col FROM table_with_intervals;
Copy

Output Code:

CREATE OR REPLACE TABLE table_with_intervals
    (
        date_col TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        time_col TIMESTAMP(6),
        intervalYearToMonth_col VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR TO MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!!,
        intervalDayToSecond_col VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY TO SECOND DATA TYPE CONVERTED TO VARCHAR ***/!!!
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    -- Date + Interval Y to M
    SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!! date_col + intervalYearToMonth_col FROM
    table_with_intervals;

    -- Date - Interval D to S
SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!! date_col - intervalDayToSecond_col FROM
    table_with_intervals;

    -- Timestamp + Interval D to S
SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!! time_col + intervalDayToSecond_col FROM
    table_with_intervals;

    -- Timestamp - Interval Y to M
SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!! time_col - intervalYearToMonth_col FROM
    table_with_intervals;
Copy

Recommendations

  • Create a User-Defined Function (UDF) that replicates Oracle’s functionality.

  • Use the transformed value stored in the column during migration as a Snowflake Interval Constant when available.

  • For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0126

Severity

Medium

Description

This error occurs when attempting to use an object that has a custom-defined data type built into the system.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE proc01 is
   var1 DBMS_SQL.VARCHAR2_TABLE;
   var2 CTX_CLS.DOC_TAB;
BEGIN
   varX := var1.property;
   varY := var2(1);
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE proc01 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      var1 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'DBMS_SQL.VARCHAR2_TABLE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/;
      var2 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'CTX_CLS.DOC_TAB' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/;
   BEGIN
      varX := var1.property !!!RESOLVE EWI!!! /*** SSC-EWI-OR0126 - UNUSABLE OBJECT var1, BUILT-IN CUSTOM TYPES ARE NOT SUPPORTED ***/!!!;
      varY := var2(1) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0126 - UNUSABLE OBJECT var2, BUILT-IN CUSTOM TYPES ARE NOT SUPPORTED ***/!!!;
   END;
$$;
Copy

Recommendations

SSC-EWI-OR0076

Severity

Medium

Description

Built-in package translation is not currently available.

Example Code

Input Code (Oracle):

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

Output Code:

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

Recommendations

SSC-EWI-OR0136

Severity

Critical

Description

Snowflake does not currently support the CURRENT OF clause within the WHERE clause of UPDATE and DELETE statements.

Example Code

Oracle:

CREATE OR REPLACE PROCEDURE proc_update_current_of
AS
  CURSOR C1
  IS
    SELECT * FROM F_EMPLOYEE FOR UPDATE OF SALARY nowait;
BEGIN
  FOR CREC IN C1
  LOOP
    UPDATE F_EMPLOYEE SET SALARY=SALARY+2000 WHERE CURRENT OF C1;
  END LOOP;
END;
Copy

Snowflake Scripting:

CREATE OR REPLACE PROCEDURE proc_update_current_of ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    C1 CURSOR
    FOR
      SELECT * FROM
        F_EMPLOYEE
      !!!RESOLVE EWI!!! /*** SSC-EWI-OR0110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
      FOR UPDATE OF SALARY nowait;
  BEGIN
      OPEN C1;
      --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
      FOR CREC IN C1 DO
      !!!RESOLVE EWI!!! /*** SSC-EWI-OR0136 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
      UPDATE F_EMPLOYEE
        SET SALARY=
                   !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!SALARY+2000 WHERE CURRENT OF C1;
      END FOR;
      CLOSE C1;
  END;
$$;
Copy

Related EWI

  1. SSC-EWI-OR0036: Warning: Arithmetic operations between string and date data types may produce unexpected results.

  2. SSC-PRF-0004: This code contains cursor-based loops which may impact performance.

  3. SSC-EWI-OR0110: The FOR UPDATE clause is not currently supported in Snowflake.

Recommendations

  • Rewrite the query using standard UPDATE or DELETE statements. Include specific column conditions in the WHERE clause. Be aware that if your table contains duplicate records, the query may affect these records multiple times.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0013

Severity

Medium

Description

The NLS (National Language Support) parameter is currently not supported for the following functions:

  • Convert a value to a character string (TOCHAR)

  • Convert a value to a date (TODATE)

  • Convert a value to a number (TONUMBER)

  • Convert a value to a timestamp (TOTIMESTAMP)

  • Convert a value to a specified data type (CAST)

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • The TO_NUMBER function is transformed into a user-defined function (UDF/Stub), allowing you to customize its behavior based on your specific parameter requirements.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0042

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Low

Description

This message appears when SnowConvert detects a query containing a MODEL clause, which is currently not supported in Snowflake.

Example Code

Input Code:

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

Output Code:

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)
();
Copy

Recommendations

SSC-EWI-OR0053

Severity

Medium

Description

This error occurs when attempting to cast data using an incorrect input format. As a result, the code is commented out and this message is displayed.

Example Code:

Input Code:

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

Output Code:

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

Recommendations

SSC-EWI-OR0002

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

High

Description

This error occurs when the system cannot resolve the columns in a Select Expression. This typically happens in two scenarios:

  1. When referencing a Type Access that hasn’t been resolved

  2. When trying to access columns from a User Defined Type that lacks column definitions (such as a Type Without Body or an empty Object Type)

Example Code

Input Code:

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

Output Code:

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;
$$;
Copy

Recommendations

SSC-EWI-OR0036

Severity

Low

Description

This error occurs when two incompatible data types are used in an arithmetic operation, which may lead to unexpected results.

Example Code

Input Code:

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

Output Code:

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

When performing operations between String and Date data types, be aware that the results may be unexpected or incorrect. It’s recommended to convert the String to a Date type before performing any date-related operations.

Recommendations

SSC-EWI-OR0067

Severity

Moderate

Parts of the code have been omitted to keep the example clear and concise.

Description

Snowflake does not allow you to define multiple constraints in a single ALTER TABLE statement.

Example Code

Oracle:

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

Snowflake Scripting:

!!!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);
Copy

Recommendations

SSC-EWI-OR0026

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

Medium

Description

The ROWID statement is currently not supported in this version.

Example Code

Oracle:

SELECT QUERY_NAME.ROWID from TABLE1;
Copy

Snowflake Scripting:

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

Recommendations

SSC-EWI-OR0103

Severity

High

Description

Snowflake scripting has limited support for FOR LOOP statements compared to Oracle. While Oracle supports multiple condition types (such as boolean expressions, collections, and records), Snowflake only allows FOR LOOP with integer ranges as bounds. Any other loop formats will be marked as unsupported and need to be manually converted.

Oracle iteration control clauses that Snowflake’s FOR LOOP does not support:

  • single_expression_control: Evaluates a single expression

  • values_of_control: Processes individual values from a collection

  • indices_of_control: Iterates through array indices

  • pairs_of_control: Processes key-value pairs from a collection

The cursor_iteration_control feature is not currently supported. However, you can convert it to a CURSOR FOR LOOP by removing the parentheses from the expression.

FOR i IN (cursor_variable) LOOP NULL; END LOOP;

Should be changed to:

FOR i IN cursor_variable LOOP NULL; END LOOP;

Example Code

Input Code Oracle:

CREATE OR REPLACE PROCEDURE P3
AS
TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
l_employee_values   values_aat;
BEGIN
FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
    NULL;
END LOOP;

FOR i IN VALUES OF l_employee_values LOOP
    NULL;
END LOOP;
END;
Copy

Output Code:

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
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
        l_employee_values VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'values_aat' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
            NULL;
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

        FOR i IN VALUES OF :l_employee_values LOOP
            NULL;
        END LOOP;
    END;
$$;
Copy

Recommendations

  • Modify the FOR LOOP condition or select an alternative LOOP type to achieve the same result.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0090

Severity

Medium

Parts of the code output have been removed to make it easier to understand.

Description

This error occurs when using an Oracle-style OUTER JOIN with the (+) operator within a BETWEEN clause that contains multiple tables. Snowflake does not support this specific syntax combination. The issue specifically arises when the BETWEEN clause references columns from different tables.

Example Code

Input Code Oracle:

SELECT
*
FROM
hr.countries c, hr.regions r,  hr.locations l WHERE
l.location_id  BETWEEN r.region_id(+) AND c.region_id(+);
Copy

Output Code:

SELECT
*
FROM
hr.countries c,
hr.regions r,
hr.locations l WHERE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0090 - INVALID NON-ANSI OUTER JOIN BETWEEN PREDICATE CASE FOR SNOWFLAKE. ***/!!!
l.location_id  BETWEEN r.region_id(+) AND c.region_id(+);
Copy

Recommendations

SSC-EWI-OR0123

Severity

Medium

Description

A database link reference has been removed from the object name since Snowflake does not support database links. Only the portion of the name before the @ symbol is retained.

Example Code

Input Code:

-- Creation of the database link
CREATE DATABASE LINK mylink
    CONNECT TO user1 IDENTIFIED BY password1
    USING 'connection_str';

-- Statements that use the database link we created
SELECT * FROM employees@mylink;

INSERT INTO employees@mylink
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');

UPDATE employees@mylink SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM employees@mylink
    WHERE employee_id = 999;
Copy

Output Code:

- Creation of the database link
-** SSC-OOS - OUT OF SCOPE CODE UNIT. CREATE DATABASE LINK IS OUT OF TRANSLATION SCOPE. **
--CREATE DATABASE LINK mylink
--    CONNECT TO user1 IDENTIFIED BY password1
--    USING 'connection_str'

    -- Statements that use the database link we created
SELECT * FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
    employees;

INSERT INTO
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
employees
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', 'sclaus@oracle.com', CURRENT_TIMESTAMP(), 'SH_CLERK');

UPDATE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
employees
    SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] ***/!!!
    employees
    WHERE employee_id = 999;
Copy

Recommendations

  • Ensure all Database Links have unique names. If multiple Database Links share the same name and the code is migrated multiple times, the External Web Interface (EWI) may process information incorrectly based on which Database Link is processed first.

  • Relocate database objects referenced by Database Links to the same Snowflake database instance you are using.

  • For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0032

Severity

Medium

Description

This error occurs when you attempt to use an unsupported parameter in a function.

Example Code

Input Code:

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

Output Code:

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

Recommendations

  • The function is converted to a user-defined function (UDF) template that you can customize to match the parameter’s behavior.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-OR0006

This Early Warning Indicator (EWI) is no longer in use. Please see the SSC-FDM-OR0047 documentation for current information.

Severity

Low

Description

The TIMESTAMP_OUTPUT_FORMAT session parameter should be set to ‘DD-MON-YY HH24.MI.SS.FF AM TZH:TZM’ to ensure timestamps are displayed in the correct format and maintain equivalence across systems.

Example Code

Input Code:

SELECT SYSTIMESTAMP FROM DUAL;
Copy

Example of default TIMESTAMP output in Oracle

January 13, 2021 04:18:37.288656 PM UTC

Output Code:

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

Example of default TIMESTAMP output in Snowflake

2021-01-13 08:18:19.720 -080

Recommendations

To modify how timestamps are displayed in Snowflake, execute this query:

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'DD-MON-YY HH24.MI.SS.FF AM TZH:TZM';
Copy

SSC-EWI-OR0057

Severity

Critical

Parts of the code output have been removed to make it easier to understand.

Description

Nested procedures or functions (procedures/functions defined within other procedures/functions) cannot be translated at this time. This feature will be available in a future release.

Example Code

Input Code:

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

Output Code:

--** SSC-FDM-0029 - 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":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
        FUNCTION FOO2 return integer;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
        FUNCTION FOO2 RETURN INTEGER AS
        BEGIN
           RETURN 123;
        END;
    BEGIN
        RETURN FOO2() + 456;
    END;
$$;
Copy

Recommendations

SSC-EWI-OR0047

Severity

Low

Parts of the code have been omitted to keep the example clear and concise.

Description

This warning appears when the Oracle TO_NCHAR function has been converted to Snowflake’s TO_VARCHAR function during the migration process.

There may be situations where the code transformation results in compilation errors or produces unexpected output that differs from the original.

Example Code

Input Code:

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

Output Code:

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

The code shown previously will generate an error when executed in Snowflake.

Not all scenarios will result in errors.

Input Code:

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

Output Code:

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

The final example runs successfully in Snowflake and produces the same output when executed.

Recommendations

SSC-EWI-OR0016

Severity

Medium

Description

The XML functions listed below are currently not supported:

  • The EXTRACT function retrieves data from XML documents

  • EXTRACTVALUE retrieves specific values from XML elements

  • XMLSEQUENCE converts XML data into a table format

  • XMLTYPE defines a data type for storing XML content

Example Code

Input Code:

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>')));
Copy

Output Code:

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>')));
Copy

Recommendations

SSC-EWI-OR0133

Severity

Medium

Description

When converting an OPEN FOR statement, SC creates a cursor assignment using the same name as the original cursor variable, along with additional statements to replicate its behavior. However, if the original code contains multiple OPEN FOR statements using the same cursor variable, the converted code will have multiple cursor assignments with identical names. This will result in compilation errors in Snowflake.

Example code

Input code

CREATE OR REPLACE PROCEDURE open_for_procedure
AS
	query1 VARCHAR(200) := 'SELECT 123 FROM dual';
	query2 VARCHAR(200) := 'SELECT 456 FROM dual';
	my_cursor_variable SYS_REFCURSOR;
BEGIN
	OPEN my_cursor_variable FOR query1;
	OPEN my_cursor_variable FOR query2;
END;
Copy

Output code

CREATE OR REPLACE PROCEDURE open_for_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		query1 VARCHAR(200) := 'SELECT 123 FROM dual';
		query2 VARCHAR(200) := 'SELECT 456 FROM dual';
		my_cursor_variable_res RESULTSET;
	BEGIN
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query1
		);
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query2
		);
		!!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'my_cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
	END;
$$;
Copy

Related EWI

  1. SSC-EWI-0030: Dynamic SQL is detected in the following statement.

Recommendations

  • To fix compilation errors in the output code, rename any cursor assignments that display the SSC-EWI-OR0133 warning message.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com