VARCHAR2 Data Type

 The Oracle VARCHAR2 data type is one of the most commonly used data types in Oracle databases. It is used to store variable-length character strings. Unlike the CHAR data type, which is used for fixed-length data, VARCHAR2 is ideal for storing data that can vary in length, such as names, addresses, descriptions, etc.

Here are the detailed notes on the Oracle VARCHAR2 data type:

1. Definition of VARCHAR2

• VARCHAR2 stands for Variable Character String (2-byte).
• It stores variable-length data, meaning it only allocates space for the actual characters stored and does not pad extra spaces, unlike the CHAR data type which pads data to the fixed length.
• The length of the string stored can vary from 1 byte up to a specified limit.

2. Syntax for VARCHAR2

The general syntax for creating a column of the VARCHAR2 data type is:

VARCHAR2(n [CHAR | BYTE])

Where:

• n: Specifies the maximum length of the string to be stored in bytes or characters (depending on the specified unit).
• CHAR or BYTE: Defines whether the length is in characters or bytes. If not specified, the default is bytes.

3. Length Limit

• The maximum length for a VARCHAR2 column is 4000 bytes (for a single column).
• In Oracle 12c and later, if you store a VARCHAR2 column in a PL/SQL variable, the length can go up to 32767 bytes.
• Character Semantics: If you specify CHAR as the length unit, the limit is 4000 characters. If you use BYTE, it can store up to 4000 bytes, and this may result in fewer characters depending on the character encoding (e.g., UTF-8 encoding uses multi-byte characters).

4. Usage and Characteristics

• Storage Efficiency: Unlike CHAR, VARCHAR2 does not pad data with spaces. It only uses the exact amount of space required for the stored data.
• Performance: VARCHAR2 is more efficient for variable-length data because it avoids wasting storage space, especially when the lengths of the values in the column vary.
• Data Length: The VARCHAR2 data type stores up to 4000 bytes/characters of text, and it allows the storage of data that may change in size over time.

5. Why Use VARCHAR2?

• Variable Length: VARCHAR2 is ideal for storing text where the length of the data varies, such as user input, addresses, names, or descriptions.
• Space Efficiency: It is more space-efficient compared to CHAR because it only uses the space necessary to store the string, unlike CHAR, which reserves the full defined space regardless of the actual string length.
• String Operations: It supports operations like string comparison, concatenation, and trimming, and it handles spaces and characters more flexibly than CHAR.

6. Difference Between VARCHAR2 and CHAR

• Length Behavior:
• CHAR is fixed-length, meaning the column will always take up the defined number of characters, padding with spaces if the actual string is shorter.
• VARCHAR2 is variable-length, meaning it only uses as much space as needed for the data, making it more space-efficient for varying-length strings.
• Performance:
• CHAR might be faster for fixed-length data because there is no need to calculate or manage varying lengths.
• VARCHAR2 is more efficient when storing variable-length strings, but might introduce a small overhead when dealing with large amounts of short strings due to the need to track length information.

7. How Does Oracle Store VARCHAR2 Data?

• Oracle stores only the actual characters in a VARCHAR2 column and does not pad with spaces. This makes VARCHAR2 more efficient for storing variable-length data compared to CHAR.
• Character Semantics vs. Byte Semantics: Oracle allows you to define VARCHAR2 columns using either characters or bytes.
• Character Semantics (VARCHAR2(n CHAR)) is used when you want to store exactly n characters, regardless of the character's byte size.
• Byte Semantics (VARCHAR2(n BYTE)) is used when you want to store up to n bytes of data, which might represent fewer than n characters if multi-byte characters are used (e.g., UTF-8 encoding).

8. Examples of Using VARCHAR2

Example 1: Creating a Table with VARCHAR2

CREATE TABLE employees (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(100),

    email VARCHAR2(150)

);

• Here, first_name is defined as a VARCHAR2(50), meaning it can store a string of up to 50 characters. The column will only use the space required to store the actual data (e.g., 'John' will take up 4 bytes, not 50).

Example 2: Inserting Data into VARCHAR2 Columns

INSERT INTO employees (employee_id, first_name, last_name, email)

VALUES (1, 'John', 'Doe', 'johndoe@example.com');

• The first_name field will store 'John', using only 4 bytes of storage. The email field will store the full email address 'johndoe@example.com'.

Example 3: Selecting Data from VARCHAR2 Columns

SELECT first_name, last_name FROM employees;

• When retrieving data from a VARCHAR2 column, Oracle returns only the stored characters without padding.

9. Behavior with NULLs

• VARCHAR2 columns can store NULL values, just like other data types.
• When inserting a NULL, Oracle does not use any space for that column.

10. What Happens if I Insert a Value Exceeding the Column Length?

• If you try to insert a string longer than the defined length of a VARCHAR2 column, Oracle will raise an error.

For example:

-- If column is defined as VARCHAR2(10) and you insert a string of length 15

INSERT INTO employees (first_name) VALUES ('Alexander');

-- This will raise an error because the string is longer than 10 characters.

11. VARCHAR2 with Constraints

You can use constraints like NOT NULL, CHECK, and UNIQUE on VARCHAR2 columns to enforce data integrity.

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50) NOT NULL

);

12. When to Use VARCHAR2

Use VARCHAR2 in scenarios where:

• The length of the data is variable, and you don't want to waste storage space.
• You are dealing with text fields (e.g., names, descriptions, emails) where the length of the data can change.

13. Maximum Length of VARCHAR2 in PL/SQL

• In PL/SQL, the maximum size for a VARCHAR2 variable is 32767 bytes (in versions 12c and later). However, for table columns, the limit is still 4000 bytes in the database.

14. Character Set Considerations

• The number of bytes required to store a string in a VARCHAR2 column depends on the character set being used. For example:
• In ASCII encoding, each character takes 1 byte.
• In UTF-8 encoding, multi-byte characters might require more than 1 byte per character.

15. Performance Considerations

• While VARCHAR2 is more storage-efficient than CHAR, it does involve some additional overhead to store the length of the data and manage variable-length strings.
• For very large text values or when storing large amounts of data in a column, consider using the CLOB data type instead of VARCHAR2.

16. Handling White Spaces in VARCHAR2

• Unlike CHAR, VARCHAR2 does not automatically pad white spaces for shorter strings. However, when comparing strings, leading and trailing spaces matter unless functions like TRIM() are used.

 

Conclusion

The Oracle VARCHAR2 data type is ideal for storing variable-length text data. It is more space-efficient than the CHAR data type because it only uses the space required for the actual string. You should use VARCHAR2 for text fields where the length of data can vary, such as names, addresses, and other descriptions.