CLOB Data Type

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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).

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

Sample Source Patterns

CLOB in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE clobtable ( clob_column CLOB, empty_column CLOB );

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

Snowflake

Query

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

Query

IN -> Oracle_02.sql

SELECT * FROM clobtable;

Result

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
clobtable;

Result

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.

Related EWIs

No related EWIs.