VARCHAR2 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

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

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
varchar2_data_types;

Result

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

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

Known Issues

No issues were found.

Related EWIs

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