Oracle to Snowflake Migration

SQLines provides tools to help you convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to Snowflake.

  • SQLines SQL Converter - SQL scripts assessment and conversion tool

Databases:

  • Oracle 21c, 19c, 18c, 12c, 11g, 10g and 9i
  • Snowflake

Migration Reference

SQL Language Elements

Built-in SQL Functions

Converting string functions:

Oracle Snowflake
1 INSTR(str, substr, pos, occur) Get the position of substring REGEXP_INSTR(str, substr, pos, occur)

Converting date and time functions:

Oracle Snowflake
1 MONTHS_BETWEEN(dt1, dt2) Get difference in months MONTHS_BETWEEN(dt1, dt2)
2 SYSDATE Get the current date and time CURRENT_TIMESTAMP(0)
3 TO_CHAR(datetime, format) Convert a datetime to string TO_CHAR(datetime, format)
4 TO_DATE(str, format) Convert a string to date and time TO_TIMESTAMP(str, format)
5 TRUNC(datetime) Truncate a datetime value TRUNC(datetime)

CREATE FUNCTION Statement

Converting user-defined functions from Oracle to Snowflake:

Oracle Snowflake
1 CREATE OR REPLACE FUNCTION name CREATE OR REPLACE PROCEDURE name
2 (param IN | OUT | IN OUT datatype DEFAULT default, …) (param datatype DEFAULT default, …)
3 RETURN data_type RETURNS data_type
4 IS | AS LANGUAGE SQL AS $$
5 declarations DECLARE declarations
6 BEGIN BEGIN
7 function_body function_body
8 END proc_name; END;
9 / $$

Note. Snowflake SQL scripting is available in stored procedures only so you have to convert CREATE FUNCTION to CREATE PROCEDURE.

For more information, see PL/SQL Statements conversion.

PL/SQL Statements

Converting Oracle PL/SQL statements and clauses to Snowflake SQL Scripting:

Oracle Snowflake
1 variable datatype := value Variable declaration variable datatype := value
2 variable table.column%TYPE Inherited data type variable datatype
3 variable cursor%ROWTYPE Cursor-based record List of variables
4 variable := value Assignment statement variable := value

Cursor declaration and operations:

Oracle Snowflake
1 CURSOR cur IS SELECT … Declare cursor cur CURSOR FOR SELECT …
2 CURSOR cur(param, …)
IS SELECT … WHERE param
Declare cursor with parameters CURSOR cur
FOR SELECT … WHERE ? …
3 OPEN cur Open cursor OPEN cur
4 OPEN cur(param, …) Open cursor with parameters OPEN cur USING (param, …)
5 FETCH cur INTO var, … Fetch cursor FETCH cur INTO var, …
6 CLOSE cur Close cursor CLOSE cur

Flow-of-control statements:

Oracle Snowflake
1 IF condition THEN … END IF; IF statement IF (condition) THEN … END IF;
IF … ELSIF … ELSE … END IF; IF … ELSEIF … ELSE … END IF;