VARCHAR2 Data Type
Description
The
VARCHAR2
data type specifies a variable-length character string in the database character set. (Oracle SQL Language Reference VARCHAR2)
As denoted in the Oracle documentation, size in VARCHAR2 data type is a length constraint and should not be confused with capacity. Total characters that can be stored in a VARCHAR2 may vary according to the database character set and configuration, but commonly the maximum size allowed is 4000.
VARCHAR2 is translated to Snowflake VARCHAR which can store a bigger number of bytes/characters by default. Either way, the memory used is variable using the size of the value stored in the column as same as in Oracle.
VARCHAR2 (size [ BYTE | CHAR ])
Sample Source Patterns
Varchar2 data types in Create Table
Oracle
CREATE TABLE varchar2_data_types
(
varchar2_column1 VARCHAR2(5),
varchar2_column2 VARCHAR2(5 BYTE),
varchar2_column3 VARCHAR2(5 CHAR)
);
INSERT INTO varchar2_data_types VALUES ('H', 'Hello', 'Hell');
Snowflake
CREATE OR REPLACE TABLE varchar2_data_types
(
varchar2_column1 VARCHAR(5),
varchar2_column2 VARCHAR(5),
varchar2_column3 VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO varchar2_data_types
VALUES ('H', 'Hello', 'Hell');
Retrieving data from varchar columns
Oracle
SELECT * FROM varchar2_data_types;
Snowflake
SELECT * FROM
varchar2_data_types;
Reviewing the variable size in the columns
Oracle
SELECT
LENGTHB(varchar2_column1),
LENGTHB(varchar2_column2),
LENGTHB(varchar2_column3)
FROM VARCHAR2_DATA_TYPES;
Snowflake
SELECT
OCTET_LENGTH(varchar2_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
VARCHAR2_DATA_TYPES;
Known Issues
No issues were found.
Related EWIs
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
Last updated