2 PL/SQL Language Fundamentals

Database Character Set

PL/SQL uses the database character set for identifiers (which are described in "Identifiers") and to store data.

The database character set consists of these characters:

• Letters A through Z and a through z

  Corresponding uppercase and lowercase letters are equivalent—for example, A is equivalent to a—except in character literals and string literals.

• Digits 0 through 9

• Symbols in Table 2-1

• Whitespace characters tab, space, and carriage return

To specify the database character set, use the CHARACTER SET clause of the CREATE DATABASE statement. For information about this statement, see Oracle Database SQL Language Reference.

National Character Set

PL/SQL uses the national character set to represent languages that have thousands of characters, each of which requires two or three bytes (Japanese, for example). The national character set represents data as Unicode, using either the AL16UTF16 or UTF8 encoding.

Table 2-2 compares AL16UTF16 and UTF8 encodings. For maximum reliability, Oracle recommends using the default AL16UTF16 encoding wherever practical.

To determine how many bytes a Unicode string needs, use the SQL function LENGTHB. For information about this statement, see Oracle Database SQL Language Reference.

To specify the national character set, use the NATIONAL CHARACTER SET clause of the CREATE DATABASE statement. For information about this statement, see Oracle Database SQL Language Reference.

Delimiters

A delimiter is a character, or character combination, that has a special meaning in PL/SQL. Table 2-3 summarizes the PL/SQL delimiters.

Identifiers

Identifiers name PL/SQL objects, which include:

• Constants

• Cursors

• Exceptions

• Keywords

• Labels

• Packages

• Reserved words

• Subprograms

• Variables

• Types

You must separate adjacent identifiers by one or more spaces or a punctuation character.

Example 2-3 shows what happens when the identifiers END and IF are not separated.

BEGIN
  IF x > y THEN high := x; END IF;  -- correct
  IF x > y THEN high := x; ENDIF;   -- incorrect
END;
/
 

END;
   *
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of these:
if

PL/SQL is case-insensitive for identifiers. For example, these identifiers are the same:

lastname
LastName
LASTNAME

Every character in an identifier, alphabetic or not, is significant. For example, these identifiers are different:

lastname
last_name

Topics:

• Reserved Words and Keywords

• Predefined Identifiers

• User-Defined Identifiers

• Quoted Identifiers

SELECT TYPE_NAME, PREDEFINED FROM ALL_TYPES;

X
t2
phone#
credit_limit
LastName
oracle$number
money$$$tree
SN##
try_again_

mine&yours
debit-amount
on/off
user id

"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"

Literals

A literal is a value that is not represented by an identifier or an expression.

Topics:

• Numeric Literals

• Character Literals

• String Literals

• BOOLEAN Literals

• Datetime and Interval Literals

For syntax diagrams of literals, see "Literal".

030
6
-14
+32767

6.6667
0.0
-12.0
3.14159
+8300.00
.5
25.

2.0f
2D

2E5
1.0E-7
3.14159e0
-1E38
-9.5e-3

5E3 = 5 * 10**3 = 5 * 1000 = 5000

'Z'
'%'
'7'
' '
'z'
'('

'Hello, world!'
'XYZ Corporation'
'10-NOV-91'
'He said, "Life is like licking honey from a thorn."'
'$1,000,000'

'baker'
'Baker'

'abc'
' abc'
'abc '
' abc '

DECLARE
  d1 DATE      := DATE '1998-12-25';
  t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
  
  t2 TIMESTAMP WITH TIME ZONE :=
  TIMESTAMP '1997-01-31 09:26:56.66 +02:00';
  
  -- Three years and two months
  -- For greater precision, use the day-to-second interval
 
  i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;
 
  -- Five days, four hours, three minutes, two and 1/100 seconds
 
  i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;
 
BEGIN
  NULL;
END;
/

Comments

The PL/SQL compiler ignores comments. Adding comments to your program can make it easier to read and understand. Typically, you use comments to describe the purpose and use of each code segment. You can also disable obsolete or unfinished pieces of code by turning them into comments.

Topics:

• Single-Line Comments

• Multiline Comments

DECLARE
  howmany     NUMBER;
  num_tables  NUMBER;
BEGIN
  -- Begin processing
  SELECT COUNT(*) INTO howmany
  FROM USER_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
  num_tables := howmany;       -- Compute another value
END;
/

-- DELETE FROM employees WHERE comm_pct IS NULL

DECLARE
  some_condition  BOOLEAN;
  pi              NUMBER := 3.1415926;
  radius          NUMBER := 15;
  area            NUMBER;
BEGIN
  /* Perform some simple tests and assignments */
 
  IF 2 + 2 = 4 THEN
    some_condition := TRUE;
  /* We expect this THEN to always be performed */
  END IF;
 
  /* This line computes the area of a circle using pi,
  which is the ratio between the circumference and diameter.
  After the area is computed, the result is displayed. */
 
  area := pi * radius**2;
  DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
END;
/
 

The area is: 706.858335

/*
  IF 2 + 2 = 4 THEN
    some_condition := TRUE;
  /* We expect this THEN to always be performed */
  END IF;
*/

/*
  IF 2 + 2 = 4 THEN
    some_condition := TRUE;
  -- We expect this THEN to always be performed
  END IF;
*/

Variable Declarations

A variable declaration always specifies the name and data type of the variable. It can also specify an initial value and the NOT NULL constraint.

The name must be a valid user-defined identifier (see "User-Defined Identifiers").

The data type can be a SQL data type (such as CHAR, DATE, or NUMBER) or a PL/SQL-only data type (such as BOOLEAN or PLS_INTEGER). For information about data types, see Chapter 3, "PL/SQL Data Types".

Example 2-7 declares several variables.

DECLARE
  part_number       NUMBER(6);     -- SQL data type
  part_name         VARCHAR2(20);  -- SQL data type
  in_stock          BOOLEAN;       -- PL/SQL-only data type
  part_price        NUMBER(6,2);   -- SQL data type
  part_description  VARCHAR2(50);  -- SQL data type
BEGIN
  NULL;
END;
/

For variable declaration syntax, see "Variable".

Constant Declarations

The information in "Variable Declarations" also applies to constant declarations, but a constant declaration has two more requirements: the keyword CONSTANT and the initial value of the constant. (The initial value of a constant is its permanent value.)

You can define constants of complex types that have no literal values or predefined constructors, by invoking a function that returns a filled-in value. For example, you can make a constant associative array this way.

Example 2-8 declares three constants.

DECLARE
  credit_limit     CONSTANT REAL    := 5000.00;  -- SQL data type
  max_days_in_year CONSTANT INTEGER := 366;      -- SQL data type
  urban_legend     CONSTANT BOOLEAN := FALSE     -- PL/SQL-only data type;
BEGIN
  NULL;
END;
/

For constant declaration syntax, see "Constant".

Initial Values of Variables and Constants

In a variable declaration, the initial value is optional (the default is NULL). In a constant declaration, the initial value is required (and the constant can never have a different value).

The initial value is assigned to the variable or constant every time control passes to the block or subprogram that contains the declaration. If the declaration is in a package specification, the initial value is assigned to the variable or constant once for each session (whether the variable or constant is public or private).

To specify the initial value, use either the assignment operator (:=) or the keyword DEFAULT, followed by an expression. The expression can include previously declared constants and previously initialized variables.

Example 2-9 assigns initial values to the constant and variables that it declares. The initial value of area depends on the previously declared constant pi and the previously initialized variable radius.

DECLARE
  hours_worked    INTEGER := 40;
  employee_count  INTEGER := 0;

  pi     CONSTANT REAL := 3.14159;
  radius          REAL := 1;
  area            REAL := (pi * radius**2);
BEGIN
  NULL;
END;
/

If you do not specify an initial value for a variable, assign a value to it before using it in any other context.

In Example 2-10, the variable counter has the initial value NULL, by default. As the example shows (using the "IS [NOT] NULL Operator") NULL is different from zero.

DECLARE
  counter INTEGER;  -- initial value is NULL by default
BEGIN
  counter := counter + 1;  -- NULL + 1 is still NULL
  
  IF counter IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('counter is NULL.');
  END IF;
END;
/
 

counter is NULL.

NOT NULL Constraint

A variable or constant declaration can specify the NOT NULL constraint, which prevents anyone from assigning a null value to the variable or constant.

A variable declaration that specifies NOT NULL must assign an initial value to the variable (because the default initial value for a variable is NULL).

PL/SQL subtypes NATURALN, POSITIVEN, and SIMPLE_INTEGER are predefined as NOT NULL. When declaring a variable of one of these subtypes, you can omit NOT NULL, and you must specify an initial value.

In Example 2-11, all four variables have the NOT NULL constraint.

DECLARE
  acct_id INTEGER(4) NOT NULL := 9999;
  a NATURALN                  := 9999;
  b POSITIVEN                 := 9999;
  c SIMPLE_INTEGER            := 9999;
BEGIN
  NULL;
END;
/

PL/SQL treats any zero-length string as a NULL value. This includes values returned by character functions and BOOLEAN expressions.

In Example 2-12, all of the variables are initialized to NULL.

DECLARE
  null_string  VARCHAR2(80) := TO_CHAR('');
  address      VARCHAR2(80);
  zip_code     VARCHAR2(80) := SUBSTR(address, 25, 0);
  name         VARCHAR2(80);
  valid        BOOLEAN      := (name != '');
BEGIN
  NULL;
END;
/

To test for a NULL value, use the "IS [NOT] NULL Operator".

%TYPE Attribute

The %TYPE attribute lets you declare a data item to be of the same data type as a previously declared data item or database column. If the declaration of the referenced item changes, the declaration of the referencing item changes accordingly.

The syntax of the declaration is:

referencing_item referenced_item%TYPE;

For the kinds of data items that can be referencing and referenced items, see "%TYPE Attribute".

The referencing item inherits the following from the referenced item:

• Data type

• Constraints (unless the referenced item is a database column)

The referencing item does not inherit the initial value of the referenced item. Therefore, if the referencing item specifies or inherits the NOT NULL constraint, you must specify an initial value for it.

The %TYPE attribute is particularly useful when declaring variables to hold database values. The syntax for declaring a variable of the same type as a database column is:

variable_name table_name.column_name%TYPE;

In Example 2-13, the variable surname inherits the data type of the database column employees.last_name, which has a NOT NULL constraint. Because surname does not inherit the NOT NULL constraint, its declaration does not need an initial value.

DECLARE
  surname  employees.last_name%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/

surname=

In Example 2-14, the variable surname inherits the data type and the NOT NULL constraint of the variable name. Because surname does not inherit the initial value of name, its declaration needs an initial value.

DECLARE
  name     VARCHAR(25) NOT NULL := 'Smith';
  surname  name%TYPE := 'Jones';
BEGIN
  DBMS_OUTPUT.PUT_LINE('name=' || name);
  DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/

name=Smith
surname=Jones

%ROWTYPE Attribute

The %ROWTYPE attribute lets you declare a record that represents a row of a database table or view: For every column of the row, the record has a field with the same name and data type. The record can store an entire row that is either selected from the table or view or fetched from a cursor.

The record fields do not inherit the constraints or initial values of the corresponding columns.

If the structure of the row changes, the structure of the record changes accordingly.

The syntax for declaring a record variable to hold a table row is:

variable_name table_name%ROWTYPE;

