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

Query

IN -> Oracle_01.sql

CREATE TABLE nvarchar2_data_types
(
	nvarchar2_column NVARCHAR2 (5)
);

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

Snowflake

Query

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

Query

IN -> Oracle_02.sql

SELECT * FROM nvarchar2_data_types;

Result

NVARCHAR2_COLUMN
----------------
ភាសាខ           |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
nvarchar2_data_types;

Result

NVARCHAR2_COLUMN|
----------------+
ភាសាខ           |

Retrieving the size in bytes of each column

Oracle

Query

IN -> Oracle_03.sql

SELECT 
LENGTHB(nvarchar2_column)
FROM nvarchar2_data_types;

Result

LENGTHB(NVARCHAR2_COLUMN)|
-------------------------+
                       10|

Snowflake

Query

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;

Result

OCTET_LENGTH(NVARCHAR2_COLUMN)|
------------------------------+
                            15|

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.

Related EWIs

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