SnowConvert AI - Oracle Functional Differences¶
SSC-FDM-OR0001¶
Bemerkung
This FDM was added for an old version of Oracle SnowConvert AI. Currently, it is deprecated.
Beschreibung¶
This error is related to the Assessment report file. It appears when occurs an error in writing the assessment details report file.
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0002¶
The sequence start value exceeds the max value allowed by Snowflake.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0068 documentation
Beschreibung¶
This error appears when the START WITH
statement value exceeds the maximum value allowed by Snowflake. What Snowflake said about the start value is: Specifies the first value returned by the sequence. Supported values are any value that can be represented by a 64-bit two’s compliment integer (from -2^63
to 2^63-1
). So according to the previously mentioned, the max value allowed is 9223372036854775807 for positive numbers and 9223372036854775808 for negative numbers.
Beispielcode¶
Eingabecode:¶
CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;
CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;
Generierter Code:¶
CREATE OR REPLACE SEQUENCE SEQUENCE1
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH 9223372036854775808
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';
CREATE OR REPLACE SEQUENCE SEQUENCE2
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH -9223372036854775809
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';
Best Practices¶
It can be recommended to just reset the sequence and modify its usage too. NOTE: the target column must have enough space for holding this value.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0003¶
Search clause removed from the with element statement.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0038 documentation
Beschreibung¶
The search_clause
is employed to define the order in which rows are processed in a SELECT statement. This functionality allows for a customized traversal of the data, ensuring that the results are returned in a specific sequence based on the specified criteria. It is important to note, however, that this behavior, characterized by the search_clause
, is not supported in Snowflake.
In databases such as Oracle, the search_clause
is commonly used in conjunction with recursive queries or common table expressions (CTEs) to influence the sequence in which hierarchical data is explored. By designating a particular column or set of columns in the search_clause
, you can control the depth-first or breadth-first traversal of the hierarchy, impacting the order in which rows are processed.
In Snowflake, search_clause
message will be generated, and the search_clause
is subsequently eliminated.
Beispielcode¶
Eingabecode:¶
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;
Generierter Code:¶
WITH dup_hiredate(eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS
(
SELECT aValue from
atable
) /*** SSC-FDM-OR0003 - SEARCH CLAUSE REMOVED FROM THE WITH ELEMENT STATEMENT ***/
SELECT aValue from
atable;
Empfehlung¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0004¶
Siblings keyword removed from the order by clause because Snowflake does not support it.
Beschreibung¶
In Oracle, the ORDER BY SIBLINGS clause can be used in hierarchical queries to preserve the order of the data given by the hierarchy, while applying a reorder of the values that are siblings in the same hierarchy. This is not supported in Snowflake.
Beispielcode¶
Eingabecode:¶
SELECT LEVEL,
LPAD(' ', 2 * (LEVEL - 1)) || NAME AS FORMATTED_NAME,
JOB_TITLE
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY NAME;
Generierter Code:¶
SELECT LEVEL,
NVL(
LPAD(' ', 2 * (
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!LEVEL - 1)) :: STRING, '') || NVL(NAME :: STRING, '') AS FORMATTED_NAME,
JOB_TITLE
FROM
EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY
PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER BY
NAME /*** SSC-FDM-OR0004 - SIBLINGS KEYWORD REMOVED FROM ORDER BY CLAUSE BECAUSE SNOWFLAKE DOES NOT SUPPORT IT ***/;
While the exact same ordering achieved with the SIBLINGS clause might not be accessible, there are a few alternatives to get a similar result.
Embed the query within an outer query that applies the desired sorting using
ORDER BY
.Create a CTE with the hierarchical query using
CONNECT BY
and reference the CTE in a subsequent query to applyORDER BY
for sibling sorting (rows at the same level).
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0005¶
Synonyms are not supported in Snowflake but references to this synonym were changed by the original object name.
Beschreibung¶
Synonyms are not supported in Snowflake. The synonyms are replaced by the original name.
Beispielcode¶
Eingabecode:¶
CREATE TABLE TABLE1
(
COLUMN1 NUMBER
);
CREATE OR REPLACE SYNONYM B.TABLE1_SYNONYM FOR TABLE1;
SELECT * FROM B.TABLE1_SYNONYM WHERE B.TABLE1_SYNONYM.COLUMN1 = 20;
Generierter Code:¶
CREATE OR REPLACE TABLE TABLE1
(
COLUMN1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- --** SSC-FDM-OR0005 - SYNONYMS NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS SYNONYM WERE CHANGED BY THE ORIGINAL OBJECT NAME. **
-- CREATE OR REPLACE SYNONYM B.TABLE1_SYNONYM FOR TABLE1
;
SELECT * FROM
TABLE1
WHERE
TABLE1.COLUMN1 = 20;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0006¶
Constraint state removed from not null inline constraint.
Beschreibung¶
This warning occurs when the not null column constraint contains one of the following Oracle constraint states as part of the column inline definition:
[ RELY | NORELY | RELY DISABLE | RELY ENABLE | VALIDATE | NOVALIDATE ]
Snowflake does not support these states; therefore, they will be removed from the NOT NULL
inline constraint.
Example Code ¶
Eingabecode:¶
CREATE TABLE Table1(
col1 INT NOT NULL RELY
);
Generierter Code:¶
CREATE OR REPLACE TABLE Table1 (
col1 INT NOT NULL /*** SSC-FDM-OR0006 - CONSTRAINT STATE RELY REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0007¶
Snowflake does not support the versioning of objects. Developers should consider alternate approaches for code versioning.
Beschreibung¶
Snowflake doesn’t support the versioning of objects. The modifier EDITINONABLE or NONEDITIONABLE is removed in the converted code and a warning is added.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE EDITIONABLE PROCEDURE FUN1 (n number)is
l_result number;
begin
DELETE FROM employees;
end;
Generierter Code:¶
--** SSC-FDM-OR0007 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. **
CREATE OR REPLACE PROCEDURE FUN1 (n NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
l_result NUMBER(38, 18);
BEGIN
DELETE FROM
employees;
END;
$$;
Best Practices¶
The user should consider alternate approaches for code versioning.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0008¶
Set Quantifier Not Supported
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0071 documentation
Beschreibung¶
Quantifier ‚all‘ is not supported in Snowflake. The modifier is removed from the source code, and a warning is added; the resulting code may behave unexpectedly.
Beispielcode¶
Eingabecode:¶
SELECT location_id FROM locations
MINUS ALL
SELECT location_id FROM departments;
Generierter Code:¶
SELECT location_id FROM
locations
--** SSC-FDM-OR0008 - QUANTIFIER 'ALL' NOT SUPPORTED FOR THIS SET OPERATOR, RESULTS MAY DIFFER **
MINUS
SELECT location_id FROM
departments;
In Snowflake, the INTERSECT
and MINUS/EXCEPT
operators will always remove duplicate values.
Best Practices¶
Check alternatives in Snowflake to emulate the functionality of the „all“ quantifier.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0009¶
SQL implicit cursor values may differ.
Beschreibung¶
Bemerkung
Generate Procedures and Macros using JavasScript as the target language adding the following flag -t JavaScript
or --PLTargetLanguage JavaScript
Bemerkung
Some parts in the output code are omitted for clarity reasons.
This EWI is shown when SQL implicit cursor value is used. This is because Oracle uses different values depending on the type of query. For example, for SELECT
the value used to set SQL implicit cursor values are the number of rows returned by the query. When the query type is UPDATE/CREATE/DELETE/INSERT
the value used is the number of rows affected, this is the main reason why this EWI is displayed.
Beispielcode¶
Eingabecode:¶
-- Additional Params: -t JavaScript
--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE AUTHID DEFINER IS
stmt_no POSITIVE;
BEGIN
IF SQL%ROWCOUNT = 0 THEN
EXIT ;
END IF;
IF SQL%ISOPEN THEN
EXIT ;
END IF;
IF SQL%FOUND THEN
EXIT ;
END IF;
IF SQL%NOTFOUND THEN
EXIT ;
END IF;
END;
Generierter Code:¶
-- Additional Params: -t JavaScript
--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
//AUTHID DEFINER
null
// SnowConvert AI Helpers Code section is omitted.
let STMT_NO = new POSITIVE();
if (SQL.ROWCOUNT /*** SSC-FDM-OR0009 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/ == 0) {
break;
}
if (SQL.ISOPEN) {
break;
}
if (SQL.FOUND) {
break;
}
if (SQL.NOTFOUND) {
break;
}
$$;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0010¶
NUMBER datatype smaller precision was increased to match scale.
Beschreibung¶
The NUMBER
data type stores fixed and floating-point numbers. This data is portable among different operating systems running the Oracle Database. The NUMBER
data type is recommended for most cases in which you must store numeric data. The syntax is the following NUMBER (X, Y)
, where X is the precision and Y is the scale.
For example, NUMBER(5, 3)
is a number that has 2 digits before the decimal and 3 digits after the decimal, just like the following:
12.345
Another important considerations:
Scale Y specifies the maximum number of digits to the right of the decimal point.
Scale-Precision Y-X specifies the minimum number of zeros present after the decimal point.
This message is shown when a NUMBER
has a smaller precision than its scale. Snowflake does not support this feature, and this message is used to indicate that the precision’s value was increased to maintain equivalence.
Note
Please consider that there are cases where this issue can either stack alongside other known transformations or not happen at all. For example, cases where the scale is replaced by nineteen and the former precision is greater than nineteen; will NOT show this message.
Beispielcode¶
Eingabecode:¶
Queries¶
CREATE TABLE SampleNumberTable(Col1 NUMBER(4, 5));
INSERT INTO SampleNumberTable (Col1)
VALUES (0.00009);
INSERT INTO SampleNumberTable (Col1)
VALUES (0.000021);
INSERT INTO SampleNumberTable (Col1)
VALUES (0.012678912);
SELECT * FROM SampleNumberTable;
Result¶
Col1 |
-------+
0.00009|
0.00002|
0.01268|
Generierter Code:¶
Queries¶
CREATE OR REPLACE TABLE SampleNumberTable (Col1 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO SampleNumberTable(Col1)
VALUES (0.00009);
INSERT INTO SampleNumberTable(Col1)
VALUES (0.000021);
INSERT INTO SampleNumberTable(Col1)
VALUES (0.012678912);
SELECT * FROM
SampleNumberTable;
Result¶
Col1 |
-------+
0.00009|
0.00002|
0.01268|
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0011¶
The boolean argument was removed because the „add to stack“ options is not supported.
Beschreibung¶
This warning is displayed when the third optional argument of RAISE_APLICATION_ERROR was removed during the migration. This functionality is not supported by Snowflake.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE FUNCTION TEST(SAMPLE_A IN NUMBER DEFAULT NULL,
SAMPLE_B IN NUMBER DEFAULT NULL)
RETURN NUMBER
AS
BEGIN
raise_application_error(-20001, 'First exception message', FALSE);
RETURN 1;
END TEST;
Generierter Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE TEST (SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
SAMPLE_B NUMBER(38, 18) DEFAULT NULL)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "07/02/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'FIRST EXCEPTION MESSAGE');
BEGIN
--** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT FALSE WAS REMOVED. **
RAISE FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
RETURN 1;
END;
$$;
Best Practices¶
No end-user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0012¶
COMMIT and ROLLBACK statements require adequate setup to perform as intended.
Beschreibung¶
COMMIT and ROLLBACK statements require adequate setup to perform as intended in Snowflake. The following instruction needs to be executed in Snowflake to simulate the correct functionality of these statements:
ALTER SESSION SET AUTOCOMMIT = false;
Beispielcode¶
Eingabecode¶
COMMIT;
ROLLBACK;
Generated Code¶
--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;
--** SSC-FDM-OR0012 - ROLLBACK REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
ROLLBACK;
Best Practices¶
Execute the query mentioned in the description section before you start to execute your code.
If you need more support, you can email us at snowconvert-support@snowflake.com.
SSC-FDM-OR0013¶
The cycle clause was is not supported in Snowflake.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0039 documentation.
Beschreibung¶
This message is shown when SnowConvert AI finds a query with a CYCLE clause. Which is not supported in Snowflake, so it is commented out from the code.
This clause marks when there is a recursion.
For more details see the documentation about the clause functionality.
Beispielcode¶
CONNECT BY¶
Eingabecode:¶
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;
Generierter 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
--** SSC-FDM-OR0013 - CYCLE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
CONNECT BY
A.C = 0 START WITH A.B = 1;
Best Practices¶
If there are cycles in the data hierarchy, you can review this article to deal with them.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0014¶
Foreign key data type mismatch.
Beschreibung¶
This error happens when there is a mismatch in a foreign key data type.
Beispielcode¶
Eingabecode:¶
CREATE TABLE "MyDb"."MyTable"
(
"COL1" NUMBER,
CONSTRAINT "PK" PRIMARY KEY ("COL1")
);
CREATE TABLE "MyDb"."MyTable1"
(
"COL1" NUMBER(*,0),
CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1")
);
Generierter Code:¶
CREATE OR REPLACE TABLE "MyDb"."MyTable"
(
"COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
CONSTRAINT "PK" PRIMARY KEY ("COL1")
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE TABLE "MyDb"."MyTable1"
(
"COL1" NUMBER(38) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
ALTER TABLE "MyDb"."MyTable1"
ADD
--** SSC-FDM-OR0014 - FOREIGN KEY DATA TYPE MISMATCH **
CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1");
Bemerkung
Note that „MyDb“.“MyTable1“.COL1 and „MyDb“.“MyTable“.COL1 are of different types and the ERROR is displayed.
Best Practices¶
If there are cycles in the data hierarchy, you can review this article to deal with them.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0015¶
LENGTHB transformed to OCTET_LENGTH results may vary due to memory management of DBMS.
Beschreibung¶
This issue happens when there is an invocation to LENGTHB function that returns the size of a column or literal in bytes. This function is transformed into OCTET_LENGTH Snowflake’s function.
When the parameter to the function is a column, the result will be the size of the value that the column has, this size may vary from Oracle to Snowflake, the type of the column plays an important role in the result returned by the function.
Beispielcode¶
Eingabecode:¶
Queries¶
CREATE TABLE char_table
(
char_column1 CHAR(15)
);
INSERT INTO char_table VALUES ('Hello world');
SELECT char_column1, LENGTHB(char_column1), LENGTH('Hello world') FROM char_table;
Result¶
|CHAR_COLUMN1 |LENGTHB(CHAR_COLUMN1)|LENGTH('HELLOWORLD')|
|---------------|---------------------|--------------------|
|Hello world |15 |11 |
Generierter Code:¶
Queries¶
CREATE OR REPLACE TABLE char_table
(
char_column1 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO char_table
VALUES ('Hello world');
SELECT char_column1,
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/, LENGTH('Hello world') FROM
char_table;
Result¶
|CHAR_COLUMN1|OCTET_LENGTH(CHAR_COLUMN1)|LENGTH('HELLO WORLD')|
|------------|--------------------------|---------------------|
|Hello world |11 |11 |
Best Practices¶
Manually check the data types used.
Check the encoding of the columns used because OCTET_LENGTH can return bigger sizes when the string contains Unicode code points.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0016¶
COMMIT and ROLLBACK options were removed because Snowflake does not require them
Beschreibung¶
COMMIT and ROLLBACK statement options are being removed because Snowflake does not require them.
Beispielcode¶
Eingabecode¶
COMMIT WORK FORCE '22.57.53';
ROLLBACK WORK FORCE '22.57.53';
Generated Code¶
--** SSC-FDM-OR0016 - COMMIT OPTIONS REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM **
--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT WORK;
--** SSC-FDM-OR0016 - ROLLBACK OPTIONS REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM **
--** SSC-FDM-OR0012 - ROLLBACK REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
ROLLBACK WORK;
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0017¶
DBTimezone was removed to use the default value of the Timestamp.
Beschreibung¶
DBTIMEZONE keyword was removed from the AT TIME ZONE expression.
Beispielcode¶
Eingabecode:¶
SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE DBTIMEZONE FROM DUAL;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0017 - DBTIMEZONE WAS REMOVED TO USE THE DEFAULT VALUE OF THE TIMESTAMP **
TO_TIMESTAMP_LTZ( TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;
Best Practices¶
You may need to set the TIMEZONE session parameter in order to get equal results.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0018¶
Merge statement may do not work as expected
Beschreibung¶
This warning is used to indicate that the Snowflake merge statement may have some functional differences compared to Oracle.
Beispielcode¶
Eingabecode:¶
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
DELETE where pt.title = 'Mrs.'
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title)
WHERE ps.title = 'Mr';
Generierter Code:¶
--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "people_target", "people_source" **
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED AND pt.title = 'Mrs.' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED AND ps.title = 'Mr' THEN
INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
Best Practices¶
If you are getting different results compared to Oracle, consider the following:
For execution order prioritization, go to the next link to get more information.
Execute the skipped DML statements outside (before or after accordingly) the merge statement.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0019¶
Window frame output may not be equivalent
Beschreibung¶
This warning is added when a ROWS window frame unit is found within the source code.
ROWS works by using physical row numbers for its computing, which may differ once it is migrated to the target platform. Manually adding extra ORDER BY clauses can help mitigate or remove this issue.
Bemerkung
Note that as the Oracle documentation states:
„The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause
to achieve this unique ordering.“
According to this is recommended to check if the function returned deterministic results beforehand to avoid any issues.
Example Code ¶
Eingabecode:¶
SELECT
SUM(C_BIRTH_DAY)
OVER (
ORDER BY C_BIRTH_COUNTRY
ROWS UNBOUNDED PRECEDING) AS MAX1
FROM WINDOW_TABLE;
Generierter Code:¶
SELECT
SUM(C_BIRTH_DAY)
OVER (
ORDER BY C_BIRTH_COUNTRY ROWS UNBOUNDED PRECEDING /*** SSC-FDM-OR0019 - WINDOW FRAME OUTPUT MAY NOT BE EQUIVALENT ***/) AS MAX1
FROM
WINDOW_TABLE;
Best Practices¶
Ensure deterministic ordering for rows to ensure deterministic outputs when running in Snowflake.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0020¶
PRAGMA EXCEPTION_INIT is not supported.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0051 documentation.
Beschreibung¶
This warning is added when PRAGMA EXCEPTION_INIT function is invoked within a procedure. Exception Name and SQL Code of the exceptions are set in the RAISE function. When it is converted to Snowflake Scripting, the SQL Code is added to the Exception declaration, however, some code values may be invalid in Snowflake Scripting.
Example Code ¶
Eingabecode:¶
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;
/
Generierter Code:¶
Snowflake Scripting¶
CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_SAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/!!!
AS
$$
DECLARE
--** SSC-FDM-OR0023 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS **
NEW_EXCEPTION EXCEPTION;
--** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
NEW_EXCEPTION2 EXCEPTION (-20100, '');
--** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 );
BEGIN
IF (true) THEN
RAISE NEW_EXCEPTION;
END IF;
EXCEPTION
WHEN NEW_EXCEPTION THEN
--Handle Exceptions
NULL;
END;
$$;
Best Practices¶
No end-user action is required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0021¶
For Loop With Float Number As Bound May Not Behave Correctly In Snowflake Scripting
Beschreibung¶
Snowflake Scripting only allows an INTEGER
or an expression that evaluates to an INTEGER
as a bound for the FOR LOOP
condition. Floating numbers will be rounded up or down and alter the original bound.
The lower bound will be rounded to the closest integer number. For example:
3.1 -> 3, 6.7 -> 7, 4.5 -> 5
However the upper bound will be truncated to the closest lower integer. For example:
3.1 -> 3, 6.7 -> 6, 4.5 -> 4
Snowflake Scripting¶
CREATE OR REPLACE PROCEDURE p1()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
var1 VARCHAR DEFAULT '';
var2 VARCHAR DEFAULT '';
var3 VARCHAR DEFAULT '';
BEGIN
--Loop 1
FOR i IN 1.2 TO 5.2 DO
var1 := var1 || ' ' || i::VARCHAR;
END FOR;
--Loop 2
FOR i IN 1.7 TO 5.5 DO
var2 := var2 || ' ' || i::VARCHAR;
END FOR;
--Loop 3
FOR i IN 1.5 TO 5.8 DO
var3 := var3 || ' ' || i::VARCHAR;
END FOR;
RETURN ' Loop1: ' || var1 ||
' Loop2: ' || var2 ||
' Loop3: ' || var3;
END;
$$;
CALL p1();
Result¶
P1 |
--------------------------------------------------+
Loop1: 1 2 3 4 5 |
Loop2: 2 3 4 5 |
Loop3: 2 3 4 5 |
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE p1
AS
BEGIN
FOR i NUMBER(5,1) IN 1.2 .. 5.7 LOOP
NULL;
END LOOP;
END;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE p1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "07/02/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
BEGIN
--** SSC-FDM-OR0021 - FOR LOOP WITH FLOAT NUMBER AS LOWER OR UPPER BOUND MAY NOT BEHAVE CORRECTLY IN SNOWFLAKE SCRIPTING **
--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
FOR i IN 1.2 TO 5.7
--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
LOOP
NULL;
END LOOP;
END;
$$;
Best Practices¶
Rewrite the FOR LOOP condition so it uses integers.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0022¶
For Loop With Multiple Conditions Is Currently Not Supported By Snowflake Scripting. Only First Condition Is Used
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0100 documentation.
Beschreibung¶
Oracle allows multiple conditions in a single FOR LOOP
however, Snowflake Scripting only allows one condition per FOR LOOP
. Only the first condition is migrated and the others are ignored during transformation.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
NULL;
END LOOP;
END;
Generierter 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
--** SSC-FDM-OR0022 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
FOR i IN REVERSE 1 TO 3 LOOP
NULL;
END LOOP;
END;
$$;
Best Practices¶
Separate the
FOR LOOP
into different loops or rewrite the condition.If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0023¶
The exception code exceeds the Snowflake Scripting limit
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0099 documentation.
Beschreibung¶
This warning appears when an exception declaration error code exceeds the Snowflake Scripting exception number limits. The number must be an integer between -20000 and -20999.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE procedure_exception
IS
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception, -19000 );
BEGIN
NULL;
END;
Generierter 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
--** SSC-FDM-OR0023 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS **
my_exception EXCEPTION;
--** SSC-FDM-OR0020 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
PRAGMA EXCEPTION_INIT ( my_exception, -19000 );
BEGIN
NULL;
END;
$$;
Best Practices¶
Check if the exception code is between the limits allowed by Snowflake Scripting, if not change it for another exception number available.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0024¶
Columns from expression not found
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0002 documentation.
Beschreibung¶
This error happens when the columns of a Select Expression were unable to be resolved, usually when it either refers to a Type Access whose reference wasn’t resolved or a column with a User Defined Type whose columns haven’t been defined; such as a Type Without Body or Object Type with no columns.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE record_unknown_table_proc
AS
unknownTable_variable_rowtype unknownTable%ROWTYPE;
BEGIN
INSERT INTO MyTable values unknownTable_variable_rowtype;
END;
Generierter 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 /*** SSC-FDM-OR0024 - COLUMNS FROM EXPRESSION unknownTable%ROWTYPE NOT FOUND ***/;
END;
$$;
Best Practices¶
Verify that the type definition that was referenced does have columns within it.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0025¶
Not Null constraint is not supported in Snowflake Procedures
Beschreibung¶
The Oracle variable declaration NOT NULL
constraint is not supported in variable declarations inside procedures in Snowflake.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE PROC04
IS
var3 FLOAT NOT NULL := 100;
BEGIN
NULL;
END;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE PROC04 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
var3 FLOAT := 100 /*** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
BEGIN
NULL;
END;
$$;
Best Practices¶
No end-user action is required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0026¶
Type not supported in cast operation.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0045 documentation.
Beschreibung¶
This error happens when a type is not supported in a cast operation.
Beispiel¶
Eingabecode:¶
select cast(' $123.45' as number, 'L999.99') from dual;
Generierter Code:¶
select
--** SSC-FDM-OR0026 - CAST TYPE NOT SUPPORTED **
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0011 - THE FORMAT PARAMETER ' $123.45' IS NOT SUPPORTED ***/!!!
cast(' $123.45' as NUMBER(38, 18) , 'L999.99') from dual;
Zugehörige EWIs¶
SSC-EWI-OR0011: The format parameter is not supported.
Best Practices¶
The cast is converted to a user-defined function (UDF/Stub), so you can modify it to emulate the behavior of the cast function.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0027¶
DEFAULT ON CONVERSION ERROR is not supported.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0029 documentation
Beschreibung¶
Default on conversion error not supported in Snowflake
Beispielcode¶
Eingabecode:¶
SELECT TO_NUMBER('2,00' DEFAULT 0 ON CONVERSION ERROR) "Value" FROM DUAL;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0027 - DEFAULT ON CONVERSION ERROR NOT SUPPORTED IN SNOWFLAKE IN SNOWFLAKE **
TO_NUMBER('2,00') "Value" FROM DUAL;
Best Practices¶
You might create UDF to emulate the behavior of
DEFAULT
valueON CONVERSION ERROR
.If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0028¶
SYS_CONTEXT parameter is not supported.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0031 documentation.
Beschreibung¶
This error happens when a SYS_CONTEXT function parameter is not supported.
Beispielcode¶
Eingabecode:¶
SELECT SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0028 - 'NLS_SORT' SYS_CONTEXT PARAMETER NOT SUPPORTED IN SNOWFLAKE **
SYS_CONTEXT ('USERENV', 'NLS_SORT') FROM DUAL;
Best Practices¶
The function is converted to a user defined function(stub), so you can modify it to emulate the behavior of the SYS_CONTEXT parameter.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0029¶
This ALTER SESSION configuration is not supported in Snowflake.
Beschreibung¶
A clause or configuration of the ALTER SESSION statement is not currently supported.
Beispielcode¶
Eingabecode:¶
ALTER SESSION SET SQL_TRACE TRUE;
Generierter Code:¶
----** SSC-FDM-OR0029 - THIS ALTER SESSION CONFIGURATION IS NOT SUPPORTED IN SNOWFLAKE **
--ALTER SESSION SET SQL_TRACE TRUE
;
Best Practices¶
For session variables, you can check the Snowflake documentation to find an equivalent.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0030¶
ROWID pseudocolumn is not supported in Snowflake
Beschreibung¶
When ROWID is used as a pseudocolumn in a query it is transformed to null in order to avoid runtime errors and the EWI is added. There is still no transformation to emulate the functionality.
Beispielcode¶
Oracle-Eingabecode:¶
SELECT ROWID FROM T1;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0030 - ROWID PSEUDOCOLUMN IS NOT SUPPORTED IN SNOWFLAKE, IT WAS CONVERTED TO NULL TO AVOID RUNTIME ERRORS **
'' AS ROWID
FROM
T1;
Best Practices¶
No end-user action is required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0031¶
The error logging clause in DML statements is not supported by Snowflake
Beschreibung¶
This error is used to advise that the error_logging clause in Oracle’s DML statements is not supported by Snowflake’s DML statements.
Beispielcode¶
Eingabecode:¶
MERGE INTO people_target pt
USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
LOG ERRORS;
Generierter Code:¶
MERGE INTO people_target pt
USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
-- --** SSC-FDM-OR0031 - THE ERROR LOGGING CLAUSE IN DML STATEMENTS IS NOT SUPPORTED BY SNOWFLAKE **
--LOG ERRORS
;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0032¶
StandardHash function with input non-string parameter generates a different result in Snowflake.
Beschreibung¶
This warning is used when STANDARD_HASH
function in Oracle with input non-string parameter generates a different result in Snowflake.
Beispielcode¶
Eingabecode:¶
Query¶
SELECT STANDARD_HASH(1+1) FROM DUAL;
Result¶
STANDARD_HASH(1+1) |
--------------------------------------------------+
E39323970701D93598FC1D357F4BF04578CE3242 |
Generierter Code:¶
Query¶
SELECT
--** SSC-FDM-OR0032 - STANDARD HASH FUNCTION WITH INPUT NON-STRING PARAMETER GENERATES A DIFFERENT RESULT IN SNOWFLAKE **
SHA1(1+1)
FROM DUAL;
Result¶
SHA1(1+1) |
--------------------------------------------------+
da4b9237bacccdf19c0760cab7aec4a8359010b0 |
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0033¶
DBMS_RANDOM.VALUE Built-In Package precision is lower in Snowflake
Beschreibung
This message is shown when SnowConvert AI migrates a DBMS_RANDOM.VALUE Oracle built-in package function. This warning indicates that the UDF added to emulate the functionality has lower precision than the original function.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE built_in_package_proc
IS
var1 NUMBER;
BEGIN
SELECT DBMS_RANDOM.VALUE() INTO var1 FROM DUAL;
SELECT DBMS_RANDOM.VALUE(2,10) INTO var1 FROM DUAL;
END;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE built_in_package_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
var1 NUMBER(38, 18);
BEGIN
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF() INTO
:var1
FROM DUAL;
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF(2,10) INTO
:var1
FROM DUAL;
END;
$$;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0034¶
Sequence start value with ‚LIMIT VALUE‘ is not supported by Snowflake.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0001 documentation.
Beschreibung¶
This error appears when the START WITH
statement value is LIMIT VALUE
.
In Oracle this clause is just use in ALTER TABLE
START
WITH
LIMIT VALUE
, which is specific toidentity_options
, can only be used withALTER
TABLE
MODIFY
. If you specifySTART
WITH
LIMIT VALUE
, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator’s high water mark. The next value returned by the sequence generator will be the high water mark +INCREMENT
BY
integer
for increasing sequences, or the high water mark -INCREMENT
BY
integer
for decreasing sequences.
ALTER TABLE ORACLE¶
Beispielcode¶
Eingabecode:¶
CREATE SEQUENCE SEQUENCE1
START WITH LIMIT VALUE;
Generierter Code:¶
CREATE OR REPLACE SEQUENCE SEQUENCE1
--** SSC-FDM-OR0034 - SEQUENCE START VALUE WITH 'LIMIT VALUE' IS NOT SUPPORTED BY SNOWFLAKE. **
START WITH LIMIT VALUE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0035¶
DBMS_OUTPUT.PUTLINE check UDF implementation
Beschreibung¶
This message is shown when SnowConvert AI migrates a DBMS_OUTPUT.PUT_LINE
Oracle built-in package function. This warning tells you to check the added UDF.
This EWI exists to tell the user to review the DBMS_OUTPUT.PUT_LINE_UDF
implementation where the following information will be found:
Warnung
Performance may be affected by using this UDF. If you want to start logging information, please uncomment the implementation. Note that this is using a temporary table, if you want the data to persist after a session ends, please remove TEMPORARY from the CREATE TABLE.
Once the calls of DBMS_OUTPUT.PUT_LINE_UDF
has been done, please use the following query to read all the logs: SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE builtin_package_call
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(1);
DBMS_OUTPUT.PUT_LINE("Test");
END;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE builtin_package_call ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF(1);
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF("Test");
END;
$$;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0036¶
Unnecessary built-in packages parameters
Beschreibung¶
This message is displayed when SnowConvert AI migrates an Oracle built-in package procedure or function, and some of the arguments are removed from the call.
Some of the original parameters may not have an equivalent in Snowflake or may not be needed in the transformed version, those parameters are removed from the produced code but are preserved in the EWI message so the user can still track them.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE built_in_package_proc
IS
w_file UTL_FILE.FILE_TYPE;
BEGIN
w_file:= UTL_FILE.FOPEN('MY_DIR','test.txt','W',32760);
UTL_FILE.PUT_LINE(w_file,'New line');
END;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE built_in_package_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
w_file OBJECT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'UTL_FILE.FILE_TYPE' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := OBJECT_CONSTRUCT();
BEGIN
--** SSC-FDM-OR0036 - PARAMETERS: 'LOCATION, MAX_LINESIZE_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
CALL UTL_FILE.FOPEN_UDF('test.txt', 'W');
SELECT
*
INTO
w_file
FROM
TABLE(RESULT_SCAN(LAST_QUERY_ID()));
--** SSC-FDM-OR0036 - PARAMETERS: 'AUTOFLUSH_UDF' UNNECESSARY IN THE IMPLEMENTATION. **
CALL UTL_FILE.PUT_LINE_UDF(:w_file, 'New line');
END;
$$;
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0037¶
The used syntax in select is not supported in Snowflake.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0004 documentation
Bemerkung
Some parts of the output code are omitted for clarity reasons.
Beschreibung¶
This warning happens when a clause in a select is not supported in Snowflake. The not supported clauses are:
CONTAINERS
SUBQUERY RESTRICTION
HIERARCHIES
EXTERNAL MODIFY
DBLINK
SHARDS
PARTITION
SUBPARTITION
HIERARCHICAL
Beispielcode¶
Eingabecode:¶
SELECT * FROM TABLE1 EXTERNAL MODIFY (LOCATION 'file.csv' REJECT LIMIT UNLIMITED);
Generierter Code:¶
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **
SELECT * FROM
TABLE1
-- --** SSC-FDM-OR0037 - THE 'OPTIONAL MODIFIED EXTERNAL' SYNTAX IN SELECT IS NOT SUPPORTED IN SNOWFLAKE **
-- EXTERNAL MODIFY (LOCATION 'file.csv' REJECT LIMIT UNLIMITED)
;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0038¶
Boolean cursor attribute is not supported.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0128 documentation.
Beschreibung¶
This message is used to indicate that a boolean cursor attribute is not supported in SnowScript or that there is no transformation that emulates its functionality in SnowScript. The following table shows the boolean cursor attributes that can be emulated:
Boolesches cursor-Attribut |
Status |
---|---|
|
Kann emuliert werden |
|
Kann emuliert werden |
|
Nicht unterstützt |
Beispielcode¶
Eingabecode:¶
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;
Generierter Code:¶
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "table1" **
CREATE OR REPLACE PROCEDURE cursor_attributes_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
is_open_attr BOOLEAN;
found_attr BOOLEAN;
my_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
my_cursor CURSOR
FOR
SELECT
OBJECT_CONSTRUCT( *) sc_cursor_record FROM
table1;
BEGIN
OPEN my_cursor;
LOOP
--** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
FETCH my_cursor INTO
:my_record;
IF (my_record IS NULL) THEN
EXIT;
END IF;
is_open_attr := null /*my_cursor%ISOPEN*/ /*** SSC-FDM-OR0038 - BOOLEAN CURSOR ATTRIBUTE %ISOPEN IS NOT SUPPORTED IN SNOWFLAKE ***/;
found_attr := my_record IS NOT NULL;
END LOOP;
CLOSE my_cursor;
END;
$$;
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0039¶
Create Type Not Supported in Snowflake
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0007 documentation
Beschreibung¶
This message is added when a Create Type statement not supported by Snowflake is used.
Beispielcode¶
Eingabecode (Oracle):¶
CREATE TYPE type6 UNDER type5(COL1 INTEGER);
Generierter Code:¶
----** SSC-FDM-OR0039 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE **
--CREATE TYPE type6 UNDER type5(COL1 INTEGER)
;
Best Practices¶
No end-user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0040¶
Numeric characters cannot be altered in Snowflake. The decimal separator in Snowflake is the dot character.
Beschreibung¶
Numeric characters cannot be altered in Snowflake. The decimal separator in Snowflake is the dot character. The ALTER session statement is commented and a warning is added.
Beispielcode¶
Oracle:¶
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
Snowflake Scripting:¶
----** SSC-FDM-OR0040 - NUMERIC CHARACTERS CANNOT BE ALTERED IN SNOWFLAKE. THE DECIMAL SEPARATOR IN SNOWFLAKE IS THE DOT CHARACTER. **
--ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
;
Best Practices¶
No end-user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0041¶
Built In Package Not Supported.
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0076 documentation
Beschreibung¶
Translation for built-in packages is not currently supported.
Beispielcode¶
Eingabecode (Oracle):¶
SELECT
UTL_RAW.CAST_TO_RAW('some magic here'),
DBMS_UTILITY.GET_TIME
FROM DUAL;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'UTL_RAW.CAST_TO_RAW' IS NOT CURRENTLY SUPPORTED. **
'' AS CAST_TO_RAW,
--** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_UTILITY.GET_TIME' IS NOT CURRENTLY SUPPORTED. **
'' AS GET_TIME
FROM DUAL;
Best Practices¶
No end-user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0042¶
Date Type Transformed To Timestamp Has A Different Behavior
Beschreibung¶
Date type is being transformed to either Date or Timestamp type depending on flag –disableDateAsTimestamp, because Date type in Snowflake has a different behavior than Oracle.
Wichtige Unterschiede¶
Oracle DATE |
Snowflake DATE |
|
---|---|---|
Funktionalität |
Speichert Informationen zu Datum und Uhrzeit |
Speichert nur Datumsinformationen (Jahr, Monat, Tag) |
Interner Speicher |
Binäre Zahl, die die Sekunden seit der Epoche angibt |
Kompaktes Format, optimiert für Datumsangaben |
Anwendungsfälle |
Allgemeiner Speicher für Datum und Uhrzeit |
Szenarien, in denen nur Datumsinformationen benötigt werden |
Vorteile |
Unterstützt sowohl Datum als auch Uhrzeit |
Effizientere Speicherung für Datumsangaben |
Einschränkungen |
Datum und Uhrzeit können nicht getrennt gespeichert werden. |
Speichert keine Zeitinformationen |
Beispielcode¶
Eingabecode (Oracle):¶
CREATE TABLE "PUBLIC"."TABLE1"
(
"CREATED_DATE" DATE,
"UPDATED_DATE" DATE
);
Generierter Code:¶
CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
(
"CREATED_DATE" TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
"UPDATED_DATE" TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
-- Additional Params: --disableDateAsTimestamp
CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
(
"CREATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
"UPDATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
Best Practices¶
No end-user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0043¶
BFILE/BLOB parameters are considered binary. A format may be needed.
Beschreibung¶
This error happens when a TO_CLOB is converted to a TO_VARCHAR function. A format may be needed for BFILE/BLOB parameters.
Beispielcode¶
Input Code: ¶
SELECT TO_CLOB('Lorem ipsum dolor sit amet') FROM DUAL;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0043 - BFILE/BLOB PARAMETERS ARE CONSIDERED BINARY, FORMAT MAY BE NEEDED. **
TO_VARCHAR('Lorem ipsum dolor sit amet')
FROM DUAL;
Best Practices¶
Check if outputs in the input code and converted code are equivalent and add a format parameter if needed.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0044¶
REGEXP_LIKE_UDF match parameter may not behave correctly
Bemerkung
Some parts in the output code are omitted for clarity reasons.
Beschreibung¶
This warning appears when the Oracle REGEXP_LIKE
condition comes with the third parameter (match parameter). The reason to add the warning is that the REGEXP_LIKE_UDF
used to replace the REGEXP_LIKE
does not recognize all the characters used by the match parameter, so the result of the query in Snowflake may not be equivalent to Oracle.
Beispielcode¶
Oracle-Eingabecode:¶
SELECT last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;
Generierter Code:¶
SELECT last_name
FROM
hr.employees
WHERE
--** SSC-FDM-OR0044 - REGEXP_LIKE_UDF MATCH PARAMETER MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
PUBLIC.REGEXP_LIKE_UDF(last_name, '([aeiou])\\1', 'i')
ORDER BY last_name;
When the
REGEXP_LIKE
condition comes with one of the characters that are not supported by the user-defined function, maybe a possible solution is to change the regular expression in order to simulate the behavior of the missing character in the match parameter. To know more about the character not supported go to REGEXP_LIKE_UDF documentation.If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0045¶
Partitions Clauses are Handled by Snowflake
Bemerkung
This FDM is deprecated, please refer to SSC-EWI-OR0010 documentation
Beschreibung¶
This warning appears when the PARTITION
and SUBPARTITION
clauses appear within a query. Snowflake handle partitions automatically
Beispielcode¶
Eingabecode:¶
SELECT * FROM TABLITA PARTITION(col1);
Generierter Code:¶
SELECT * FROM
TABLITA
-- --** SSC-FDM-OR0045 - PARTITIONS CLAUSES ARE HANDLED BY SNOWFLAKE **
-- PARTITION(col1)
;
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0046¶
The Subquery Restriction is not Possible in Snowflake
Bemerkung
Some parts of the output code are omitted for clarity reasons.
Beschreibung¶
This warning happens when a Subquery Restriction appears in a SELECT
Statement.
Beispielcode¶
Eingabecode:¶
SELECT * FROM LATERAL(SELECT * FROM TABLITA WITH READ ONLY CONSTRAINT T);
Generierter Code:¶
SELECT * FROM LATERAL(SELECT * FROM
TABLITA
-- --** SSC-FDM-OR0046 - THE SUBQUERY RESTRICTION IS NOT POSSIBLE IN SNOWFLAKE **
-- WITH READ ONLY CONSTRAINT T
);
Best Practices¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0047¶
It may be needed to set a TimeStampOutput format.
Beschreibung¶
TIMESTAMP_OUTPUT_FORMAT session parameter may need to be set to ‚DD-MON-YY HH24.MI.SS.FF AM TZH:TZM‘ for timestamp output equivalence.
Beispielcode¶
Input Code: ¶
SELECT SYSTIMESTAMP FROM DUAL;
Example of default TIMESTAMP output in Oracle¶
Output
13-JAN-21 04.18.37.288656 PM +00:00
Generierter Code:¶
SELECT
CURRENT_TIMESTAMP() /*** SSC-FDM-OR0047 - YOU MAY NEED TO SET TIMESTAMP OUTPUT FORMAT ('DD-MON-YY HH24.MI.SS.FF AM TZH:TZM') ***/
FROM DUAL;
Example of default TIMESTAMP output in Snowflake¶
Output
2021-01-13 08:18:19.720 -080
Best Practices¶
To change the timestamp output format in Snowflake use the following query:
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'DD-MON-YY HH24.MI.SS.FF AM TZH:TZM';
If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0049¶
LAG function might fail if default value type differs from the expression type.
Beschreibung¶
In Oracle, the LAG
function automatically converts the default value’s data type to match the expression’s type. Snowflake, however, does not perform this implicit conversion. Therefore, a warning is issued to indicate that the LAG
function may fail if the data types are incompatible.
Beispielcode¶
Eingabecode:¶
SELECT
LAG(salary, 2, '0') OVER (ORDER BY salary) AS salary_two_steps_back
FROM
employees;
Generierter Code:¶
SELECT
--** SSC-FDM-OR0049 - LAG FUNCTION MIGHT FAIL IF DEFAULT VALUE TYPE DIFFERS FROM THE EXPRESSION TYPE. **
LAG(salary, 2, '0')
OVER (ORDER BY salary) AS salary_two_steps_back
FROM
employees;
Best Practices¶
Verify that the data type of the default value matches the data type of the expression in the
LAG
function. If they differ, explicitly cast the default value to the expression’s data type.If you need more support, you can email us at snowconvert-support@snowflake.com
SSC-FDM-OR0050¶
Exceptions with NOCOPY
parameters may lead to data inconsistency.
Beschreibung¶
In Oracle PL/SQL, the NOCOPY
keyword is an optimization hint for OUT
and IN OUT
procedure parameters. By default, Oracle passes these parameters by value, creating an expensive copy of the data during the call and copying it back upon completion. This can cause significant performance overhead for large data structures.
NOCOPY
instructs Oracle to pass by reference instead, allowing the procedure to directly modify the original data. This eliminates copying overhead and improves performance. However, changes are immediate and are not implicitly rolled back if an unhandled exception occurs within the procedure.
Therefore, we will remove the NOCOPY parameters option and add this FDM. This is because procedure execution terminates upon hitting an exception, preventing the RETURN
statement from being reached. As a result, the variable in the caller’s declare block retains its initial values, as the procedure fails to successfully return a new value for assignment.
Beispielcode¶
Eingabecode:¶
CREATE OR REPLACE PROCEDURE calculate_division_with_nocopy (
p_numerator IN NUMBER,
p_denominator IN NUMBER,
p_result OUT NOCOPY NUMBER
)
IS
PROCEDURE calculate_division(result OUT NOCOPY NUMBER)
AS
BEGIN
result := 20;
result := p_numerator / p_denominator;
END calculate_division;
BEGIN
calculate_division(p_result);
EXCEPTION
WHEN OTHERS THEN
p_result := p_result;
END calculate_division_with_nocopy;
Generierter Code:¶
CREATE OR REPLACE PROCEDURE calculate_division_with_nocopy (p_numerator NUMBER(38, 18), p_denominator NUMBER(38, 18), p_result OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "07/23/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
calculate_division PROCEDURE(result
--** SSC-FDM-OR0050 - EXCEPTIONS WITH NOCOPY PARAMETERS MAY LEAD TO DATA INCONSISTENCY. **
NUMBER(38, 18))
RETURNS NUMBER
AS
BEGIN
result := 20;
result := :p_numerator / :p_denominator;
RETURN result;
END;
call_results NUMBER;
BEGIN
call_results := (
CALL
calculate_division(:p_result)
);
p_result := :call_results;
EXCEPTION
WHEN OTHER THEN
p_result := :p_result;
END;
$$;
Best Practices¶
If you need more support, you can email us at snowconvert-support@snowflake.com