SnowConvert for SQL

In this page, there is an explanation for each of the additional parameters that are available in SnowConvert for SQL

Common CLI Arguments

The following arguments can be used in all the SQL tools:

-i, --input <PATH> (Required)

The path to the folder or file containing the input source code.

-o, --output <PATH> (Required)

The path to the output folder where the converted code and reports will be stored.

-a, --assessment

Flag to indicate whether or not to generate only Assessment files. By default, it's set to FALSE.

-t, --PLTargetLanguage <TARGET_LANGUAGE>

String value specifying the target language to convert Stored procedures and Macros. Currently supported are: SnowScript and JavaScript. The default value is set to SnowScript.

-e, --encoding <CODE PAGE>

The encoding code page number is used for parsing the source files. We only accept encodings supported by .NET Core. Here are the ones supported at the moment:

Code Page	Name	Display Name
1200	utf-16	Unicode
1201	unicodeFFFE	Unicode (Big endian)
12000	utf-32	Unicode (UTF-32)
12001	utf-32BE	Unicode (UTF-32 Big endian)
20127	us-ascii	US-ASCII
28591	iso-8859-1	Western European (ISO)
65000	utf-7	Unicode (UTF-7). Not available in .NET 5
65001	utf-8	Unicode (UTF-8). Default encoding

-s, --customschema <SCHEMA_NAME>

The string value specifies the custom schema name to apply. If not specified, the original database name will be used. Example: DB1.MyCustomSchema.Table1.

-d, --database <DB_NAME>

The string value specifies the custom database name to apply. Example: MyCustomDB.PUBLIC.Table1.

--rate

The string value specifies the conversion rate mode. Currently supported are: LoC (Lines of Code) and Character. The default value is set to LoC.

-m, --comments

Flag to indicate if the user wants to comment on nodes that have missing dependencies.

--useExistingNameQualification

This flag must be used in conjunction with the -d or -s parameters. When used, it preserves the existing name qualification from the input code when previous parameters are used. Let's take a look at this example where -s newSchema was included:

Input

IN -> Teradata_01.sql

SELECT * FROM mySchema.myObject;

Output Default

OUT -> Teradata_01.sql

-- Additional Params: -s newSchema
SELECT
* FROM
newSchema.myObject;

Output with param useExistingNameQualification

OUT -> Teradata_01b.sql

-- Additional Params: -s newSchema --useExistingNameQualification
SELECT
* FROM
mySchema.myObject;

The same applies to databases.

--disableEWIsGeneration

Flag to indicate whether EWIs comments (Errors, Warnings, and Issues) will not be generated on the converted code. The default is false.

--terms

Show access code terms information.

--help

Display the help information.

Teradata CLI arguments

The following CLI arguments are specific for executing migrations with SnowConvert for Teradata

--CharacterToApproximateNumber <NUMBER>

An integer value for the CHARACTER to Approximate Number transformation (Default: 10).

--DefaultDateFormat <STRING>

String value for the Default DATE format (Default: "YYYY/MM/DD").

--DefaultTimeFormat <STRING>

String value for the Default TIME format (Default: "HH:MI:SS").

--DefaultTimestampFormat <STRING>

String value for the Default TIMESTAMP format (Default: "YYYY/MM/DD HH:MI:SS").

--DefaultTimezoneFormat <STRING>

String value for the Default TIMEZONE format (Default: "GMT-5").

-p, --scriptTargetLanguage <TARGET_LANGUAGE>

The string value specifies the target language to convert Bteq and Mload script files. Currently supported values are SnowScript and Python. The default value is set to Python.

--replaceDeleteAllToTruncate

Flag to indicate whether Delete All statements must be replaced with Truncate or not. This will generate MSCEWI2037 when the replacement is done. Example:

Input

IN -> Teradata_02.sql

create table testTable(
    column1 varchar(30)
);

delete testTable all;

delete from testTable;

Output Default

OUT -> Teradata_01.sql

CREATE TABLE PUBLIC.testTable (
column1 varchar(30)
);

DELETE FROM PUBLIC.testTable;

DELETE FROM PUBLIC.testTable ;

Output with param replaceDeleteAllToTruncate

OUT -> Teradata_01b.sql

