Lexical elements

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

Separators

A separator is a character or a combination of characters that have special meaning in PL/SQL. Do not embed any other characters (including space characters) in a separator.

The following table lists 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 specified by double quotation marks, 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.

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 by 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 in the database character set.

Ordinary user-defined identifiers

You can define ordinary user-defined identifiers. PL/SQL has the following requirements and limitations 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 exceed 128 characters in length.

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

• PL/SQL identifiers are not case-sensitive.

The following user-defined 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 in the database character set between the double quotation marks, except for the double quotation mark, newline, and null character.

The following quoted 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 double quotation marks) is a valid ordinary user-defined identifier, the double quotation marks are optional when the identifier is referenced. If you omit the double quotation marks, the identifier is not case-sensitive.

You can use reserved keywords as quoted identifiers. 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 double quotation marks, which makes the identifier not case-sensitive.
    DBMS_Output.Put_Line("ALL");      -- You must use the double quotation marks.
end;
/

The output is as follows:

hello
all

Text

The value of text is neither represented by an identifier nor derived 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 a predefined logical value, which can be 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 line continuation, which is used to indicate that "this string continues on the next source line". If a string continues on the next source 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 they can be used 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 in the database character set.

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

Compilation directives

Compilation directives are instructions processed by the compiler during compilation.

A compilation directive starts with the keyword PRAGMA and is followed by the name of the directive. Compilation directives can appear in declarations or before them. Specific compilation directives may have additional restrictions. Some directives include parameters, and if the compiler cannot recognize the name or parameters, the directive is invalid.

The following table lists the types of compilation directives supported by PL:

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 help other application developers understand your source text. The PL compiler ignores the content of comments.

Typically, comments 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.

During testing or debugging, 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" code segments. Note that nested multi-line comments are not allowed. 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
 statement is always executed*/
  END IF;
*/

Whitespace between lexical elements

You can add whitespace between lexical elements to make the source text easier to read.

Here's 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;
/