For more information about the syntax of %ROWTYPE, see "%ROWTYPE Attribute".

Example 2-15 creates a table, employees_temp, and then creates a record, emprec, that has a field for every column in the table. Each field (referenced by emprec.field_name) inherits the name and data type of the corresponding column, but not the constraints or initial value.

CREATE TABLE employees_temp (
  empid    NUMBER(6) NOT NULL PRIMARY KEY
  deptid   NUMBER(6) CONSTRAINT c_employees_temp_deptid
                     CHECK (deptid BETWEEN 100 AND 200),
  deptname VARCHAR2(30) := 'Sales'
);
 
DECLARE
  emprec  employees_temp%ROWTYPE;
BEGIN
  emprec.empid := NULL;  -- Null constraint not inherited
  emprec.deptid := 50;   -- Check constraint not inherited
  DBMS_OUTPUT.PUT_LINE ('emprec.deptname: ' || emprec.deptname);
                           -- Initial value not inherited
END;
/
 

emprec.deptname:

Example 2-16 declares a cursor, c1, which returns two columns of the departments table, and then creates a record, dept_rec, that has a field for each returned column.

DECLARE
  CURSOR c1 IS
    SELECT department_id, department_name
    FROM departments;
  
  dept_rec c1%ROWTYPE;  -- Has 2 fields, department_id and department_name
  
BEGIN
  NULL;
END;
/

In Example 2-16, the record join_rec has four fields: employee_id, email, and manager_id (which correspond to columns in the employees table) and location_id (which correspond to a column in the departments table).

DECLARE
  CURSOR c2 IS
    SELECT employee_id, email, employees.manager_id, location_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
  
  join_rec  c2%ROWTYPE;  -- includes columns from two tables
  
BEGIN
  NULL;
END;
/

Assigning Values to Variables with the Assignment Statement

To assign the value of an expression to a variable, use this form of the assignment statement:

variable_name := expression;

For the complete syntax of the assignment statement, see "Assignment Statement". For the syntax of an expression, see "Expression".

Example 2-25 declares several variables (specifying initial values for some) and then uses assignment statements to assign the values of expressions to them.

DECLARE  -- You can assign initial values here
  wages          NUMBER;
  hours_worked   NUMBER := 40;
  hourly_salary  NUMBER := 22.50;
  bonus          NUMBER := 150;
  country        VARCHAR2(128);
  counter        NUMBER := 0;
  done           BOOLEAN;
  valid_id       BOOLEAN;
  emp_rec1       employees%ROWTYPE;
  emp_rec2       employees%ROWTYPE;
  TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  comm_tab       commissions;
 
BEGIN  -- You can assign values here too
  wages := (hours_worked * hourly_salary) + bonus;
  country := 'France';
  country := UPPER('Canada');
  done := (counter > 100);
  valid_id := TRUE;
  emp_rec1.first_name := 'Antonio';
  emp_rec1.last_name := 'Ortiz';
  emp_rec1 := emp_rec2;
  comm_tab(5) := 20000 * 0.15;
END;
/

Assigning Values to Variables with the SELECT INTO Statement

A simple form of the SELECT INTO statement is:

SELECT select_item [, select_item ]... 
INTO variable_name [, variable_name ]...
FROM table_name;

For each select_item, there must be a corresponding, type-compatible variable_name. Because SQL does not have a BOOLEAN type, variable_name cannot be a BOOLEAN variable. For the complete syntax of the SELECT INTO statement, see "SELECT INTO Statement".

Example 2-26 uses a SELECT INTO statement to assign to the variable bonus the value that is 10% of the salary of the employee whose employee_id is 100.

DECLARE
  bonus   NUMBER(8,2);
BEGIN
  SELECT salary * 0.10 INTO bonus
  FROM employees
  WHERE employee_id = 100;
END;

DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
/

bonus = 2458.2

Assigning Values to Variables as Parameters of a Subprogram

If you pass a variable to a subprogram as an OUT or IN OUT parameter, and the subprogram assigns a value to the parameter, the variable retains that value after the subprogram finishes running. For more information, see "OUT Mode" and "IN OUT Mode".

Example 2-27 passes the variable new_sal to the procedure adjust_salary. The procedure assigns a value to the corresponding formal parameter, sal. Because sal is an IN OUT parameter, the variable new_sal retains the assigned value after the procedure finishes running.

DECLARE
  new_sal  NUMBER(8,2);
  emp_id   NUMBER(6) := 126;
  
  PROCEDURE adjust_salary (
    emp_id      NUMBER,
    sal IN OUT NUMBER
  )
  IS
    emp_job  VARCHAR2(10);
    avg_sal  NUMBER(8,2);
  BEGIN
    SELECT job_id INTO emp_job
    FROM employees
    WHERE employee_id = emp_id;
  
    SELECT AVG(salary) INTO avg_sal
    FROM employees
    WHERE job_id = emp_job;
  
    DBMS_OUTPUT.PUT_LINE ('The average salary for '
                          || emp_job
                          || ' employees: '
                          || TO_CHAR(avg_sal)
    );
 
    sal := (sal + avg_sal)/2;
  END;
 
BEGIN
  -- Assign initial value to new_sal:
 
  SELECT AVG(salary)
  INTO new_sal
  FROM employees;
 
  DBMS_OUTPUT.PUT_LINE ('initial value of new_sal: ' ||
                        TO_CHAR(new_sal)
                       );
 
  -- Pass new_sal to procedure:
 
  adjust_salary(emp_id, new_sal);
 
  DBMS_OUTPUT.PUT_LINE ('final value of new_sal: ' ||
                        TO_CHAR(new_sal)
                       );
END;
/
 

initial value of new_sal: 6461.83
The average salary for ST_CLERK employees: 3281.01
final value of new_sal: 5056.07

Assigning Values to BOOLEAN Variables

The only values that you can assign to a BOOLEAN variable are TRUE, FALSE, and NULL.

Example 2-28 initializes the BOOLEAN variable done to NULL by default, assigns it the literal value FALSE, compares it to the literal value TRUE, and assigns it the value of a BOOLEAN expression.

DECLARE
  done    BOOLEAN;              -- Initial value is NULL by default
  counter NUMBER := 0;
BEGIN
  done := FALSE;                -- Assign literal value
  WHILE done != TRUE            -- Compare to literal value
    LOOP
      counter := counter + 1;
      done := (counter > 500);  -- Assign value of BOOLEAN expression
    END LOOP;
END;
/

For more information about the BOOLEAN data type, see "Predefined PL/SQL BOOLEAN Data Type".

Concatenation Operator

The concatenation operator (||) appends one string operand to another, as Example 2-29 shows.

DECLARE
  x VARCHAR2(4) := 'suit';
  y VARCHAR2(4) := 'case';
BEGIN
  DBMS_OUTPUT.PUT_LINE (x || y);
END;
/
 

suitcase

The concatenation operator ignores null operands, as Example 2-30 shows.

BEGIN
  DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce');
END;
/
 

applesauce

For more information about the syntax of the concatenation operator, see character_expression ::=.

Operator Precedence

An operation is either a unary operator and its single operand or a binary operator and its two operands. The operations in an expression are evaluated in order of operator precedence.

Table 2-4 shows operator precedence from highest to lowest. Operators with equal precedence are evaluated in no particular order.

To control the order of evaluation, enclose operations in parentheses, as in Example 2-31.

DECLARE
  a INTEGER := 1+2**2;
  b INTEGER := (1+2)**2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
  DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b));
END;
/

a = 5
b = 9

When parentheses are nested, the most deeply nested operations are evaluated first.

In Example 2-32, the operations (1+2) and (3+4) are evaluated first, producing the values 3 and 7, respectively. Next, the operation 3*7 is evaluated, producing the result 21. Finally, the operation 21/7 is evaluated, producing the final value 3.

DECLARE
  a INTEGER := ((1+2)*(3+4))/7;
BEGIN
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;
/

a = 3

You can also use parentheses to improve readability, as in Example 2-33, where the parentheses do not affect evaluation order.

DECLARE
  a INTEGER := 2**2*3**2;
  b INTEGER := (2**2)*(3**2);
BEGIN
  DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
  DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b));
END;
/

a = 36
b = 36

Example 2-34 shows the effect of operator precedence and parentheses in several more complex expressions.

DECLARE
  salary      NUMBER := 60000;
  commission  NUMBER := 0.10;
BEGIN
  -- Division has higher precedence than addition:
  
  DBMS_OUTPUT.PUT_LINE('5 + 12 / 4 = ' || TO_CHAR(5 + 12 / 4));
  DBMS_OUTPUT.PUT_LINE('12 / 4 + 5 = ' || TO_CHAR(12 / 4 + 5));
  
 -- Parentheses override default operator precedence:
 
  DBMS_OUTPUT.PUT_LINE('8 + 6 / 2 = ' || TO_CHAR(8 + 6 / 2));
  DBMS_OUTPUT.PUT_LINE('(8 + 6) / 2 = ' || TO_CHAR((8 + 6) / 2));
 
  -- Most deeply nested operation is evaluated first:
 
  DBMS_OUTPUT.PUT_LINE('100 + (20 / 5 + (7 - 3)) = '
                      || TO_CHAR(100 + (20 / 5 + (7 - 3))));
 
  -- Parentheses, even when unnecessary, improve readability:
 
  DBMS_OUTPUT.PUT_LINE('(salary * 0.05) + (commission * 0.25) = '
    || TO_CHAR((salary * 0.05) + (commission * 0.25))
  );
 
  DBMS_OUTPUT.PUT_LINE('salary * 0.05 + commission * 0.25 = '
    || TO_CHAR(salary * 0.05 + commission * 0.25)
  );
END;
/
 

5 + 12 / 4 = 8
12 / 4 + 5 = 8
8 + 6 / 2 = 11
(8 + 6) / 2 = 7
100 + (20 / 5 + (7 - 3)) = 108
(salary * 0.05) + (commission * 0.25) = 3000.025
salary * 0.05 + commission * 0.25 = 3000.025

Logical Operators

The logical operators AND, OR, and NOT follow the tri-state logic shown in Table 2-5. AND and OR are binary operators; NOT is a unary operator.

As Table 2-5 and Example 2-35 show, AND returns TRUE if and only if both operands are TRUE. (Several examples invoke the print_boolean procedure that Example 2-35 creates. The procedure uses the "IS [NOT] NULL Operator".)

