Lexical elements

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

Delimiters

Delimiters are characters or combinations of characters that have special meanings in PL/SQL. Do not embed any other characters (including space characters) in delimiters.

The following table lists the delimiters 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 a specific meaning. For example, identifiers firstname and first_name are different.

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

Unless quoted with double quotation marks, PL/SQL identifiers are not case-sensitive. For example, 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 the reserved keywords in PL/SQL, see PL/SQL reserved keywords.

Predefined identifiers

Predefined identifiers are declared by using the predefined package STANDARD.

For more information about the 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 special characters: slash (/), asterisk (*), ampersand (&), and dollar sign ($).

• The identifier cannot exceed 128 characters in length.

• You cannot use a PL/SQL reserved keyword as a PL/SQL identifier.

• 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 marks, newline characters, and null characters.

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

You can use a reserved keyword as a quoted identifier. We do not recommend that you do this. Because a reserved keyword is not a valid ordinary user-defined identifier, you must always enclose it in double quotation marks, and it is 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. If you omit them, the identifier is 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 not represented by an identifier or calculated from other values.

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

PL/SQL text includes all SQL text and BOOLEAN text (which SQL does not support). BOOLEAN text is a predefined logical value of 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 are significant. For example, 'abc' and 'a b c' are different.

• PL/SQL does not support a line continuation character that indicates that "this string continues on the next source line". If a string continues on the next source line, the string must be separated by a newline character. You can also use the concatenation operator (||) to avoid a newline.

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.

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

• The value of a character text that contains zero characters is 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.

Compiler directives

Compiler directives are instructions processed by the compiler during compilation.

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

The following table lists the types of compiler 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 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;
  /* Required
THEN
 statement is always executed*/
  END IF;
*/

Whitespace between tokens

You can add whitespace between tokens 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;
/