PL/SQL User's Guide and Reference | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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 := 'SPCAX'; -- 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.
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 or both values in a comparison have 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.
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 CHAR 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 VARCHAR2 column, Oracle aborts the insert and generates an error.
The same rules apply when updating.
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 CHAR variable, PL/SQL aborts the assignment and raises the predefined 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.
SQL> SET COMPATIBILITY V6
As the next example shows, in the Oracle Precompiler environment, you enter the runtime option DBMS on the command line, specifying the value V6, V7, or NATIVE (the default). NATIVE specifies the version of Oracle resident on your system, which must be version 6 or later.
... DBMS=V6
When selecting data over a V7-to-V6 link, use VARCHAR2 variables in the WHERE clause instead of CHAR variables. Otherwise, you might get an unsupported network datatype error.
When inserting character values, you can ensure that no trailing blanks are stored by using the RTRIM function, which trims trailing blanks. An example follows:
my_empno := 7471;
my_ename := 'LEE '; -- note trailing blanks
...
INSERT INTO emp
VALUES (my_empno, RTRIM(my_ename), ...); -- inserts 'LEE'
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |