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.
This appendix discusses the following 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. In the following example, the
value assigned to last_name
includes six trailing blanks, not just one:last_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 declarationacronym 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
last_name1 VARCHAR2(10) := 'COLES'; last_name2 VARCHAR2(10) := 'COLEMAN';
the following IF condition is true:
IF last_name1 > last_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 declarationslast_name1 CHAR(5) := 'BELLO'; last_name2 CHAR(10) := 'BELLO '; -- note trailing blanks
the following
IF
condition is true:IF last_name1 = last_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 declarationslast_name1 VARCHAR2(10) := 'DOW'; last_name2 VARCHAR2(10) := 'DOW '; -- note trailing blanks
the following
IF
condition is false:IF last_name1 = last_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 declarationslast_name1 VARCHAR2(10) := 'STAUB'; last_name2 CHAR(10) := 'STAUB'; -- PL/SQL blank-pads value
the following
IF
condition is false because the value of last_name2
includes five trailing blanks:IF last_name1 = last_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.
Note: 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' END;
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 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
.
Note: The same rules apply when fetching.
No comments:
Post a Comment