PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# C
CHAR versus VARCHAR2 Semantics

This appendix explains the semantic differences between the `CHAR` and `VARCHAR2` base types. These subtle but important differences come into play when you assign, compare, insert, update, select, or fetch character values.

##### Major Topics

Assigning Character Values
Comparing Character Values
Inserting Character Values
Selecting Character Values

## Assigning Character Values

When you assign a character value to a `CHAR` variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. So, information about trailing blanks is lost. For example, given the following declaration, the value of `name` includes six trailing blanks, not just one:

    name CHAR(10) := 'CHEN ';  -- note trailing blank

If the character value is longer than the declared length of the `CHAR` variable, PL/SQL aborts the assignment and raises the predefined exception `VALUE_ERROR`. PL/SQL neither truncates the value nor tries to trim trailing blanks. For example, given the declaration

    acronym CHAR(4);

the following assignment raises `VALUE_ERROR`:

    acronym := 'SPCA ';  -- note trailing blank

When you assign a character value to a `VARCHAR2` variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, so no information is lost. If the character value is longer than the declared length of the `VARCHAR2` variable, PL/SQL aborts the assignment and raises `VALUE_ERROR`. PL/SQL neither truncates the value nor tries to trim trailing blanks.

## Comparing Character Values

You can use the relational operators to compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. One character value is greater than another if it follows it in the collating sequence. For example, given the declarations

    name1 VARCHAR2(10) := 'COLES';
    name2 VARCHAR2(10) := 'COLEMAN';

the following IF condition is true:

    IF name1 > name2 THEN ...

ANSI/ISO SQL requires that two character values being compared have equal lengths. So, if both values in a comparison have datatype `CHAR`, blank-padding semantics are used. That is, before comparing character values of unequal length, PL/SQL blank-pads the shorter value to the length of the longer value. For example, given the declarations

    name1 CHAR(5) := 'BELLO';
    name2 CHAR(10) := 'BELLO   ';  -- note trailing blanks

the following `IF` condition is true:

    IF name1 = name2 THEN ...

If either value in a comparison has datatype `VARCHAR2`, non-blank-padding semantics are used. That is, when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths. For example, given the declarations

    name1 VARCHAR2(10) := 'DOW';
    name2 VARCHAR2(10) := 'DOW   ';  -- note trailing blanks

the following `IF` condition is false:

    IF name1 = name2 THEN ...

If one value in a comparison has datatype `VARCHAR2` and the other value has datatype `CHAR`, non-blank-padding semantics are used. But, remember, when you assign a character value to a `CHAR` variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. So, given the declarations

    name1 VARCHAR2(10) := 'STAUB';
    name2 CHAR(10)     := 'STAUB';  -- PL/SQL blank-pads value

the following `IF` condition is false because the value of `name2` includes five trailing blanks:

    IF name1 = name2 THEN ...

All string literals have datatype `CHAR`. So, if both values in a comparison are literals, blank-padding semantics are used. If one value is a literal, blank-padding semantics are used only if the other value has datatype `CHAR`.

## Inserting Character Values

When you insert the value of a PL/SQL character variable into an Oracle database column, whether the value is blank-padded or not depends on the column type, not on the variable type.

When you insert a character value into a `CHAR` database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.

When you insert a character value into a `VARCHAR2` database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.

The same rules apply when updating.

When inserting character values, to ensure that no trailing blanks are stored, use the function `RTRIM`, which trims trailing blanks. An example follows:

    DECLARE
       ...
       my_name VARCHAR2(15);
    BEGIN
       ...
       my_ename := 'LEE   ';  -- note trailing blanks
       INSERT INTO emp
          VALUES (my_empno, RTRIM(my_ename), ...);  -- inserts 'LEE'

## Selecting Character Values

When you select a value from an Oracle database column into a PL/SQL character variable, whether the value is blank-padded or not depends on the variable type, not on the column type.

When you select a column value into a `CHAR` variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. As a result, information about trailing blanks is lost. If the character value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises the exception `VALUE_ERROR`.

When you select a column value into a `VARCHAR2` variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are stored intact, so no information is lost.

For example, when you select a blank-padded `CHAR` column value into a `VARCHAR2` variable, the trailing blanks are not stripped. If the character value is longer than the declared length of the `VARCHAR2` variable, PL/SQL aborts the assignment and raises `VALUE_ERROR`.

The same rules apply when fetching.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index