8.1.4.5 Equality of Unicode strings

When testing two character strings for equality, PL/SQL doesn't take into account the fact that a single Unicode character may have more than one representation. A string containing the single code point 0x00E3 to represent the character ã is not considered the equivalent of a string containing the two code points 0x0061 and 0x0303. For example:

DECLARE

x NVARCHAR2(30);

y NVARCHAR2(30);

BEGIN

x := UNISTR('\00E3');

y := UNISTR('\0061\0303');

IF x = y THEN

DBMS_OUTPUT.PUT_LINE('x = y');

ELSIF x <> y THEN

DBMS_OUTPUT.PUT_LINE('x <> y');

END IF;

END;

The output is:

x <> y

You can use the COMPOSE function, described later in this chapter, to deal with this particular situation. In this specific instance, you would write:

IF COMPOSE(x) = COMPOSE(y)

There is one situation where COMPOSE won't help, though, and that is when a character can be represented both by a surrogate pair and by its code point value.

In Oracle9i Release 1, you must call COMPOSE from within a SQL statement. In Release 2, you can also invoke COMPOSE in a PL/SQL statement.

8.1.4.6 Sort order

Sort order isperhaps the only character set issue that is not an extension of the basic bytes-versus-characters issue. Different character sets use different numeric values for the various characters represented. This affects comparison statements in which one string or character is compared to another. Is the uppercase letter "A" greater than or less than the lowercase letter "a"? It depends. If you're using an ASCII-based character set, you'll find that uppercase letters are "less than" lowercase letters. If you're using EBCDIC, however, you'll find the opposite to be true.

8.2 String Datatypes

Oracle supports four string datatypes, which are summarized in Table 8-1. Which type you should use depends on your answers to the following two questions:

• Are you working with variable-length or fixed-length strings?

• Do you wish to use the database character set or the national character set?

Table 8-1. PL/SQL's string datatypes
	Fixed-length	Variable-length
Database character set	CHAR	VARCHAR2
National character set	NCHAR	NVARCHAR2

You will rarely need or want to use the fixed-length CHAR and NCHAR datatypes in Oracle-based applications; in fact, we recommend that you never use these types unless there is a specific requirement for fixed-length strings. See the upcoming Section 8.3 for a description of problems you may encounter when mixing fixed- and variable-length string variables.

8.2.1 The varchar2 Datatype

VARCHAR2 variables store variable-length character strings. When you declare a variable-length string, you must also specify a maximum length for the string, which can range from 1 to 32767 bytes. You may specify the maximum length in terms of characters or bytes, but either way the length is ultimately defined in bytes. The general format for aVARCHAR2 declaration is:

variable_name VARCHAR2 (max_length [CHAR | BYTE]);

where:

variable_name

Is the name of the variable you wish to declare.

max_length

Is the maximum length of the variable.

CHAR

Indicates that max_length is expressed in terms of characters.

BYTE

Indicates that max_length represents a number of bytes.

When you specify the maximum length of a VARCHAR2 string in terms of characters (using the CHAR qualifier), the actual length in bytes is determined using the largest number of bytes that the database character set uses to represent a character. For example, the Unicode UTF-8 character set uses up to three bytes for some characters; thus, if UTF-8 is your underlying character set, declaring a VARCHAR2 variable with a maximum length of 100 characters is equivalent to declaring the same variable with a maximum length of 300 bytes.

If you omit the CHAR or BYTE qualifier when declaring a VARCHAR2 variable, then whether the size is in characters or bytes depends on the NLS_LENGTH_SEMANTICS parameter. NLS_LENGTH_SEMANTICS defaults to BYTE, so you'll find in practice that omitting the qualifier usually results in byte-sized variables.

Following are some examples of VARCHAR2 declarations:

DECLARE

small_string VARCHAR2(4);

line_of_text VARCHAR2(2000);

feature_name VARCHAR2(100 BYTE); -- 100 byte string

emp_name VARCHAR2(30 CHAR); -- 30 character string

The maximum length allowed for PL/SQL VARCHAR2 variables is 32,767 bytes, a much higher maximum than that for the VARCHAR2 datatype in the Oracle RDBMS (2000 bytes prior to Oracle8i, and 4000 bytes in Oracle8i and above). This size limit applies regardless of whether you declare the variable's size in terms of characters or bytes. As a result of PL/SQL's much higher size limit, if you plan to store a PL/SQL VARCHAR2 value into a VARCHAR2 database column, you must remember that only the first 2000 or 4000 bytes can be inserted, depending on which release of Oracle you are using. Neither PL/SQL nor SQL automatically resolves this inconsistency.

If you need to work with strings greater than 4000 bytes in length (or 2000 prior to Oracle8i), consider storing those strings in CLOB (character large object) columns. See Chapter 12 for information on CLOBs.