Lexical units

A lexical unit of PL is its smallest standalone component. Lexical units include delimiters, identifiers, literals, pragmas, and comments.

Delimiters

A delimiter is a character or a combination of characters that has a special meaning in PL. Do not embed any other characters, including a space character, into a delimiter.

The following table lists the delimiters supported in PL.

Identifiers

You can use identifiers to name PL components such as constants, cursors, exceptions, keywords, labels, packages, reserved words, subprograms, types, and variables.

Each character in an identifier has a meaning, regardless whether it is a letter or not. For example, identifiers firstname and first_name are different.

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

PL identifiers are case-insensitive unless they are enclosed by double quotation marks. For example, identifiers firstname, FirstName, and FIRSTNAME are the same.

Reserved keywords

A reserved PL keyword is an identifier that has a special meaning.

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

Predefined identifiers

Predefined identifiers are declared in the predefined STANDARD package.

For more information, see Definition of the PL environment by the STANDARD package.

You can use a predefined identifier as a user-defined identifier. This is not recommended because your local declaration overrides the global declaration.

User-defined identifiers

A user-defined identifier is an ordinary or quoted identifier that consists of characters from the database character set.

Ordinary user-defined identifiers

You can define your PL identifiers with the following limitations:

• The identifier must start with a letter.

• It can contain letters, digits, and forward slashes (/), asterisks (*), ampersands (&), and dollar signs ($).

• It can contain a maximum of 128 characters.

• You cannot use a reserved PL word as a PL identifier.

• PL identifiers are case-insensitive.

The following user-defined identifiers are valid:

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

Quoted identifiers

A quoted user-defined identifier is enclosed in double quotation marks.

Any character from the database character set is allowed between the double quotation marks, except double quotation marks, line breaks, and null characters.

The following quoted identifiers are valid:

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

A quoted user-defined identifier is case-sensitive. However, if a quoted user-defined 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 they are omitted, the identifier is case-insensitive.

You can use a reserved keyword as a quoted user-defined identifier. However, this is not recommended. This is because that reserved keywords are invalid ordinary user-defined identifiers and case-sensitive and must be placed in double quotation marks.

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, and if you do, it is case-insensitive.
    DBMS_Output.Put_Line("ALL");      -- The double quotation marks must be used.
end;
/

The output is as follows:

hello
all

Literals

The value of a literal is neither represented by an identifier nor computed from other values.

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

PL literals include all SQL literals and BOOLEAN literals, which SQL does not have. A BOOLEAN literal is a predefined logical value such as TRUE, FALSE, or NULL. NULL indicates that the value is unknown.

When you use PL character literals, note that:

• They are case-sensitive. For example, "A" and "a" are different.

• A space character is meaningful. For example, 'abc' and 'a b c' are different.

• PL does not support using a backlash to indicate line continuation. A line break is required if you need to continue a string on the next line. You can also use a concatenation operator (||) to avoid a line break.

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" do not equal to the integer literals 0 to 9. You can use them in an arithmetic expression because PL converts them to integers.

• A character literal containing zero characters has the value NULL, and is called a null string. However, this NULL value is different from the BOOLEAN value NULL.

• An ordinary character literal consists of characters from the database character set.

• A national character literal consists of characters from the national character set.

Pragmas

A pragma is a directive that the compiler processes in a compilation task.

It starts with the reserve word PRAGMA, which is followed by the name of the pragma. A pragma may appear in or before a statement. Some specific pragmas also have additional limitations. Some pragmas contain parameters. A pragma becomes invalid when the compiler cannot recognize its names or parameters.

PL supports the following pragma types:

• AUTONOMOUS_TRANSACTION

• COVERAGE

• DEPRECATE

• EXCEPTION_INIT

• INLINE

• RESTRICT_REFERENCES

• SERIALLY_REUSABLE

• UDF

Comments

The purpose of a comment is to help other programmers understand your source code. The PL compiler ignores comments.

Generally, you can add a comment to a code segment to describe its purpose. You can also disable abolished or incomplete code segments by changing them into comments.

Single-line comments

A single-line comment starts with -- and extends to the end of the line.

When you test or debug a program, you can disable a line of code by adding a comment to it. Here is an example:

-- INSERT INTO t1 VALUES(1, 1)

Multi-line comments

A multi-line comment starts with /*, ends with */, and can span several lines.

You can use multi-line comment delimiters to "comment out" code segments. Avoid nested multi-line comments. This is because 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 must be always executed. */
  END IF;
*/

Space characters between lexical units

You can add space characters between lexical units to make the source code easier to read.

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;  -- The syntax is correct but it is not easy to read.

  -- The following statement is easier to read:

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