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
CREATE TABLE nvarchar2_data_types
(
nvarchar2_column NVARCHAR2 (5)
);
INSERT INTO nvarchar2_data_types VALUES ('ភាសាខ');
Snowflake
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 ('ភាសាខ');
Retrieving information from Nchar columns
Oracle
SELECT * FROM nvarchar2_data_types;
Snowflake
SELECT * FROM
nvarchar2_data_types;
Retrieving the size in bytes of each column
Oracle
SELECT
LENGTHB(nvarchar2_column)
FROM nvarchar2_data_types;
Snowflake
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.
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
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
Last updated