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

Partial

Partial

Partial

Partial

Full

N/A

Full

N/A

Not Translated

Snowflake does not have a native equivalent for Oracle collections. See Collections and Records.

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

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;

Snowflake

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;

Not supported numeric expressions

Oracle

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