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'
}

Sample Source Patterns

Empty string ('')

The empty strings are equivalent to NULL in Oracle, so in order to emulate the behavior in Snowflake, the empty strings are converted to NULL or undefined depending if the literal is used inside a procedure or not.

Oracle

Query

IN -> Oracle_01.sql

SELECT UPPER('') FROM DUAL;

Result

UPPER('')|
---------+
         |

Snowflake

Query

OUT -> Oracle_01.sql

SELECT UPPER(NULL) FROM DUAL;

Result

UPPER(NULL)|
-----------+
           |

Empty string in stored procedures

Oracle

Query

IN -> Oracle_02.sql

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;

Result

COL1	|COL2	|
--------+-------+
        |hello  |

Snowflake

Query

OUT -> Oracle_02.sql

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;

Result

COL1|COL2 |
----+-----+
    |hello|

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

IN -> Oracle_03.sql

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

Result

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

Snowflake

Query

OUT -> Oracle_03.sql

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

Result

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

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