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

Oracle DATE arithmetic:

	Oracle		Snowflake
1	SYSDATE + 1	Add 1 day	DATEADD(DAY, 1, CURRENT_TIMESTAMP(0))
2	SYSDATE + 1/24	Add 1 hour	DATEADD(HOUR, 1, CURRENT_TIMESTAMP(0))
3	SYSDATE + 1/1440	Add 1 minute	DATEADD(MINUTE, 1, CURRENT_TIMESTAMP(0))
4	SYSDATE + 1/86400	Add 1 second	DATEADD(SECOND, 1, CURRENT_TIMESTAMP(0))

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;

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications