NVARCHAR2 Data Type

Description

The NVARCHAR2 data type specifies a variable-length character string in the national character set. (Oracle SQL Language Reference NVARCHAR2)

NVARCHAR2 (size)

NVARCHAR2 allows to store special characters with their Unicode to be preserved across any usage, these special characters may need more bits to be stored and that is why, by default, the NVARCHAR2 character set is AL16UTF16, contrary to the common character data set for VARCHAR2 which is usually AL32UTF8.

NVARCHAR transformed to Snowflake VARCHAR, Transformation information related to VARCHAR2, is also valid for NVARCHAR2.

NVARCHAR2 (size)

Sample Souce Patterns

Nvarchar2 data type in Create Table

Oracle

IN -> Oracle_01.sql
CREATE TABLE nvarchar2_data_types
(
	nvarchar2_column NVARCHAR2 (5)
);

INSERT INTO nvarchar2_data_types VALUES ('ភាសាខ');

Snowflake

OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE nvarchar2_data_types
	(
		nvarchar2_column VARCHAR(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO nvarchar2_data_types
	VALUES ('ភាសាខ');

In Oracle, trying to insert these values in a VARCHAR2 column with the same size, will trigger an error: value too large for column.

Retrieving information from Nchar columns

Oracle

IN -> Oracle_02.sql
SELECT * FROM nvarchar2_data_types;

Snowflake

OUT -> Oracle_02.sql
SELECT * FROM
nvarchar2_data_types;

Retrieving the size in bytes of each column

Oracle

IN -> Oracle_03.sql
SELECT 
LENGTHB(nvarchar2_column)
FROM nvarchar2_data_types;

Snowflake

OUT -> Oracle_03.sql
SELECT
OCTET_LENGTH(nvarchar2_column) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nvarchar2_data_types;

Note that the number specified in the column declaration is the size in characters and not in bytes, That is why we see more space used to store those special characters.

In Snowflake, VARCHAR uses UTF-8, size can vary depending on the Unicode character that can be represented in 1, 2, 3, or 4 bytes. In this case, the Cambodian characters are using 3 bytes to be stored.

Besides these slight differences, the integrity of the data is preserved.

Known Issues

1. Results obtained from some built-in functions may vary

As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.

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

Last updated