LONG Data Type

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. (Oracle SQL Language Reference Long Data Type)

LONG

Sample Source Patterns

Long in Create Table

Oracle

Query

CREATE TABLE long_table
   ( id 	  NUMBER,
     long_column  LONG);
     
 INSERT INTO long_table VALUES (1, 'this is a text');

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.long_table ( id 	NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
long_column VARCHAR /*** MSC-WARNING - MSCEWI1036 - LONG DATA TYPE CONVERTED TO VARCHAR ***/);

INSERT INTO PUBLIC.long_table VALUES (1, 'this is a text');

Retrieving data from a Long column

Oracle

Query

SELECT long_column FROM long_table;

Result

|LONG_COLUMN   |
|--------------|
|this is a text|

Snowflake

Query

SELECT long_column FROM PUBLIC.long_table;

Result

|LONG_COLUMN   |
|--------------|
|this is a text|

Known Issues

1. The max length of long (Oracle) and varchar (Snowflake) are different

According to Oracle documentation, Long column can store up to 2 gigabytes of data, but Snowflake varchar is limited to 16Mb.

2. Cast of Long column

Long data type only can be cast to CLOB data type, and the only way to achieve this is using the TO_LOB function, this function only works if is used in the select list of a subquery in an INSERT statement. Consider the following sample

Oracle

Query

CREATE TABLE target_table (col CLOB);

INSERT INTO target_table (SELECT TO_LOB(long_column) FROM long_table);

If the target table column data type is different from CLOB, Oracle may insert null values or display an error when attempting to insert the data.

Related EWIs

1. MSCEWI1036: Data type converted to another data type.