CREATE OR REPLACE PROCEDURE print_boolean (
  name   VARCHAR2,
  value  BOOLEAN
) IS
BEGIN
  IF value IS NULL THEN
    DBMS_OUTPUT.PUT_LINE (name || ' = NULL');
  ELSIF value = TRUE THEN
    DBMS_OUTPUT.PUT_LINE (name || ' = TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE (name || ' = FALSE');
  END IF;
END;
/
 
DECLARE
  PROCEDURE print_x_and_y (
    x  BOOLEAN,
    y  BOOLEAN
  ) IS
  BEGIN
   print_boolean ('x', x);
   print_boolean ('y', y);
   print_boolean ('x AND y', x AND y);
 END print_x_and_y;
 
BEGIN
 print_x_and_y (FALSE, FALSE);
 print_x_and_y (TRUE, FALSE);
 print_x_and_y (FALSE, TRUE);
 print_x_and_y (TRUE, TRUE);
 
 print_x_and_y (TRUE, NULL);
 print_x_and_y (FALSE, NULL);
 print_x_and_y (NULL, TRUE);
 print_x_and_y (NULL, FALSE);
END;
/
 

x = FALSE
y = FALSE
x AND y = FALSE
x = TRUE
y = FALSE
x AND y = FALSE
x = FALSE
y = TRUE
x AND y = FALSE
x = TRUE
y = TRUE
x AND y = TRUE
x = TRUE
y = NULL
x AND y = NULL
x = FALSE
y = NULL
x AND y = FALSE
x = NULL
y = TRUE
x AND y = NULL
x = NULL
y = FALSE
x AND y = FALSE

As Table 2-5 and Example 2-36 show, OR returns TRUE if either operand is TRUE. (Example 2-36 invokes the print_boolean procedure from Example 2-35.)

DECLARE
  PROCEDURE print_x_or_y (
    x  BOOLEAN,
    y  BOOLEAN
  ) IS
  BEGIN
    print_boolean ('x', x);
    print_boolean ('y', y);
    print_boolean ('x OR y', x OR y);
  END print_x_or_y;
 
BEGIN
  print_x_or_y (FALSE, FALSE);
  print_x_or_y (TRUE, FALSE);
  print_x_or_y (FALSE, TRUE);
  print_x_or_y (TRUE, TRUE);
 
  print_x_or_y (TRUE, NULL);
  print_x_or_y (FALSE, NULL);
  print_x_or_y (NULL, TRUE);
  print_x_or_y (NULL, FALSE);
END;
/
 

x = FALSE
y = FALSE
x OR y = FALSE
x = TRUE
y = FALSE
x OR y = TRUE
x = FALSE
y = TRUE
x OR y = TRUE
x = TRUE
y = TRUE
x OR y = TRUE
x = TRUE
y = NULL
x OR y = TRUE
x = FALSE
y = NULL
x OR y = NULL
x = NULL
y = TRUE
x OR y = TRUE
x = NULL
y = FALSE
x OR y = NULL

As Table 2-5 and Example 2-37 show, NOT returns the opposite of its operand, unless the operand is NULL. NOT NULL returns NULL, because NULL is an indeterminate value. (Example 2-37 invokes the print_boolean procedure from Example 2-35.)

DECLARE
  PROCEDURE print_not_x (
    x  BOOLEAN
  ) IS
  BEGIN
    print_boolean ('x', x);
    print_boolean ('NOT x', NOT x);
  END print_not_x;
 
BEGIN
  print_not_x (TRUE);
  print_not_x (FALSE);
  print_not_x (NULL);
END;
/
 

x = TRUE
NOT x = FALSE
x = FALSE
NOT x = TRUE
x = NULL
NOT x = NULL

In Example 2-38, you might expect the sequence of statements to run because x and y seem unequal. But, NULL values are indeterminate. Whether x equals y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
  x NUMBER := 5;
  y NUMBER := NULL;
BEGIN
  IF x != y THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('x != y');  -- not run
  ELSIF x = y THEN -- also yields NULL
    DBMS_OUTPUT.PUT_LINE('x = y');
  ELSE
    DBMS_OUTPUT.PUT_LINE
      ('Can''t tell if x and y are equal or not.');
  END IF;
END;
/
 

Can't tell if x and y are equal or not.

In Example 2-39, you might expect the sequence of statements to run because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
  a NUMBER := NULL;
  b NUMBER := NULL;
BEGIN
  IF a = b THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('a = b');  -- not run
  ELSIF a != b THEN  -- yields NULL, not TRUE
    DBMS_OUTPUT.PUT_LINE('a != b');  -- not run
  ELSE
    DBMS_OUTPUT.PUT_LINE('Can''t tell if two NULLs are equal');
  END IF;
END;
/
 

Can't tell if two NULLs are equal

In Example 2-40, the two IF statements appear to be equivalent. However, if either x or y is NULL, then the first IF statement assigns the value of y to high and the second IF statement assigns the value of x to high.

DECLARE
  x    INTEGER := 2;
  Y    INTEGER := 5;
  high INTEGER;
BEGIN
  IF (x > y)       -- If x or y is NULL, then (x > y) is NULL
    THEN high := x;  -- run if (x > y) is TRUE
    ELSE high := y;  -- run if (x > y) is FALSE or NULL
  END IF;
  
  IF NOT (x > y)   -- If x or y is NULL, then NOT (x > y) is NULL
    THEN high := y;  -- run if NOT (x > y) is TRUE
    ELSE high := x;  -- run if NOT (x > y) is FALSE or NULL
  END IF;
END;
/

Example 2-41 invokes the print_boolean procedure from Example 2-35 three times. The third and first invocation are logically equivalent—the parentheses in the third invocation only improve readability. The parentheses in the second invocation change the order of operation.

DECLARE
  x  BOOLEAN := FALSE;
  y  BOOLEAN := FALSE;
  
BEGIN
  print_boolean ('NOT x AND y', NOT x AND y);
  print_boolean ('NOT (x AND y)', NOT (x AND y));
  print_boolean ('(NOT x) AND y', (NOT x) AND y);
END;
/
 

NOT x AND y = FALSE
NOT (x AND y) = TRUE
(NOT x) AND y = FALSE

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as it can determine the result. Therefore, you can write expressions that might otherwise cause errors.

In Example 2-42, short-circuit evaluation prevents the OR expression from causing a divide-by-zero error. When the value of on_hand is zero, the value of the left operand is TRUE, so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.

DECLARE
  on_hand  INTEGER := 0;
  on_order INTEGER := 100;
BEGIN
  -- Does not cause divide-by-zero error;
  -- evaluation stops after first expression
  
  IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
    DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');
  END IF;
END;
/
 

On hand quantity is zero.

Comparison Operators

Comparison operators compare one expression to another. The result is always either TRUE, FALSE, or NULL. If the value of one expression is NULL, then the result of the comparison is also NULL.

The comparison operators are:

• Relational Operators

• IS [NOT] NULL Operator

• LIKE Operator

• BETWEEN Operator

• IN Operator

BEGIN
  print_boolean ('(2 + 2 =  4)', 2 + 2 = 4);
  
  print_boolean ('(2 + 2 <> 4)', 2 + 2 <> 4);
  print_boolean ('(2 + 2 != 4)', 2 + 2 != 4);
  print_boolean ('(2 + 2 ~= 4)', 2 + 2 ~= 4);
  print_boolean ('(2 + 2 ^= 4)', 2 + 2 ^= 4);
  
  print_boolean ('(1 < 2)', 1 < 2);
 
  print_boolean ('(1 > 2)', 1 > 2);
 
  print_boolean ('(1 <= 2)', 1 <= 2);
 
  print_boolean ('(1 >= 1)', 1 >= 1);
END;
/
 

(2 + 2 =  4) = TRUE
(2 + 2 <> 4) = FALSE
(2 + 2 != 4) = FALSE
(2 + 2 ~= 4) = FALSE
(2 + 2 ^= 4) = FALSE
(1 < 2) = TRUE
(1 > 2) = FALSE
(1 <= 2) = TRUE
(1 >= 1) = TRUE

'y' > 'r'

'Kathy' > 'Kathryn'

'a' = 'A'
'Alpha' = 'ALPHA'

'Cooperate' = 'Co-Operate'
'Co-Operate' = 'coöperate'

'01-JAN-91' > '31-DEC-90'

DECLARE
  PROCEDURE compare (
    value   VARCHAR2,
    pattern VARCHAR2
  ) IS
  BEGIN
    IF value LIKE pattern THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  compare('Johnson', 'J%s_n');
  compare('Johnson', 'J%S_N');
END;
/
 

TRUE
FALSE

DECLARE
  PROCEDURE half_off (sale_sign VARCHAR2) IS
  BEGIN
    IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  half_off('Going out of business!');
  half_off('50% off!');
END;
/
 

FALSE
TRUE

BEGIN
  print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);
  print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);
  print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);
  print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);
END;
/
 

2 BETWEEN 1 AND 3 = TRUE
2 BETWEEN 2 AND 3 = TRUE
2 BETWEEN 1 AND 2 = TRUE
2 BETWEEN 3 AND 4 = FALSE

DECLARE
  letter VARCHAR2(1) := 'm';
BEGIN
  print_boolean (
    'letter IN (''a'', ''b'', ''c'')',
    letter IN ('a', 'b', 'c')
  );
  print_boolean (
    'letter IN (''z'', ''m'', ''y'', ''p'')',
    letter IN ('z', 'm', 'y', 'p')
  );
END;
/
 

letter IN ('a', 'b', 'c') = FALSE
letter IN ('z', 'm', 'y', 'p') = TRUE
 

DECLARE
  a INTEGER; -- Initialized to NULL by default
  b INTEGER := 10;
  c INTEGER := 100;
BEGIN
  print_boolean ('100 IN (a, b, c)', 100 IN (a, b, c));
  print_boolean ('100 NOT IN (a, b, c)', 100 NOT IN (a, b, c));
  
  print_boolean ('100 IN (a, b)', 100 IN (a, b));
  print_boolean ('100 NOT IN (a, b)', 100 NOT IN (a, b));
 
  print_boolean ('a IN (a, b)', a IN (a, b));
  print_boolean ('a NOT IN (a, b)', a NOT IN (a, b));
END;
/
 

100 IN (a, b, c) = TRUE
100 NOT IN (a, b, c) = FALSE
100 IN (a, b) = NULL
100 NOT IN (a, b) = NULL
a IN (a, b) = NULL
a NOT IN (a, b) = NULL

BOOLEAN Expressions

A BOOLEAN expression is an expression that returns a BOOLEAN value—TRUE, FALSE, or NULL. The simplest BOOLEAN expression is a single BOOLEAN value, constant, or variable. The following are also BOOLEAN expressions:

NOT boolean_expression
boolean_expression relational_operator boolean_expression
boolean_expression { AND | OR } boolean_expression

For a list of relational operators, see Table 2-6. For the complete syntax of a BOOLEAN expression, see boolean_expression ::=.

Typically, you use BOOLEAN expressions as conditions in control statements (which are described in Chapter 4, "PL/SQL Control Statements") and in WHERE clauses of DML statements.

You can use a BOOLEAN variable itself as a condition; you need not compare it to the value TRUE or FALSE. In Example 2-49, the conditions in the loops are equivalent.

DECLARE
  done BOOLEAN;
BEGIN
  -- These WHILE loops are equivalent
  
  done := FALSE;
  WHILE done = FALSE
    LOOP
      done := TRUE;
    END LOOP;
 
  done := FALSE;
  WHILE NOT (done = TRUE)
    LOOP
      done := TRUE;
    END LOOP;
 
  done := FALSE;
  WHILE NOT done
    LOOP
      done := TRUE;
    END LOOP;
END;
/

CASE Expressions

Topics:

• Simple CASE Expression

• Searched CASE Expression

CASE selector
WHEN selector_value_1 THEN result_1
WHEN selector_value_2 THEN result_2
...
WHEN selector_value_n THEN result_n
[ ELSE
  else_result ]
END]

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Fair'
      WHEN 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;
    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
 

Grade B is Very Good

DECLARE
  grade CHAR(1); -- NULL by default
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
  CASE grade
    WHEN NULL THEN 'No grade assigned'
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Very Good'
    WHEN 'C' THEN 'Good'
    WHEN 'D' THEN 'Fair'
    WHEN 'F' THEN 'Poor'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/

