Home

VARCHAR2 and NVARCHAR2 should be used

Description

    The rule "VARCHAR2 and NVARCHAR2 should be used" states that the VARCHAR2 and NVARCHAR2 data types should be used when creating columns in a database table. These data types are used to store character strings, and are the preferred data types for storing character strings in Oracle databases. VARCHAR2 is used for storing strings of up to 4000 characters, while NVARCHAR2 is used for storing strings of up to 2000 characters. Both data types are used for storing character strings, but NVARCHAR2 should be used when the data is Unicode-encoded, as it is more efficient than VARCHAR2 for storing Unicode-encoded data.

Key Benefits

  • Increased Storage Capacity: VARCHAR2 and NVARCHAR2 offer increased storage capacity compared to CHAR and NCHAR data types. This allows for more data to be stored in the same amount of space.
  • Improved Performance: VARCHAR2 and NVARCHAR2 have improved performance compared to CHAR and NCHAR data types. This allows for faster query processing and improved overall system performance.
  • Flexibility: VARCHAR2 and NVARCHAR2 offer more flexibility than CHAR and NCHAR data types. This allows for the data to be stored in a more efficient manner and makes it easier to modify the data in the future.

 

Non-compliant Code Example

DECLARE 
  firstname NVARCHAR2(75);
  lastname NVARCHAR2(75);
  landmark NCHAR(75);          --Non compliant code (NCHAR is used)

BEGIN
	SELECT FIRSTNAME, LASTNAME, LANDMARK 
	INTO firstname,lastname,landmark
	FROM CUSTOMERS;
END;

Compliant Code Example

DECLARE 
  firstname NVARCHAR2(75);
  lastname NVARCHAR2(75);
  landmark NVARCHAR2(75);          --Compliant code (VARCHAR2 OR NVARCHAR2 is used)

BEGIN
	SELECT FIRSTNAME, LASTNAME, LANDMARK 
	INTO firstname,lastname,landmark
	FROM CUSTOMERS;
END;
Visual Expert 2024
 VEPLSQLRULE58