EXPRESSIONS

Translation reference for Oracle expressions to Snow Scripting

Some parts in the output code are omitted for clarity reasons.

Description

The following table has a summary of how to transform the different Oracle Expression kinds into Snow Scripting.

Syntax	Conversion status	Notes
Character Expressions	Partial	Partially Supported Common scenarios
Numeric Expressions	Partial	Partially Supported Common scenarios
Date Expressions	Partial	Partially Supported Common scenarios
Boolean Expressions	Partial	Not supported boolean expressions
Simple Case Expressions	Full	N/A
Searched Case Expressions	Full	N/A
Collection Constructor	Not Translated	Snowflake does not have a native equivalent for Oracle collections. See Collections and Records.
Qualified Expressions	Not Translated	Snowflake does not have a native equivalent for Oracle record types. See Collections and Records.

Partially supported common scenarios

Oracle Constants

For more information, check the Oracle Constant declaration section.

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE EXPRESSIONS_TABLE(col VARCHAR(30));
CREATE OR REPLACE PROCEDURE EXPRESSIONS_SAMPLE
IS
RESULT VARCHAR(50);
CONST CONSTANT VARCHAR(20) := 'CONSTANT TEXT';
BEGIN
	-- CONSTANT EXPRESSIONS
	RESULT := CONST;
	INSERT INTO EXPRESSIONS_TABLE(COL) VALUES (RESULT);
END;

CALL EXPRESSIONS_SAMPLE();
SELECT * FROM EXPRESSIONS_TABLE;

Result

|COL          |
|-------------|
|CONSTANT TEXT|

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE EXPRESSIONS_TABLE (col VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE EXPRESSIONS_SAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		RESULT VARCHAR(50);
		--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
		CONST VARCHAR(20) := 'CONSTANT TEXT';
	BEGIN
		-- CONSTANT EXPRESSIONS
		RESULT := :CONST;
		INSERT INTO EXPRESSIONS_TABLE(COL) VALUES (:RESULT);
	END;
$$;

CALL EXPRESSIONS_SAMPLE();

SELECT * FROM
	EXPRESSIONS_TABLE;

Result

|COL          |
|-------------|
|CONSTANT TEXT|

Not supported numeric expressions

Oracle

Query

CREATE TABLE NUMERIC_EXPRESSIONS_TABLE(col number);

CREATE OR REPLACE PROCEDURE NUMERIC_EXPRESSIONS
IS
RESULT NUMBER;
CURSOR C1 IS SELECT * FROM NUMERIC_EXPRESSIONS_TABLE;
TYPE NUMERIC_TABLE IS TABLE OF NUMBER(10);
COLLECTION NUMERIC_TABLE; 
BEGIN
	-- CURSOR EXPRESSIONS
	OPEN C1;
	RESULT := C1%ROWCOUNT;
	CLOSE C1;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);
	
	-- ** OPERATOR
	RESULT := 10 ** 2;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);
	
	-- COLLECTION EXPRESSIONS
	COLLECTION := NUMERIC_TABLE(1, 2, 3, 4, 5, 6); 
	RESULT := COLLECTION.COUNT + COLLECTION.FIRST;
<