NCLOB Data type

Description

A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. (Oracle SQL Language Reference NCLOB Data Type).

NCLOB Data Type is not supported in Snowflake. VARCHAR is used instead.

Sample Source Patterns

NCLOB in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE nclobtable ( nclob_column NCLOB, empty_column NCLOB );

INSERT INTO nclobtable VALUES ( 'THIS IS A TEST', EMPTY_CLOB() );

Snowflake

Query

OUT -> Oracle_01.sql

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

INSERT INTO nclobtable
VALUES ( 'THIS IS A TEST', TO_VARCHAR(' - '));

Retrieving Data

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM nclobtable;

Result

NCLOB_COLUMN  |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST|            |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
nclobtable;

Result

NCLOB_COLUMN  |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST| -          |

Known Issues

1. The difference in max length CLOB (Oracle) and VARCHAR (Snowflake)

An Oracle NCLOB column maximum size is (4 gigabytes - 1) * (database block size), but Snowflake VARCHAR is limited to 16MB.

2. Empty value with EMPTY_CLOB

Initializing a column using EMPTY_CLOB() will return an empty LOB locator. While after translation the column will return a string with ' - '.

3. No access to the DBMS_LOB built-in package

Since LOB data types are not supported in Snowflake there is not an equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.

Related EWIs

No related EWIs.