Grade  is No such grade

CASE
WHEN boolean_expression_1 THEN result_1
WHEN boolean_expression_2 THEN result_2
...
WHEN boolean_expression_n THEN result_n
[ ELSE
  else_result ]
END]

DECLARE
  grade      CHAR(1) := 'B';
  appraisal  VARCHAR2(120);
  id         NUMBER  := 8429862;
  attendance NUMBER := 150;
  min_days   CONSTANT NUMBER := 200;
  
  FUNCTION attends_this_school (id NUMBER)
    RETURN BOOLEAN IS
  BEGIN
    RETURN TRUE;
  END;
BEGIN
  appraisal :=
  CASE
    WHEN attends_this_school(id) = FALSE
      THEN 'Student not enrolled'
    WHEN grade = 'F' OR attendance < min_days
      THEN 'Poor (poor performance or bad attendance)'
    WHEN grade = 'A' THEN 'Excellent'
    WHEN grade = 'B' THEN 'Very Good'
    WHEN grade = 'C' THEN 'Good'
    WHEN grade = 'D' THEN 'Fair'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE
    ('Result for student ' || id || ' is ' || appraisal);
END;
/
 

Result for student 8429862 is Poor (poor performance or bad attendance)

DECLARE
  grade CHAR(1); -- NULL by default
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE
      WHEN grade IS NULL THEN 'No grade assigned'
      WHEN grade = 'A' THEN 'Excellent'
      WHEN grade = 'B' THEN 'Very Good'
      WHEN grade = 'C' THEN 'Good'
      WHEN grade = 'D' THEN 'Fair'
      WHEN grade = 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;
    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
 

Grade  is No grade assigned

SQL Functions in PL/SQL Expressions

In PL/SQL expressions, you can use all SQL functions except:

• Aggregate functions (such as AVG and COUNT)

• Analytic functions (such as LAG and RATIO_TO_REPORT)

• Collection functions (such as CARDINALITY and SET)

• Data mining functions (such as CLUSTER_ID and FEATURE_VALUE)

• Encoding and decoding functions (such as DECODE and DUMP)

• Model functions (such as ITERATION_NUMBER and PREVIOUS)

• Object reference functions (such as REF and VALUE)

• XML functions (such as APPENDCHILDXML and EXISTSNODE)

• These conversion functions:

  • BIN_TO_NUM

  • CAST

  • RAWTONHEX

  • ROWIDTONCHAR

• These miscellaneous functions:

  • CUBE_TABLE

  • DATAOBJ_TO_PARTITION

  • LNNVL

  • SYS_CONNECT_BY_PATH

  • SYS_TYPEID

  • WIDTH_BUCKET

PL/SQL supports an overload of BITAND for which the arguments and result are BINARY_INTEGER.

When used in a PL/SQL expression, the RAWTOHEX function accepts an argument of data type RAW and returns a VARCHAR2 value with the hexadecimal representation of bytes that comprise the value of the argument. Arguments of types other than RAW can be specified only if they can be implicitly converted to RAW. This conversion is possible for CHAR, VARCHAR2, and LONG values that are valid arguments of the HEXTORAW function, and for LONG RAW and BLOB values of up to 16380 bytes.

How Conditional Compilation Works

Conditional compilation uses selection directives, which are similar to IF statements, to select source text for compilation. The condition in a selection directive usually includes an inquiry directive. Error directives raise user-defined errors. All conditional compilation directives are built from preprocessor control tokens and PL/SQL text.

Topics:

• Preprocessor Control Tokens

• Selection Directives

• Error Directives

• Inquiry Directives

• Static Expressions

$plsql_identifier

$IF boolean_static_expression $THEN
   text
[ $ELSIF boolean_static_expression $THEN
   text
]...
[ $ELSE
   text
$END
]

$ERROR varchar2_static_expression $END

PLS-00179: $ERROR: string

$$name

SQL> CREATE OR REPLACE PROCEDURE p
  2  IS
  3    i PLS_INTEGER;
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('Inside p');
  6    i := $$PLSQL_LINE;
  7    DBMS_OUTPUT.PUT_LINE('i = ' || i);
  8    DBMS_OUTPUT.PUT_LINE('$$PLSQL_LINE = ' || $$PLSQL_LINE);
  9    DBMS_OUTPUT.PUT_LINE('$$PLSQL_UNIT = ' || $$PLSQL_UNIT);
 10  END;
 11  /
 
Procedure created.
 
SQL> BEGIN
  2   p;
  3   DBMS_OUTPUT.PUT_LINE('Outside p');
  4   DBMS_OUTPUT.PUT_LINE('$$PLSQL_UNIT = ' || $$PLSQL_UNIT);
  5  END;
  6  /
 

Inside p
i = 6
$$PLSQL_LINE = 8
$$PLSQL_UNIT = P
Outside p
$$PLSQL_UNIT =
 
PL/SQL procedure successfully completed.

BEGIN
  DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS);
  DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
END;
/

$$PLSCOPE_SETTINGS =
$$PLSQL_CCFLAGS =
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSQL_WARNINGS = ENABLE:ALL
$$NLS_LENGTH_SEMANTICS = BYTE

ALTER SESSION SET PLSQL_CCFLAGS = 
  'name1:value1, name2:value2, ... namen:valuen'

ALTER SESSION SET PLSQL_CCFLAGS = 'flag:TRUE, flag:5'

ALTER SESSION SET
PLSQL_CCFlags = 'Some_Flag:1, Some_Flag:2, PLSQL_CCFlags:99'
/
BEGIN
  Print($$Some_Flag);
  Print($$PLSQL_CCFlags);
END;
/

