SnowConvert: Oracle Literals Translation Reference¶

Text literals¶

Description¶

Use the text literal notation to specify values whenever string appears in the syntax of expressions, conditions, SQL functions, and SQL statements in other parts of this reference.

(Oracle SQL Language Reference Text literals)

[ {N | n} ]
{ ‘[ c ]…’
| { Q | q } ‘quote_delimiter c [ c ]… quote_delimiter’
}

Empty string (‘’)¶

The empty strings are equivalent to NULL in Oracle. To emulate this behavior in Snowflake, empty strings are converted to NULL or undefined, depending on whether the literal is used inside a procedure.

Oracle¶

-- Query 
SELECT UPPER('') FROM DUAL;

-- Result
UPPER('')|  
\---------+
Copy

Snowflake

-- Query  
SELECT UPPER(NULL) FROM DUAL;

-- Result
UPPER(NULL)|  
\-----------+
Copy

Empty string in stored procedures¶

Oracle¶

CREATE TABLE empty\_string\_table(  
col1 VARCHAR(10),  
col2 VARCHAR(10));

CREATE OR REPLACE PROCEDURE null\_proc AS  
    var1 INTEGER := '';  
    var3 INTEGER := null;  
    var2 VARCHAR(20) := 'hello';  
BEGIN  
    var1 := var1 \+ 456;  
    var2 := var2 || var1;  
    IF var1 IS NULL THEN  
        INSERT INTO empty\_string\_table VALUES (var1, var2);  
    END IF;  
END;

CALL null\_proc();

SELECT \* FROM empty\_string\_table;
Copy
COL1    |COL2    |  
\--------+-------+  
        |hello  |
Copy

Snowflake

CREATE OR REPLACE TABLE empty\_string\_table (  
    col1 VARCHAR(10),  
    col2 VARCHAR(10))  
COMMENT \= '{"origin":"sf\_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'  
;

CREATE OR REPLACE PROCEDURE null\_proc ()  
RETURNS VARCHAR  
LANGUAGE SQL  
COMMENT \= '{"origin":"sf\_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'  
EXECUTE AS CALLER  
AS  
$$  
    DECLARE  
        var1 INTEGER := NULL;  
        var3 INTEGER := null;  
        var2 VARCHAR(20) := 'hello';  
    BEGIN  
        var1 := :var1 \+ 456;  
        var2 := NVL(:var2 :: STRING, '') || NVL(:var1 :: STRING, '');  
        IF (:var1 IS NULL) THEN  
            INSERT INTO empty\_string\_table  
            VALUES (:var1, :var2);  
        END IF;  
    END;  
$$;

CALL null\_proc();

SELECT \* FROM  
    empty\_string\_table;
Copy
COL1|COL2 |  
\----+-----+  
    |hello|
Copy

Empty string in built-in functions¶

The transformation does not apply when the empty string is used as an argument of the REPLACE and CONCAT functions in order to keep the functional equivalence.

Oracle¶

-- Query  
SELECT REPLACE('Hello world', '', 'l'), CONCAT('A','') FROM DUAL;

-- Result
REPLACE('HELLOWORLD','','L')|CONCAT('A','')|  
\----------------------------+--------------+  
Hello world                 |A             |
Copy

Snowflake

-- Query 
SELECT REPLACE('Hello world', '', 'l'), CONCAT('A','') FROM DUAL;

-- Result
REPLACE('HELLO WORLD', '', 'L')|CONCAT('A','')|  
\-------------------------------+--------------+  
Hello world                    |A             |
Copy

If the empty strings are replaced by NULL for these cases, the results of the queries will be different.

Interval Type and Date Type¶

Description¶

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are not supported data types; they are transformed to VARCHAR(20). Therefore, all arithmetic operations between Date Types and the original Interval Type Columns are not supported.

Furthermore, operations between an Interval Type and Date Type (in this order) are not supported in Snowflake.

Example Code¶

Input Code Oracle:¶

CREATE TABLE table\_with\_intervals  
(  
    date\_col DATE,  
    time\_col TIMESTAMP,  
    intervalYearToMonth\_col INTERVAL YEAR TO MONTH,  
    intervalDayToSecond\_col INTERVAL DAY TO SECOND  
);

\-- Date \+ Interval Y to M  
SELECT date\_col \+ intervalYearToMonth\_col FROM table\_with\_intervals;

\-- Date \- Interval D to S  
SELECT date\_col \- intervalDayToSecond\_col FROM table\_with\_intervals;

\-- Timestamp \+ Interval D to S  
SELECT time\_col \+ intervalDayToSecond\_col FROM table\_with\_intervals;

\-- Timestamp \- Interval Y to M  
SELECT time\_col \- intervalYearToMonth\_col FROM table\_with\_intervals;
Copy

Output Code:¶

CREATE OR REPLACE TABLE table\_with\_intervals  
    (  
        date\_col TIMESTAMP /\*\*\* SSC-FDM-OR0042 \- DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. \*\*\*/,  
        time\_col TIMESTAMP(6),  
        intervalYearToMonth\_col VARCHAR(20)  /\*\*\* SSC-EWI-0036 \- INTERVAL YEAR TO MONTH DATA TYPE CONVERTED TO VARCHAR \*\*\*/ ,  
        intervalDayToSecond\_col VARCHAR(20)  /\*\*\* SSC-EWI-0036 \- INTERVAL DAY TO SECOND DATA TYPE CONVERTED TO VARCHAR \*\*\*/   
    )  
    COMMENT \= '{"origin":"sf\_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'  
    ;

    \-- Date \+ Interval Y to M  
    SELECT  
    date\_col \+ intervalYearToMonth\_col  /\*\*\* SSC-EWI-OR0095 \- OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED \*\*\*/  FROM  
    table\_with\_intervals;

    \-- Date \- Interval D to S  
SELECT  
    date\_col \- intervalDayToSecond\_col  /\*\*\* SSC-EWI-OR0095 \- OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED \*\*\*/  FROM  
    table\_with\_intervals;

    \-- Timestamp \+ Interval D to S  
SELECT  
    time\_col \+ intervalDayToSecond\_col  /\*\*\* SSC-EWI-OR0095 \- OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED \*\*\*/  FROM  
    table\_with\_intervals;

    \-- Timestamp \- Interval Y to M  
SELECT  
    time\_col \- intervalYearToMonth\_col  /\*\*\* SSC-EWI-OR0095 \- OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED \*\*\*/  FROM  
    table\_with\_intervals;
Copy

Recommendations¶

  • Implement the UDF to simulate the Oracle behavior.

  • Extract the already transformed value that was stored in the column during migration, and use it as a Snowflake Interval Constant when possible.

  • If you need more support, you can email us at snowconvert-support@snowflake.com

Interval Literal¶

Description¶

Snowflake Intervals can only be used in arithmetic operations. Intervals used in any other scenario are not supported.

Example Code¶

Input Code Oracle:¶

SELECT INTERVAL '1-5' YEAR TO MONTH FROM DUAL;
Copy

Output Code:¶

SELECT  
!!!RESOLVE EWI!!! /*** SSC-EWI-0107 - INTERVAL LITERAL IS NOT SUPPORTED BY SNOWFLAKE IN THIS SCENARIO  ***/!!!
 INTERVAL '1-5' YEAR TO MONTH FROM DUAL; 
Copy