SnowConvert AI - Oracle Conversion Settings¶

Object Conversion¶

1. 转换同义词： 该标志用于指示是否应转换同义词。默认情况下，它设置为 True。

2. 将包转换为新架构： 该标志用于指示是否应将包转换为新架构。

   请检查启用和禁用该标志的过程的命名：

Input

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

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

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

3. 将日期转换为时间戳：

该标志用于指示 SYSDATE 是否应转换为 CURRENT_DATE_ 或 _ CURRENT_TIMESTAMP。这也将影响所有将转换为 TIMESTAMP 的 DATE 列。

Input

CREATE TABLE DATE_TABLE(
    DATE_COL DATE
);

SELECT SYSDATE FROM DUAL;

Output Default

CREATE OR REPLACE TABLE DATE_TABLE (
        DATE_COL 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"}}'
    ;

    SELECT
        CURRENT_TIMESTAMP()
    FROM DUAL;

Output with param disableDateAsTimestamp

-- Additional Params: --disableDateAsTimestamp
CREATE OR REPLACE TABLE DATE_TABLE (
        DATE_COL 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"}}'
    ;

    SELECT
        CURRENT_DATE()
    FROM DUAL;

4. 将OUTER JOINS 转换为 ANSI 语法： 该标志用于指示是否应将外部联接转换为仅限 ANSI 语法。

Data type mappings¶

SnowConvert defines default mappings for data type conversions. However, you can point to a JSON file to customize specific data type mappings.

Customize data types: You can upload a JSON file to define specific data type transformation rules. This feature allows you to customize how data types are converted during migration.

Supported transformations include:

• NUMBER to custom NUMBER with specific precision and scale

• NUMBER to DECFLOAT for preserving exact decimal precision

When you upload a data type customization file:

• SnowConvert AI applies your transformation rules during conversion

• Numeric literals in INSERT statements targeting customized columns are automatically cast to the appropriate type

• A TypeMappings Report is generated showing all data type transformations applied

JSON Structure:

The JSON file supports three ways to specify data type changes:

Priority order: When multiple rules apply to the same column, SnowConvert AI uses this priority (highest to lowest):

1. specificTableTypeChanges (most specific)

2. projectTypeChanges.columns (name pattern)

3. projectTypeChanges.types (global type mapping)

Example JSON configuration:

{
  "projectTypeChanges": {
    "types": {
      "NUMBER": "DECFLOAT",
      "NUMBER(10, 0)": "NUMBER(18, 0)"
    },
    "columns": [
      {
        "nameExpression": "PRICE",
        "targetType": "DECFLOAT"
      },
      {
        "nameExpression": ".*_AMOUNT$",
        "targetType": "NUMBER(18, 2)"
      }
    ]
  },
  "specificTableTypeChanges": {
    "tables": [
      {
        "tableName": "EMPLOYEES",
        "columns": [
          {
            "columnName": "SALARY",
            "targetType": "NUMBER(15, 2)"
          }
        ]
      }
    ]
  }
}

Download template: Copy and save the JSON structure above as your starting point.

Example transformation:

Given the following Oracle input code:

CREATE TABLE employees (
    employee_ID NUMBER,
    manager_YEAR NUMBER(10, 0),
    manager_MONTH NUMBER(10, 0),
    salary NUMBER(12, 2)
);

CREATE OR REPLACE TABLE employees (
    employee_ID NUMBER(11, 2),
    manager_YEAR NUMBER(18, 0),
    manager_MONTH NUMBER(2, 0),
    salary NUMBER(15, 2)
);

General Result Tab¶

1. Comment objects with missing dependencies: This flag indicates whether the user wants to comment on nodes with missing dependencies.

2. Set encoding of the input files: Check General Conversion Settings for more details.

Description¶

Prepare my code: Flag to indicate whether the input code should be processed before parsing and transformation. This can be useful to improve the parsing process. By default, it's set to FALSE.

Splits the input code top-level objects into multiple files. The containing folders would be organized as follows:

Copy

└───A new folder named ''[input_folder_name]_Processed''
    └───Top-level object type
        └───Schema name

Example¶

├───in
│       DDL_Packages.sql
│       DDL_Procedures.sql
│       DDL_Tables.sql

├───in_Processed
    ├───package
    │   └───MY_SCHEMA
    │           MY_FIRST_PACKAGE.sql
    │           ANOTHER_PACKAGE.sql
    │
    ├───procedure
    │   └───MY_SCHEMA
    │           A_PROCEDURE.sql
    │           ANOTHER_PROCEDURE.sql
    │           YET_ANOTHER_PROCEDURE.sql
    │
    └───table
        └───MY_SCHEMA
                MY_TABLE.sql
                ADDITIONAL_TABLE.sql
                THIRD_TABLE.sql

Requirements ¶

To identify top-level objects, a tag must be included in a comment before their declaration. Our Extraction scripts generate these tags.

The tag should follow the next format:

<sc-top_level_object_type>top_level_object_name</sc-top_level_object_type>

You can follow the next example:

/* <sc-table> MY_SCHEMA.MY_TABLE</sc-table> */
CREATE TABLE "MY_SCHEMA"."MY_TABLE" (
    "MY_COLUMN" VARCHAR2(128)
) ;