Lexical elements

The lexical elements of PL are the smallest independent components, including separators, identifiers, text, compilation directives, and comments.

Separators

Separators are characters or character combinations that have special meanings in PL/SQL. Do not embed any other characters (including space characters) in separators.

The following table describes the separators supported by PL/SQL.

Identifiers

Identifiers are used to name PL/SQL elements, including constants, cursors, exceptions, keywords, labels, packages, reserved words, subprograms, types, and variables.

Each character in an identifier (regardless of whether it is a letter) has meaning. For example, the identifiers firstname and first_name are different.

You must separate adjacent identifiers with one or more space characters or punctuation marks.

Unless quoted, PL/SQL identifiers are not case-sensitive. For example, the identifiers firstname, FirstName, and FIRSTNAME are the same.

Reserved keywords

Reserved keywords are identifiers with special meanings in PL/SQL.

We do not recommend that you use reserved keywords as user-defined identifiers. For more information about PL/SQL reserved keywords, see PL/SQL reserved keywords.

Predefined identifiers

Predefined identifiers are declared using the predefined package STANDARD.

For more information about predefined identifiers, see Definition of the STANDARD package in the PL/SQL environment.

You can use predefined identifiers as user-defined identifiers. However, local declarations override global declarations. We do not recommend that you do this.

User-defined identifiers

User-defined identifiers are ordinary or quoted identifiers composed of characters from the database character set.

Ordinary user-defined identifiers

You can define ordinary user-defined identifiers. PL/SQL has the following requirements and restrictions for user-defined identifiers:

• The first character must be a letter.

• The identifier can contain letters, digits, and the following characters: /, *, &, and $.

• The identifier cannot be longer than 128 characters.

• You cannot use PL/SQL reserved words as identifiers.

• PL/SQL identifiers are not case-sensitive.

The following identifiers are valid:

t8
telephone#
credit_limit
FirstName
OceanBase$number
money$$tree
SN##
try_again_

Quoted identifiers

Quoted identifiers are enclosed in double quotation marks.

You can use any character from the database character set between the quotation marks, except for quotation marks, line breaks, and the null character.

The following identifiers are valid:

"X+Y"
"first name"
"on/off switch"
"department(s)"
"*** body info ***"

Quoted identifiers are case-sensitive. However, if a quoted identifier (without quotation marks) is a valid ordinary user-defined identifier, the quotation marks are optional when the identifier is referenced. If you omit the quotation marks, the identifier is not case-sensitive.

You can use reserved keywords as quoted identifiers, but we do not recommend that you do this. Because reserved keywords are not valid ordinary user-defined identifiers, you must always enclose them in double quotation marks, and they are case-sensitive.

obclient> DECLARE
    "HELLO" varchar2(10) := 'hello';    -- HELLO is a user-defined identifier
    "ALL" varchar2(10) := 'all';             -- ALL is a reserved keyword
BEGIN
    DBMS_Output.Put_Line(Hello);       -- You can omit the quotation marks, which makes the identifier not case-sensitive.
    DBMS_Output.Put_Line("ALL");      -- You must use quotation marks.
end;
/

The output is as follows:

hello
all

Text

The value of text is not represented by an identifier or calculated from other values.

For example, 119 is integer text, and 'xyz' is character text. However, 1+2 is not text.

PL/SQL text includes all SQL text and BOOLEAN text. SQL does not support BOOLEAN text. BOOLEAN text is predefined logical values TRUE, FALSE, or NULL. NULL indicates an unknown value.

When you use PL/SQL character text, note the following:

• Character text is case-sensitive. For example, " A" and " a" are different.

• Space characters have meaning. For example, 'abc' and 'a b c' are different.

• PL/SQL does not support a line continuation character. If a string continues on the next line, the string must be followed by a line break. You can also use the concatenation operator (||) to avoid line breaks.

obclient> BEGIN
  DBMS_OUTPUT.PUT_LINE('The string
                       breaks here.');
  DBMS_OUTPUT.PUT_LINE('The string ' ||
                       'contains no line-break character.');
END;
/

The output is as follows:

The string
breaks here.
The string contains no line-break character.

• Characters from "0" to "9" are not equal to the integer values 0 to 9. However, PL/SQL converts them to integers, so you can use them in arithmetic expressions.

• A character text with zero characters has the value NULL, which is called an empty string. However, this NULL value is not the NULL value of BOOLEAN.

• Ordinary character text is composed of characters from the database character set.

• National character text is composed of characters from the national character set.

Compiler directives

Compiler directives are instructions that the compiler processes during compilation.

Compiler directives start with the keyword PRAGMA and are followed by the name of the directive. Compiler directives can appear in declarations or before declarations. Specific compiler directives may have additional restrictions. Some directives include parameters. If the compiler cannot recognize the name or parameters of a directive, the directive is invalid.

The following table lists the compiler directives supported by PL/SQL:

Examples

Example 1: Using the SERIALLY_REUSABLE Pragma

CREATE PACKAGE pkg1 IS
   PRAGMA SERIALLY_REUSABLE;
   num NUMBER := 0;
   PROCEDURE init_pkg_state(n NUMBER);
   PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
   PRAGMA SERIALLY_REUSABLE;
   PROCEDURE init_pkg_state (n NUMBER) IS
   BEGIN
      pkg1.num := n;
   END;
   PROCEDURE print_pkg_state IS
   BEGIN
      dbms_output.put_line('Num: ' || pkg1.num);
   END;
END pkg1;
/
DROP PACKAGE pkg1;

Example 2: Using the AUTONOMOUS_TRANSACTION Pragma

CREATE OR REPLACE PROCEDURE log_error(message VARCHAR2) IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log (error_message, log_date) VALUES (message, SYSDATE);
   COMMIT;
END log_error;

Example 3: Using the EXCEPTION_INIT Pragma

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   -- Code that triggers a deadlock exception
   -- ...
EXCEPTION
   WHEN deadlock_detected THEN
      DBMS_OUTPUT.PUT_LINE('A deadlock was detected.');
END;

Example 4: Using the INLINE Pragma

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER) RETURN NUMBER IS
   PRAGMA INLINE(add_numbers, 'YES');
BEGIN
   RETURN a + b;
END add_numbers;

Comments

Comments are used to help other application developers understand your source text. The PL/SQL compiler ignores the content of comments.

Typically, comments are used to describe the purpose of each code segment. You can also comment out obsolete or incomplete code segments to disable them.

Single-line comments

Single-line comments start with -- and extend to the end of the line.

When testing or debugging a program, you can comment out a line of code. For example:

-- INSERT INTO t1 VALUES(1, 1)

Multi-line comments

Multi-line comments start with / * and end with * /, and can span multiple lines.

You can use multi-line comment delimiters to "comment out" a code segment. Note that you cannot nest multi-line comments. A multi-line comment cannot contain another multi-line comment. However, a multi-line comment can contain single-line comments.

The following example results in a syntax error:

/*
  IF 1 + 2 = 3 THEN
    some_condition := TRUE;
  /* Needed
THEN
 The statement is always executed */
  END IF;
*/

Whitespace between tokens

You can add whitespace between tokens to make the source text more readable.

Here is an example:

obclient> DECLARE
  a    NUMBER := 5;
  b    NUMBER := 1;
  max  NUMBER;
BEGIN
  IF a>b THEN max:=a;ELSE max:=b;END IF;  -- Correct syntax but not easy to read

  -- The following statement is easier to read:

  IF a > b THEN
    max:=a;
  ELSE
    max:=b;
  END IF;
END;
/