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:
Lock the table
Find either:
The maximum identity column value (for increasing sequences)
The minimum identity column value (for decreasing sequences)
Set this value as the sequence generator’s high water mark
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;
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"}}';
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
$$;
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
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 justcolumn_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;
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;
$$;
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" ...
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;
Output Code:¶
SELECT
TO_NUMBER('12.48', '99.99', 38, 2)
FROM DUAL;
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
Output Code:¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0031 - 'NLS_SORT' SYS_CONTEXT PARAMETER NOT SUPPORTED IN SNOWFLAKE ***/!!!
SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;
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;
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;
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;
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;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
Learn how to handle files in Snowflake by reviewing the documentation here.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
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;
COL1|COL2|COL3|
-+-+-+
555| 560| 600|
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;
|COL1|COL2|COL3|
|-|-|-|
|555 |555 |555 |
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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);
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);
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
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') );
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') );
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') );
URL |
+
https://thecompany.snowflakecomputing.com/api/files/CODETEST/PUBLIC/MY_STAGE/%2Fmydirectory%2Fmyfile.jpg|
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.
Convert the data type to one that is compatible with the target system.
For additional assistance, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
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;
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;
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
);
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
Output Code:¶
SELECT
REGEXP_INSTR('CORPORATE FLOOR','OR', -3, 2) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0020 - NEGATIVE VALUES NOT SUPPORTED FOR FUNCTION ***/!!! FROM DUAL;
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;
/
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;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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
;
$$;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
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';
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';
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
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);
Output Code:¶
SELECT * FROM
table1
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0010 - PARTITIONS CLAUSES ARE HANDLED BY SNOWFLAKE. IT REQUIRES MANUAL FIX ***/!!!
PARTITION(col1);
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;
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;
$$;
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;
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;
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;
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;
$$;
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;
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"}}';
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"}}';
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;
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;
Recommendations¶
If your data hierarchy contains cycles, please refer to this article for guidance on how to handle them.
For additional assistance, contact our support team at snowconvert-support@snowflake.com
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;
Output Code:¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0029 - DEFAULT ON CONVERSION ERROR NOT SUPPORTED IN SNOWFLAKE ***/!!! TO_NUMBER('2,00') "Value" FROM DUAL;
Recommendations¶
Consider creating a User-Defined Function (UDF) to replicate the functionality of the
DEFAULT
value when handlingON 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;
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`)*/
;
$$;
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);
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);
Recommendations¶
Information typically found in Oracle Built-In views is available in Snowflake through either the Information Schema or by using the SHOW command.
For additional assistance, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
Use Snowflake’s FLATTEN function as an alternative to JSON_TABLE functionality.
For additional assistance, contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
Recommendations¶
Consider changing the variable data type or use Snowflake’s semi-structured data types to replicate the desired behavior.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
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;
Output Code:¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0008 - UNKNOWN FORMAT, MAY HAVE UNEXPECTED BEHAVIOR ***/!!!
TO_CHAR(DATE '1998-12-25','iw-iyyy'') FROM DUAL;
Note: The date format ‘iw-iyyy’ is not supported.
Recommendations¶
For a list of supported timestamp formats, please refer to the documentation.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
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;
$$;
Recommendations¶
To manually migrate procedures, create a variable and assign the expression to it.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
$$;
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;
Snowflake Scripting:¶
select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0069 - THE SEQUENCE CURRVAL PROPERTY IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
seq1.currval from dual;
Recommendations¶
For guidance on handling scenarios involving the CURRVAL property, please refer to Snowflake’s documentation.
For additional assistance, contact our support team at snowconvert-support@snowflake.com
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;
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;
Recommendation¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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);
Output Code:¶
--!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
--CREATE TYPE type6 UNDER type5(COL1 INTEGER)
;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
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;
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;
Recommendations¶
No further action is needed from your side.
For additional assistance, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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
;
$$;
For better readability, some sections of the output code have been omitted.
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
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;
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;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
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;
$$;
Related EWI¶
SSC-EWI-OR0036: Warning: Arithmetic operations between string and date data types may produce unexpected results.
SSC-PRF-0004: This code contains cursor-based loops which may impact performance.
SSC-EWI-OR0110: The FOR UPDATE clause is not currently supported in Snowflake.
Recommendations¶
Rewrite the query using standard
UPDATE
orDELETE
statements. Include specific column conditions in theWHERE
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;
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;
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)
();
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)
();
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
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;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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:
When referencing a Type Access that hasn’t been resolved
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;
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;
$$;
Recommendations¶
Check if the referenced type definition contains column definitions.
For additional assistance, please contact us at snowconvert-support@snowflake.com
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;
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;
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¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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);
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);
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
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;
Snowflake Scripting:¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0026 - ROWID NOT SUPPORTED ***/!!!
QUERY_NAME.ROWID from
TABLE1;
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
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 expressionvalues_of_control
: Processes individual values from a collectionindices_of_control
: Iterates through array indicespairs_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;
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;
$$;
Recommendations¶
Modify the
FOR LOOP
condition or select an alternativeLOOP
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(+);
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(+);
Recommendations¶
Convert the Outer Join to ANSI syntax manually.
For additional assistance, please contact us at snowconvert-support@snowflake.com
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;
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;
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;
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;
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;
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;
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';
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
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
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;
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;
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;
The final example runs successfully in Snowflake and produces the same output when executed.
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
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>')));
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>')));
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
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;
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;
$$;
Related EWI¶
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