Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle NVARCHAR2

Oracle NVARCHAR2

Summary: this tutorial introduces you to the Oracle NVARCHAR2 data type and explains the differences between NVARCHAR2 and VARCHAR2.

Introduction to Oracle NVARCHAR2 data type

The NVARCHAR2 is Unicode data type that can store Unicode characters. The character set of the NVARCHAR2 is national character set specified at the database creation time.

To find the character set of the NVARCHAR2 in your database, you use the following query:

SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
Oracle NVARCHAR2 default nls character set

In our Oracle database server, the NVARCHAR2 data type uses AL16UTF16 character set which encodes Unicode data in the UTF-16 encoding. The AL16UTF16 use 2 bytes to store a character.

The NVARCHAR2 stores variable-length character data. When you create a table with the NVARCHAR2 column, the maximum size is always in character length semantics, which is also the default and only length semantics for the NVARCHAR2 data type.

Oracle NVARCHAR2 examples

The following statement creates a table with an NVARCHAR2 column whose maximum length is 50 characters.

CREATE TABLE nvarchar2_demo ( description NVARCHAR2(50) );

Because the current national character set is UTF-16, the maximum byte length of the description column is 200 bytes.

Note that the maximum byte length is the product of the maximum character length and the maximum number of bytes in each character.

The following statement inserts a row into the nvarchar2_demo table:

INSERT INTO nvarchar2_demo VALUES('ABCDE');

We use the DUMP() function to find the detailed information on the value stored in the nvarchar2_demo table:

SELECT description, DUMP(description,1016) FROM nvarchar2_demo;
Oracle NVARCHAR2 example

As shown in the result, the datatype code 1 is and the length is 10 bytes (5 characters, 2 bytes each).

 VARCHAR2 vs. NVARCHAR2

First, the maximum size of VARCHAR2 can be in either bytes or characters, whereas the maximum size of NVARCHAR2 is only in characters. In addition, the maximum byte length of an NVARCHAR2 depends on the configured national character set.

Second, a VARCHAR2 column only can store characters in the default character set while the NVARCHAR2 can store virtually any characters

The following query returns the default character set used by the VARCHAR2 data type.

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
Oracle NVARCHAR2 - default character set for VARCHAR2

In this tutorial, you have learned about Oracle NVARCHAR2 and the differences between NVARCHAR2 and VARCHAR2.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle VARCHAR2
Next Oracle DATE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.