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

Query

IN -> Oracle_01.sql

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

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

SELECT * FROM varchar2_data_types;

Result

VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H               |Hello           |Hell            |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
varchar2_data_types;

Result

VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H               |Hello           |Hell            |

Reviewing the variable size in the columns

Oracle

Query

IN -> Oracle_03.sql

SELECT
LENGTHB(varchar2_column1),
LENGTHB(varchar2_column2),
LENGTHB(varchar2_column3) 
FROM VARCHAR2_DATA_TYPES;

Result

LENGTHB(VARCHAR2_COLUMN1)|LENGTHB(VARCHAR2_COLUMN2)|LENGTHB(VARCHAR2_COLUMN3)|
-------------------------+-------------------------+-------------------------+
                        1|                        5|                        4|

Snowflake

Query

OUT -> Oracle_03.sql

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;

Result

OCTET_LENGTH(VARCHAR2_COLUMN1)|OCTET_LENGTH(VARCHAR2_COLUMN2)|OCTET_LENGTH(VARCHAR2_COLUMN3)|
------------------------------+------------------------------+------------------------------+
                             1|                             5|                             4|

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.