-- Additional Params: --replaceDeleteAllToTruncate
CREATE TABLE testTable (
    column1 varchar(30)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

TRUNCATE TABLE testTable;

DELETE FROM
    testTable;

--generateStoredProcedureTags

Flag to indicate whether the SQL statements SELECT, INSERT, CREATE, DELETE, UPDATE, DROP, MERGE in Stored Procedures will be tagged on the converted code. This feature is used for easy statement identification on the migrated code. Wrapping these statements within these XML-like tags allows for other programs to quickly find and extract them. The decorated code looks like this:

--splitPeriodDatatype

This flag is used to indicate that the tool should migrate any use of the PERIOD datatype as two separate DATETIME fields that will hold the original period begin and end values, anytime a period field or function is migrated using this flag SSC-FDM-TD0004 will be added to warn about this change.

Input

IN -> Teradata_03.sql

CREATE TABLE myTable(
   col1 PERIOD(DATE),
   col2 VARCHAR(50),
   col3 PERIOD(TIMESTAMP)
);

Output Default

OUT -> Teradata_03.sql

CREATE TABLE myTable (
   col1 VARCHAR(24) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!,
   col2 VARCHAR(50),
   col3 VARCHAR(58) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Output with param splitPeriodDatatype

OUT -> Teradata_03b.sql

-- Additional Params: --splitPeriodDatatype
CREATE TABLE myTable (
   col1_begin DATE,
   col1_end DATE /*** SSC-FDM-TD0004 - PERIOD DATA TYPES ARE HANDLED AS TWO DATA FIELDS ***/,
   col2 VARCHAR(50),
   col3_begin TIMESTAMP,
   col3_end TIMESTAMP /*** SSC-FDM-TD0004 - PERIOD DATA TYPES ARE HANDLED AS TWO DATA FIELDS ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

--arrange

Flag to indicate whether the input code should be processed before parsing and transformation.

--RenamingFile

The path to a .json file that specifies new names for certain objects such as Tables, Views, Procedures, Functions, and Macros. This parameter can't be used with the customSchema argument. Navigate to the Renaming Feature page to learn more about this argument.

Learn more about this step on our Processing the code page.

--disableTopologicalLevelReorder

This flag is used to indicate that the tool should not reorder the views and tables in a topological order. In other words, using this parameter the output code will be in the same order as the input code.

Input

IN -> Teradata_04.sql

CREATE TABLE tab1 (
	col1 INTEGER
);

CREATE TABLE tab3 (
	col1 INTEGER
);

CREATE TABLE tab2 AS (
	SELECT * FROM tab1
);

Output Default

OUT-> Teradata_04.sql

CREATE TABLE tab1(
	col1 INTEGER
);
CREATE TABLE tab2
  AS (SELECT * FROM tab1);
  
CREATE TABLE tab3(
	col1 INTEGER
);

Output with param disableTopologicalLevelReorder

OUT-> Teradata_04b.sql

-- Additional Params: --disableTopologicalLevelReorder
CREATE TABLE tab1 (
	col1 INTEGER
);

CREATE TABLE tab2 AS (
	SELECT * FROM tab1
);

CREATE TABLE tab3 (
	col1 INTEGER
);

Oracle CLI arguments

--disableSynonym

Flag to indicate whether or not Synonyms should be transformed. By default, it's set to true.

--disablePackagesAsSchemas

Flag to indicate whether or not the Packages should be transformed to new Schemas.

Please check the naming of the procedure enabling and disabling the flag:

Input

IN -> Oracle_01.sql

CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE remove_emp (employee_id NUMBER );
END emp_mgmt;

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS 
PROCEDURE remove_emp (employee_id NUMBER) IS 
   BEGIN 
      DELETE FROM employees 
      WHERE employees.employee_id = remove_emp.employee_id; 
      tot_emps := tot_emps - 1; 
   END; 
END emp_mgmt;

Output Default

OUT -> Oracle_01.sql

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

CREATE OR REPLACE PROCEDURE emp_mgmt.remove_emp (employee_id 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
$$
   BEGIN
      DELETE FROM
         employees
         WHERE employees.employee_id = remove_emp.employee_id;
         tot_emps :=
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
                     tot_emps - 1;
   END;
$$;

Output with param disablePackagesAsSchemas

OUT -> Oracle_01b.sql

-- Additional Params: --disablePackagesAsSchemas
CREATE OR REPLACE PROCEDURE EMP_MGMT_REMOVE_EMP (employee_id 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
$$
   BEGIN
      DELETE FROM
         employees
         WHERE employees.employee_id = remove_emp.employee_id;
         tot_emps :=
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
                     tot_emps - 1;
   END;
$$;

--outerJoinsToOnlyAnsiSyntax

Flag to indicate whether Outer Joins should be transformed to only ANSI syntax.

--disableDateAsTimestamp

Flag to indicate whether SYSDATE should be transformed into CURRENT_DATE or CURRENT_TIMESTAMP. This will also affect all DATE columns that will be transformed to TIMESTAMP.

Input

IN -> Oracle_02.sql

CREATE TABLE DATE_TABLE(
    DATE_COL DATE
);

SELECT SYSDATE FROM DUAL;

Output Default

OUT -> Oracle_02.sql

CREATE OR REPLACE TABLE DATE_TABLE (
        DATE_COL TIMESTAMP
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

SELECT
    CURRENT_TIMESTAMP()
FROM DUAL;

Output with param disableDateAsTimestamp

OUT -> Oracle_02b.sql

-- Additional Params: --disableDateAsTimestamp
CREATE OR REPLACE TABLE DATE_TABLE (
        DATE_COL DATE
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

SELECT
   CURRENT_DATE()
FROM DUAL;

Learn more about how you can get access to the SnowConvert for Oracle Command Line Interface tool by filling out the form on our Snowflake Migrations Info page.

--arrange

Flag to indicate whether the input code should be processed before parsing and transformation.

Learn more about this step on our Processing the code page.

SQL Server CLI arguments

-u, --usedatabase

Flag to indicate whether or not the Transact SQL USE statement should be translated.

-p, --arrange <ARRANGE OPTION> [<ARRANGE OPTION>]

Flag to indicate whether or not to preprocess or arrange the source code before its transformation. By default, it's set to FALSE.

prettyprint	Applies indentation to the original code and get it well organized.
generatereports	Generates extra reports after the arrangement.
multiple	Applies arrangement to multiple databases represented as multiple folders, and keeps their original structure.