2
99

constant_name CONSTANT data_type := static_expression;

CREATE PACKAGE my_debug IS
  debug CONSTANT BOOLEAN := TRUE;
  trace CONSTANT BOOLEAN := TRUE;
END my_debug;
/
 
CREATE PROCEDURE my_proc1 IS
BEGIN
  $IF my_debug.debug $THEN
    DBMS_OUTPUT.put_line('Debugging ON');
  $ELSE
    DBMS_OUTPUT.put_line('Debugging OFF');
  $END
END my_proc1;
/
 
CREATE PROCEDURE my_proc2 IS
BEGIN
  $IF my_debug.trace $THEN
    DBMS_OUTPUT.put_line('Tracing ON');
  $ELSE
    DBMS_OUTPUT.put_line('Tracing OFF');
  $END
END my_proc2;
/

Conditional Compilation Examples

Example 2-58 generates an error message if the database version and release is less than 10.2; otherwise, it displays a message saying that the version and release are supported and uses a COMMIT statement that became available at release 10.2.

BEGIN
  $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN  -- selection directive begins
    $ERROR 'unsupported database release' $END  -- error directive
  $ELSE
    DBMS_OUTPUT.PUT_LINE (
      'Release ' || DBMS_DB_VERSION.VERSION || '.' ||
      DBMS_DB_VERSION.RELEASE || ' is supported.'
    );
  -- This COMMIT syntax is newly supported in 10.2:
  COMMIT WRITE IMMEDIATE NOWAIT;
  $END  -- selection directive ends
END;
/
 

Release 11.1 is supported.

Example 2-59 sets the values of the user-defined inquiry directives $$my_debug and $$my_tracing and then uses conditional compilation:

• In the specification of package my_pkg, to determine the base type of the subtype my_real (BINARY_DOUBLE is available only for Oracle Database versions 10g and later.)

• In the body of package my_pkg, to compute the values of my_pi and my_e differently for different database versions

• In the procedure circle_area, to compile some code only if the inquiry directive $$my_debug has the value TRUE.

ALTER SESSION SET PLSQL_CCFLAGS = 'my_debug:FALSE, my_tracing:FALSE';
 
CREATE OR REPLACE PACKAGE my_pkg AS
  SUBTYPE my_real IS
    $IF DBMS_DB_VERSION.VERSION < 10 $THEN
      NUMBER;
    $ELSE
      BINARY_DOUBLE;
    $END
  
  my_pi my_real;
  my_e  my_real;
END my_pkg;
/
 
CREATE OR REPLACE PACKAGE BODY my_pkg AS
BEGIN
  $IF DBMS_DB_VERSION.VERSION < 10 $THEN
    my_pi := 3.14159265358979323846264338327950288420;
    my_e  := 2.71828182845904523536028747135266249775;
  $ELSE
    my_pi := 3.14159265358979323846264338327950288420d;
    my_e  := 2.71828182845904523536028747135266249775d;
  $END
END my_pkg;
/
 
CREATE OR REPLACE PROCEDURE circle_area(radius my_pkg.my_real) IS
  my_area       my_pkg.my_real;
  my_data_type  VARCHAR2(30);
BEGIN
  my_area := my_pkg.my_pi * (radius**2);
  
  DBMS_OUTPUT.PUT_LINE
    ('Radius: ' || TO_CHAR(radius) || ' Area: ' || TO_CHAR(my_area));
  
  $IF $$my_debug $THEN
    SELECT DATA_TYPE INTO my_data_type
    FROM USER_ARGUMENTS
    WHERE OBJECT_NAME = 'CIRCLE_AREA'
    AND ARGUMENT_NAME = 'RADIUS';
 
    DBMS_OUTPUT.PUT_LINE
      ('Data type of the RADIUS argument is: ' || my_data_type);
  $END
END;
/

Retrieving and Printing Post-Processed Source Text

The DBMS_PREPROCESSOR package provides subprograms that retrieve and print the source text of a PL/SQL unit in its post-processed form. For information about the DBMS_PREPROCESSOR package, see Oracle Database PL/SQL Packages and Types Reference.

Example 2-60 invokes the procedure DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE to print the post-processed form of my_pkg (from Example 2-59). Lines of code in Example 2-59 that are not included in the post-processed text appear as blank lines.

CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
  'PACKAGE', 'HR', 'MY_PKG'
);

PACKAGE my_pkg AS
SUBTYPE my_real IS
BINARY_DOUBLE;
my_pi my_real;
my_e my_real;
END my_pkg;

Conditional Compilation Directive Restrictions

A conditional compilation directive cannot appear in the specification of a schema-level user-defined type (created with the "CREATE TYPE Statement"). This type specification specifies the attribute structure of the type, which determines the attribute structure of dependent types and the column structure of dependent tables.

The SQL parser imposes these restrictions on the location of the first conditional compilation directive in a stored PL/SQL unit or anonymous block:

• In a package specification, a package body, a type body, and in a schema-level subprogram with no formal parameters, the first conditional compilation directive cannot appear before the keyword IS or AS.

• In a schema-level subprogram with at least one formal parameter, the first conditional compilation directive cannot appear before the left parenthesis that follows the subprogram name.

  This example is correct:

  CREATE OR REPLACE PROCEDURE my_proc (
    $IF $$xxx $THEN i IN PLS_INTEGER $ELSE i IN INTEGER $END
  ) IS BEGIN NULL; END my_proc;
  /
  

• In a trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN, whichever comes first.

The SQL parser also imposes this restriction: If an anonymous block uses a placeholder, the placeholder cannot appear in a conditional compilation directive. For example:

BEGIN
  :n := 1; -- valid use of placeholder
  $IF ... $THEN
    :n := 1; -- invalid use of placeholder
$END