SnowConvert AI - Oracle

What is SnowConvert AI for Oracle?

SnowConvert AI is a software that understands Oracle SQL and PL/SQL (https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html), and performs the following conversions:

SnowConvert AI Terminology

为了确保我们在深入探讨代码转换的奇妙之处之前达成共识，以下是一些关键术语的定义说明，这样当我们在文档中频繁使用这些术语时，您能准确理解它们的含义：

  • SQL（结构化查询语言）： 大多数现代数据库架构中用于存储、操作和检索数据的标准语言。

  • PL/SQL： SQL 的过程语言。由 Oracle 创建，且仍由 Oracle 用作 Oracle 中存储过程和函数的脚本语言。

  • SnowConvert AI: The software that converts securely and automatically your Oracle files to the Snowflake cloud data platform.

  • Conversion rule or transformation rule: Rules that allow SnowConvert AI to convert from a portion of source code and determine the expected target code.

  • Parse: Parse or parsing is an initial process done by SnowConvert AI to understand the source code, and build up an internal data structure to process the conversion rules.

Let's dive in to some of the code conversions that Snowflake SnowConvert AI can perform.

代码转换

Oracle SQL 转 Snowflake SQL

SnowConvert AI for Oracle takes in Oracle source code in SQL and converts the Data Definition Language (DDL), Data Manipulation Language (DML), and functions in the source code to the corresponding SQL in Snowflake SQL.

示例

以下是简单 CREATE TABLE 语句转换示例。

源代码：

CREATE TABLE "MyTable"
(
  "COL1" NUMBER,
  "COL2" NUMBER,
  "COL3" NUMBER GENERATED ALWAYS AS (COL1 * COL2) VIRTUAL,
  "COL4" LONG,
  "COL5" CLOB,
  "COL6" ROWID,
  "COL7" NVARCHAR2(10),
  "COL8" RAW(255),
  CONSTRAINT "PK" PRIMARY KEY ("COL1")
);
Copy

迁移后的 Snowflake SQL 代码：

CREATE OR REPLACE TABLE "MyTable"
  (
    "COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    "COL2" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    "COL3" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ AS (COL1 * COL2),
    "COL4" VARCHAR,
    "COL5" VARCHAR,
    "COL6" VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
    "COL7" VARCHAR(10),
    "COL8" BINARY,
    CONSTRAINT "PK" PRIMARY KEY ("COL1")
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
  ;
Copy

在转换后的 SQL 中，您会注意到我们进行了多方面的转换。一些亮点：

  • 如果用户未指定 PUBLIC 架构，则默认情况下为所有表和视图名称添加此架构

  • CREATE TABLE 至 CREATE OR REPLACE TABLE

  • 数据类型转换：

    • LONG 至 VARCHAR

    • CLOB 至 VARCHAR

    • ROWID 至 VARCHAR

    • NVARCHAR2 至 VARCHAR

    • RAW 至 BINARY

  • 数据类型属性：GENERATED ALWAYS AS (COL1 * COL2) VIRTUAL 至 AS (COL1 * COL2)

For more information about data types and their equivalent: Data Types. More examples can be found in the rest of the documentation.

Oracle PL/SQL

SnowConvert AI takes Oracle stored procedures and functions (PL/SQL) and converts them to either Snowflake Scripting or JavaScript embedded into Snowflake SQL. Oracle CREATE PROCEDURE and REPLACE PROCEDURE syntax is replaced by Snowflake CREATE OR REPLACE PROCEDURE syntax.

示例

以下是一个简单的 Oracle CREATE PROCEDURE 转换示例，用于向日志表插入数据。

备注

此示例将用于 Snowflake Scripting 和 JavaScript。

CREATE OR REPLACE PROCEDURE SC_DEMO.PROC_LOG 
      (final_proc  VARCHAR2, 
       final_message   VARCHAR2,
       logger_type VARCHAR2 DEFAULT 'I')
AS
BEGIN
  INSERT INTO SC_DEMO.PROC_LOG_TABLE
    VALUES (SC_DEMO.final_logging_seq.NEXTVAL,
            sysdate,
            SUBSTR(logger_type, 1, 1),
            SUBSTR(final_proc, 1, 30),
            SUBSTR(final_message, 1, 1024));
  COMMIT;

END;
Copy

到 Snowflake Scripting

Snowflake Scripting 作为 Snowflake SQL 的扩展，它增加了对过程逻辑的支持，允许我们创建存储过程并复制 Oracle PL/SQL 的类似行为和语句。

迁移示例

CREATE OR REPLACE PROCEDURE SC_DEMO.PROC_LOG
(final_proc VARCHAR, final_message VARCHAR,
 logger_type VARCHAR DEFAULT 'I')
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    INSERT INTO SC_DEMO.PROC_LOG_TABLE
      VALUES (SC_DEMO.final_logging_seq.NEXTVAL, CURRENT_TIMESTAMP(),
              SUBSTR(:logger_type, 1, 1),
              SUBSTR(:final_proc, 1, 30),
              SUBSTR(:final_message, 1, 1024));
    --** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
    COMMIT;
  END;
$$;
Copy

到 JavaScript

JavaScript 称为脚本语言，所有内部语句都转换为 JavaScript。 如果您想更好地了解 JavaScript API，可查看 [此文档] (https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html)。

迁移示例

-- Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE SC_DEMO.PROC_LOG
(final_proc STRING, final_message STRING,
 logger_type STRING DEFAULT 'I')
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert AI Helpers Code section is omitted.

  EXEC(`INSERT INTO SC_DEMO.PROC_LOG_TABLE
  VALUES (SC_DEMO.final_logging_seq.NEXTVAL, CURRENT_TIMESTAMP(),
            SUBSTR(?, 1, 1),
            SUBSTR(?, 1, 30),
            SUBSTR(?, 1, 1024))`,[LOGGER_TYPE,FINAL_PROC,FINAL_MESSAGE]);
  EXEC(`--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;`);
$$;
Copy

在此已转换的 SQL 中，您会注意到我们已转换为嵌入 Snowflake SQL 的新语言 (JavaScript)。 其中的亮点不胜枚举，但可以说，这份文档包含了了解这种转换的所有基本要素。

The line that states // ... Necessary SnowConvert AI Helpers are inserted here ... will actually have the SnowConvert AI JavaScript Helpers. They can be lengthy, so they are removed from this first example.

And that's it! Snowflake SnowConvert AI takes the pain and frustration out of changing data platforms. Learn more about getting started with SnowConvert AI for Oracle on the next page.

语言: 中文