Comparison operators cannot be used to place restrictions on character-based attributes.
This chapter explains these aspects of the PL/SQL language: Show
Character SetsAny character data to be processed by PL/SQL or stored in a database must be represented as a sequence of bytes. The byte representation of a single character is called a character code. A set of character codes is called a character set. Every Oracle database supports a database character set and a national character set. PL/SQL also supports these character sets. This document explains how PL/SQL uses the database character set and national character set. Topics
Database Character SetPL/SQL uses the database character set to represent:
The database character set can be either single-byte, mapping each supported character to one particular byte, or multibyte-varying-width, mapping each supported character to a sequence of one, two, three, or four bytes. The maximum number of bytes in a character code depends on the particular character set. Every database character set includes these basic characters:
PL/SQL source text that uses only the basic characters can be stored and compiled in any database. PL/SQL source text that uses nonbasic characters can be stored and compiled only in databases whose database character sets support those nonbasic characters. Table 2-1 Punctuation Characters in Every Database Character Set
National Character SetPL/SQL uses the national character set to represent character values of data types Lexical UnitsThe lexical units of PL/SQL are its smallest individual components—delimiters, identifiers, literals, and comments. Topics
DelimitersA delimiter is a character, or character combination, that has a special meaning in PL/SQL. Do not embed any others characters (including whitespace characters) inside a delimiter. Table 2-2 summarizes the PL/SQL delimiters. Table 2-2 PL/SQL Delimiters
IdentifiersIdentifiers name PL/SQL elements, which include:
Every character in an identifier, alphabetic or not, is significant.
For example, the identifiers You must separate adjacent identifiers by one or more whitespace characters or a punctuation character. Except as explained in "Quoted User-Defined Identifiers", PL/SQL is case-insensitive for identifiers. For example, the identifiers Topics
Reserved Words and KeywordsReserved words and keywords are identifiers that have special meaning in PL/SQL. You cannot use reserved words as ordinary user-defined identifiers. You can use them as quoted user-defined identifiers, but it is not recommended. For more information, see "Quoted User-Defined Identifiers". You can use keywords as ordinary user-defined identifiers, but it is not recommended. For lists of PL/SQL reserved words and keywords, see Table D-1 and Table D-2, respectively. Predefined IdentifiersPredefined identifiers are declared in the predefined package For a list of predefined identifiers, connect to Oracle Database as a user who has the DBA role and use this query: SELECT TYPE_NAME FROM ALL_TYPES WHERE PREDEFINED='YES'; You can use predefined identifiers as user-defined identifiers, but it is not recommended. Your local declaration overrides the global declaration (see "Scope and Visibility of Identifiers"). User-Defined IdentifiersA user-defined identifier is:
Tip: Make user-defined identifiers meaningful. For example,
the meaning of Ordinary User-Defined IdentifiersAn ordinary user-defined identifier:
The database character set defines which characters are classified as letters and digits. The representation of the identifier in the database character set cannot exceed 30 bytes. Examples of acceptable ordinary user-defined identifiers: X t2 phone# credit_limit LastName oracle$number money$$$tree SN## try_again_ Examples of unacceptable ordinary user-defined identifiers: mine&yours debit-amount on/off user id Quoted User-Defined IdentifiersA quoted user-defined identifier is enclosed in double quotation marks. Between the double quotation marks, any characters from the database character set are allowed except double quotation marks, new line characters, and null characters. For example, these identifiers are acceptable: "X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***" The representation of the quoted identifier in the database character set cannot exceed 30 bytes (excluding the double quotation marks). A quoted user-defined identifier is case-sensitive, with one exception: If a quoted user-defined identifier, without its enclosing double quotation marks, is a valid ordinary user-defined identifier, then the double quotation marks are optional in references to the identifier, and if you omit them, then the identifier is case-insensitive. In Example
2-1, the quoted user-defined identifier Example 2-1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier DECLARE "HELLO" varchar2(10) := 'hello'; BEGIN DBMS_Output.Put_Line(Hello); END; / Result: hello In Example 2-2, the reference
Example 2-2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier DECLARE "HELLO" varchar2(10) := 'hello'; BEGIN DBMS_Output.Put_Line("Hello"); END; / Result: DBMS_Output.Put_Line("Hello"); * ERROR at line 4: ORA-06550: line 4, column 25: PLS-00201: identifier 'Hello' must be declared ORA-06550: line 4, column 3: PL/SQL: Statement ignored It is not recommended, but you can use a reserved word as a quoted user-defined identifier. Because a reserved word is not a valid ordinary user-defined identifier, you must always enclose the identifier in double quotation marks, and it is always case-sensitive. Example 2-3 declares quoted user-defined identifiers Example 2-3 Reserved Word as Quoted User-Defined Identifier DECLARE "BEGIN" varchar2(15) := 'UPPERCASE'; "Begin" varchar2(15) := 'Initial Capital'; "begin" varchar2(15) := 'lowercase'; BEGIN DBMS_Output.Put_Line("BEGIN"); DBMS_Output.Put_Line("Begin"); DBMS_Output.Put_Line("begin"); END; / Result: UPPERCASE Initial Capital lowercase PL/SQL procedure successfully completed. Example 2-4 references a quoted user-defined identifier that is a reserved word, neglecting to enclose it in double quotation marks. Example 2-4 Neglecting Double Quotation Marks DECLARE "HELLO" varchar2(10) := 'hello'; -- HELLO is not a reserved word "BEGIN" varchar2(10) := 'begin'; -- BEGIN is a reserved word BEGIN DBMS_Output.Put_Line(Hello); -- Double quotation marks are optional DBMS_Output.Put_Line(BEGIN); -- Double quotation marks are required end; / Result: DBMS_Output.Put_Line(BEGIN); -- Double quotation marks are required * ERROR at line 6: ORA-06550: line 6, column 24: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ( ) - + case mod new not null |