ORACLE-The differences of VARCHAR2 (5) and VARCHAR2 (5 byte)

Introduction

This article explains the differences of VARCHAR2 (5) and VARCHAR2 (5 byte) in ORACLE database. Some application developers have the confusion about the way of defining VARCHAR2 data types. The VARCHAR2 definition is much important when the end-user using the multi-byte languages like Japanese, Chinese..etc

Technologies:

ORACLE 10g

Language:

SQL and PLSQL

Consider the following declarations.

varA VARCHAR2(5 byte); -- You can store up to 5 bytes

varB VARCHAR2(5) -- You can store up to 5 characters

But we need to be careful when we try to store multi-byte characters like Japanese characters. Here each character needs more than one byte to store in the database.

When we use VARCHAR2(5 byte) definition and if the Japanese character takes 3 bytes then we can save only one character to that column.

When we use VARCHAR2(5) definition and if the Japanese character takes 3 bytes still we can save only one character to that column even though by definition we can store up to 5 characters.

Consider the following example.

CREATE TABLE test_varchar(
colA VARCHAR2(5),
colB VARCHAR2(5 byte));

Table created

INSERT INTO test_varchar
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar
VALUES ('aaaaa','気木機能性');

ORA-12899: value too large for column "TEST_VARCHAR"."COLB" (actual: 15, maximum: 5)

INSERT INTO test_varchar
VALUES ('気木機能性', 'aaaaa');

ORA-12899: value too large for column "TEST_VARCHAR"."COLA" (actual: 15, maximum: 5)

When we used the “VARCHAR2(5)” definition we receive the “Value too large” error because by default, the character datatypes (CHAR and VARCHAR2) are specified in bytes , not in characters. Hence, the specification VARCHAR2(5)in a table definition allows 5 bytes for storing character data.

How to overcome this issue?

We can overcome this problem by switching to character semantics when defining the column size.

NLS_LENGTH_SEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.

We can set the NLS_LENGTH_SEMANTICS as CHAR.

Now consider the following example.

ALTER SESSION
SET NLS_LENGTH_SEMANTICS = 'CHAR';

CREATE TABLE test_varchar2(
colC VARCHAR2(5),
colD VARCHAR2(5 byte));
Table created

INSERT INTO test_varchar2
VALUES ('aaaaa','bbbbb');

1 row inserted

INSERT INTO test_varchar2
VALUES ('気木機能性', 'aaaaa');

1 row inserted

Now we can successfully save 5 multi-byte characters into the colC in test_varchar2 table.

Setting NLS Parameters

NLS (National Language Support) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:

• § As initialization parameters on the server

You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:

NLS_TERRITORY = "CZECH REPUBLIC"

• § As environment variables on the client

You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:

% setenv NLS_SORT FRENCH

• § With the ALTER SESSION statement

You can use NLS parameters that are set in an ALTER SESSION statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.

ALTER SESSION SET NLS_SORT = FRENCH;

• § In SQL functions

You can use NLS parameters explicitly to hardcode NLS behavior within a SQL function. This practice overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement. For example:

TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Methods of Setting NLS Parameters and Their Priorities

NLS Data Dictionary Views

We can check the session, instance, and database NLS parameters by querying the following data dictionary views:

• NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.
• v NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
• v NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.

Conclusion

The ORACLE NLS parameters have influenced in the CHAR and VARCHAR2 data type definitions. By using the NLS parameters we can easily achieve the globalization in the applications.

Aaruran.

http://aaruran-ora.blogspot.com/