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

IN -> Oracle_01.sql

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

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE long_table
 (
      id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
      long_column VARCHAR
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

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

Retrieving data from a Long column

Oracle

Query

IN -> Oracle_02.sql

SELECT long_column FROM long_table;

Result

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

Snowflake

Query

OUT -> Oracle_02.sql

SELECT long_column FROM
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. SSC-FDM-0006: Number type column may not behave similarly in Snowflake