CLOB Data Type

Description

A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. (Oracle SQL Language Reference CLOB Data Type).

Sample Source Patterns

CLOB in Create Table

Oracle

IN -> Oracle_01.sql
CREATE TABLE clobtable ( clob_column CLOB, empty_column CLOB );

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

Snowflake

OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE clobtable ( clob_column VARCHAR,
empty_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

Retrieving Data

Oracle

IN -> Oracle_02.sql
SELECT * FROM clobtable;

Snowflake

OUT -> Oracle_02.sql
SELECT * FROM
clobtable;

Known Issues

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

An Oracle CLOB 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 in Snowflake 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.

Last updated