Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 PL/SQL Language Fundamentals

This chapter explains the following aspects of the PL/SQL language:

Character Sets and Lexical Units

PL/SQL supports two character sets: the database character set, which is used for identifiers and source code, and the national character set, which is used for national language data. This topic applies only to the database character set. For information about the national character set, see NCHAR and NVARCHAR2 Data Types.

PL/SQL programs are written as lines of text using the following characters:

PL/SQL keywords are not case-sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.

A line of PL/SQL text contains groups of characters known as lexical units:

You must separate adjacent identifiers by a space or punctuation. For example:

SQL> BEGIN
  2    IF x > y THEN high := x; END IF;  -- correct
  3    IF x > y THEN high := x; ENDIF;   -- incorrect
  4  END;
  5  /
END;
   *
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
 
SQL> 

You cannot embed spaces inside lexical units (except string literals and comments). For example:

SQL> BEGIN
  2    count := count + 1;   -- correct
  3    count : = count + 1;  -- incorrect
  4  END;
  5  /
  count : = count + 1;  -- incorrect
        *
ERROR at line 3:
ORA-06550: line 3, column 9:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;
 
SQL> 

To show structure, you can split lines using carriage returns, and indent lines using spaces or tabs. For example:

SQL> DECLARE
  2    x    NUMBER := 10;
  3    y    NUMBER := 5;
  4    max  NUMBER;
  5  BEGIN
  6    IF x>y THEN max:=x;ELSE max:=y;END IF;  -- correct but hard to read
  7  
  8    -- Easier to read:
  9  
 10    IF x > y THEN
 11      max:=x;
 12    ELSE
 13      max:=y;
 14    END IF;
 15  END;
 16  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. Table 2-1 lists the PL/SQL delimiters.

Table 2-1 PL/SQL Delimiters

Symbol Meaning

+

addition operator

%

attribute indicator

'

character string delimiter

.

component selector

/

division operator

(

expression or list delimiter

)

expression or list delimiter

:

host variable indicator

,

item separator

*

multiplication operator

"

quoted identifier delimiter

=

relational operator

<

relational operator

>

relational operator

@

remote access indicator

;

statement terminator

-

subtraction/negation operator

:=

assignment operator

=>

association operator

||

concatenation operator

**

exponentiation operator

<<

label delimiter (begin)

>>

label delimiter (end)

/*

multi-line comment delimiter (begin)

*/

multi-line comment delimiter (end)

..

range operator

<>

relational operator

!=

relational operator

~=

relational operator

^=

relational operator

<=

relational operator

>=

relational operator

--

single-line comment indicator


Identifiers

You use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages.

The minimum length of an identifier is one character; the maximum length is 30 characters. The first character must be a letter, but each later character can be either a letter, numeral, dollar sign ($), underscore (_), or number sign (#). For example, the following are acceptable identifiers:

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

Characters other than the aforementioned are not allowed in identifiers. For example, the following are not acceptable identifiers:

mine&yours  -- ampersand (&) is not allowed
debit-amount -- hyphen (-) is not allowed
on/off       -- slash (/) is not allowed
user id      -- space is not allowed

PL/SQL is not case-sensitive with respect to identifiers. For example, PL/SQL considers the following to be the same:

lastname
LastName
LASTNAME

Every character, alphabetic or not, is significant. For example, PL/SQL considers the following to be different:

lastname
last_name

Make your identifiers meaningful rather than obscure. For example, the meaning of cost_per_thousand is obvious, while the meaning of cpt is not.

Topics:

Reserved Words and Keywords

Both reserved words and keywords have special meaning in PL/SQL. The difference between reserved words and keywords is that you cannot use reserved words as identifiers. You can use keywords as as identifiers, but it is not recommended.

Trying to redefine a reserved word causes a compilation error. For example:

SQL> DECLARE
  2    end BOOLEAN;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
  end BOOLEAN;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin was inserted before "END" to continue.
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted
 
SQL> 

The PL/SQL reserved words are listed in Table D-1.

Keywords also have special meaning in PL/SQL, but you can redefine them (this is not recommended). The PL/SQL keywords are listed inTable D-2.

Predefined Identifiers

Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are valid:

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

The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.

Literals

A literal is an explicit numeric, character, string, or BOOLEAN value not represented by an identifier. The numeric literal 147 and the BOOLEAN literal FALSE are examples. For information about the PL/SQL data types, see Predefined PL/SQL Scalar Data Types and Subtypes.

Topics:

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. For example:

030   6   -14   0   +32767

A real literal is an optionally signed whole or fractional number with a decimal point. For example:

6.6667   0.0   -12.0   3.14159   +8300.00   .5   25.

PL/SQL considers numbers such as 12.0 and 25. to be reals even though they have integral values.

A numeric literal value that is composed only of digits and falls in the range -2147483648 to 2147483647 has a PLS_INTEGER data type; otherwise this literal has the NUMBER data type. You can add the f of d suffix to a literal value that is composed only of digits to specify the BINARY_FLOAT or BINARY_TABLE respectively. For the properties of the data types, see Predefined PL/SQL Numeric Data Types and Subtypes.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. For example:

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

xEy stands for "x times ten to the power of y." As the next example shows, the number after E is the power of ten by which the number before E is multiplied (the double asterisk (**) is the exponentiation operator):

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

The number after E also corresponds to the number of places the decimal point shifts. In the preceding example, the implicit decimal point shifted three places to the right. In the following example, it shifts three places to the left:

5E-3 = 5 * 10**-3 = 5 * 0.001 = 0.005

The absolute value of a NUMBER literal can be in the range 1.0E-130 up to (but not including) 1.0E126. The literal can also be 0. For information about results outside the valid range, see NUMBER Data Type.

Example 2-1 NUMBER Literals

SQL> DECLARE
  2    n NUMBER;
  3  BEGIN
  4    n := -9.999999E-130;
  5    n :=  9.999E125;
  6    n := 10.0E125;
  7  END;
  8  /
  n := 10.0E125;
       *
ERROR at line 6:
ORA-06550: line 6, column 8:
PLS-00569: numeric overflow or underflow
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
 
SQL> 

Real literals can also use the trailing letters f and d to specify the types BINARY_FLOAT and BINARY_DOUBLE, as shown in Example 2-2.

Example 2-2 Using BINARY_FLOAT and BINARY_DOUBLE

SQL> DECLARE
  2    x BINARY_FLOAT := sqrt(2.0f);
  3      -- single-precision floating-point number
  4    y BINARY_DOUBLE := sqrt(2.0d);
  5      -- double-precision floating-point number
  6  BEGIN
  7    NULL;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Character Literals

A character literal is an individual character enclosed by single quotes ('). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. For example:

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

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different. Also, the character literals '0'..'9' are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. All string literals except the null string ('') have data type CHAR. For example:

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

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

'baker'
'Baker'

To represent an apostrophe within a string, you can write two single quotes, which is not the same as writing a double quote:

'I''m a string, you''re a string.'

You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then need not escape other single quotation marks inside the literal:

-- q'!...!' notation allows use of single quotes inside literal
string_var := q'!I'm a string, you're a string.!';

You can use delimiters [, {, <, and (, pair them with ], }, >, and ), pass a string literal representing a SQL statement to a subprogram, without doubling the quotation marks around 'INVALID' as follows:

func_call(q'[SELECT index_name FROM user_indexes
  WHERE status ='INVALID']');

For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q, as in the following example, where 00E0 represents the character é:

where_clause := nq'#WHERE COL_VALUE LIKE '%\00E9'#';

For more information about the NCHAR data type and unicode strings, see Oracle Database Globalization Support Guide.

BOOLEAN Literals

BOOLEAN literals are the predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Remember, BOOLEAN literals are values, not strings. For example, TRUE is no less a value than the number 25.

Date and Time Literals

Datetime literals have various formats depending on the data type, as in Example 2-3.

Example 2-3 Using DateTime Literals

SQL> DECLARE
  2    d1 DATE      := DATE '1998-12-25';
  3    t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
  4  
  5    t2 TIMESTAMP WITH TIME ZONE :=
  6       TIMESTAMP '1997-01-31 09:26:56.66 +02:00';
  7  
  8    -- Three years and two months
  9    -- For greater precision, use the day-to-second interval
 10  
 11    i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;
 12  
 13    -- Five days, four hours, three minutes, two and 1/100 seconds
 14  
 15     i2 INTERVAL DAY TO SECOND :=
 16       INTERVAL '5 04:03:02.01' DAY TO SECOND;
 17  
 18  BEGIN
 19    NULL;
 20  END;
 21  /
 
PL/SQL procedure successfully completed.
 
SQL> 

See Also:

Comments

The PL/SQL compiler ignores comments. Adding comments to your program promotes readability and aids understanding. 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:

See Also:

Comment

Single-Line Comments

A single-line comment begins with --. It can appear anywhere on a line, and it extends to the end of the line, as in Example 2-4.

Example 2-4 Single-Line Comments

SQL> DECLARE
  2    howmany     NUMBER;
  3    num_tables  NUMBER;
  4  BEGIN
  5    -- Begin processing
  6    SELECT COUNT(*) INTO howmany
  7      FROM USER_OBJECTS
  8        WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
  9     num_tables := howmany;          -- Compute some other value
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL> 

While testing or debugging a program, you might want to disable a line of code by making it a comment. For example:

-- DELETE FROM employees WHERE comm_pct IS NULL

Multiline Comments

A multiline comments begins with /*, ends with */, and can span multiple lines, as in Example 2-5. You can use multiline comment delimiters to "comment out" sections of code.

Example 2-5 Multiline Comment

SQL> DECLARE
  2     some_condition  BOOLEAN;
  3     pi              NUMBER := 3.1415926;
  4     radius          NUMBER := 15;
  5     area            NUMBER;
  6  BEGIN
  7    /* Perform some simple tests and assignments */
  8    IF 2 + 2 = 4 THEN
  9      some_condition := TRUE;
 10      /* We expect this THEN to always be performed */
 11    END IF;
 12    /* The following line computes the area of a circle using pi,
 13    which is the ratio between the circumference and diameter.
 14    After the area is computed, the result is displayed. */
 15    area := pi * radius**2;
 16    DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
 17  END;
 18  /
The area is: 706.858335
 
PL/SQL procedure successfully completed.
 
SQL>

Declarations

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its data type, and name the storage location so that you can reference it.

Topics:

Variables

Example 2-6 declares a variable of type DATE, a variable of type SMALLINT (to which it assigns the initial value zero), and three variables of type REAL. The expression following the assignment operator can be arbitrarily complex, and can refer to previously initialized variables, as in the declaration of the variable area.

Variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL.

Example 2-6 Declaring Variables

SQL> DECLARE
  2    birthday   DATE;
  3    emp_count  SMALLINT := 0;
  4    pi         REAL := 3.14159;
  5    radius     REAL := 1;
  6    area       REAL := pi * radius**2;
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Constants

To declare a constant, put the keyword CONSTANT before the type specifier. The following declaration names a constant of type REAL and assigns an unchangeable value of 5000 to the constant. A constant must be initialized in its declaration. Constants are initialized every time a block or subprogram is entered.

Example 2-7 Declaring Constants

SQL> DECLARE
  2    credit_limit      CONSTANT REAL    := 5000.00;
  3    max_days_in_year  CONSTANT INTEGER := 366;
  4    urban_legend      CONSTANT BOOLEAN := FALSE;
  5  BEGIN
  6    NULL;
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Using DEFAULT

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Use DEFAULT for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value.

Example 2-8 Assigning Default Values to Variables with DEFAULT Keyword

SQL> DECLARE
  2    blood_type CHAR DEFAULT 'O';         -- Same as blood_type CHAR := 'O';
  3  
  4    hours_worked    INTEGER DEFAULT 40;  -- Typical value
  5    employee_count  INTEGER := 0;        -- No typical value
  6  
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>

Using NOT NULL

A declaration can impose the NOT NULL constraint, which prevents you from assigning a null value to the variable. Because variables are initialized to NULL by default, a declaration that specifies NOT NULL must also specify a default value.

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 the NOT NULL constraint, and you must specify a default value.

Example 2-9 Declaring Variables with NOT NULL Constraint

SQL> DECLARE
  2    acct_id INTEGER(4) NOT NULL := 9999;
  3    a NATURALN                  := 9999;
  4    b POSITIVEN                 := 9999;
  5    c SIMPLE_INTEGER            := 9999;
  6  BEGIN
  7    NULL;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Using the %TYPE Attribute

The %TYPE attribute lets you declare a constant, variable, field, or parameter to be of the same data type a previously declared variable, field, record, nested table, or database column. If the referenced item changes, your declaration is automatically updated. You need not change your code when, for example, the length of a VARCHAR2 column increases.

An item declared with %TYPE (the referencing item) always inherits the data type of the referenced item. The referencing item inherits the constraints only if the referenced item is not a database column. The referencing item inherits the default value only if the referencing item is not a database column and does not have the NOT NULL constraint.

In Example 2-10, the variable debit inherits the data type of the variable credit. The variables upper_name, lower_name, and init_name inherit the data type and default value of the variable name.

Example 2-10 Using %TYPE to Declare Variables of the Types of Other Variables

SQL> DECLARE
  2    credit  PLS_INTEGER RANGE 1000..25000;
  3    debit   credit%TYPE;  -- inherits data type
  4  
  5    name        VARCHAR2(20) := 'JoHn SmItH';
  6    upper_name  name%TYPE;  -- inherits data type and default value
  7    lower_name  name%TYPE;  -- inherits data type and default value
  8    init_name   name%TYPE;  -- inherits data type and default value
  9  BEGIN
 10    DBMS_OUTPUT.PUT_LINE ('name: ' || name);
 11    DBMS_OUTPUT.PUT_LINE ('upper_name: ' || UPPER(name));
 12    DBMS_OUTPUT.PUT_LINE ('lower_name: ' || LOWER(name));
 13    DBMS_OUTPUT.PUT_LINE ('init_name:  ' || INITCAP(name));
 14  END;
 15  /
name: JoHn SmItH
upper_name: JOHN SMITH
lower_name: john smith
init_name:  John Smith
 
PL/SQL procedure successfully completed.
 
SQL> 

If you add a NOT NULL constraint to the variable name in Example 2-10, and declare another variable that references it, you must specify a default value for the new item, as Example 2-11 shows.

Example 2-11 Using %TYPE Incorrectly with NOT NULL Referenced Type

SQL> DECLARE
  2    name    VARCHAR2(20) NOT NULL := 'JoHn SmItH';
  3    same_name   name%TYPE;
  4  BEGIN
  5    NULL;
  6  END;
  7  /
  same_name   name%TYPE;
              *
ERROR at line 3:
ORA-06550: line 3, column 15:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
 
SQL> 

In Example 2-12, the variables upper_name, lower_name, and init_name inherit the data type and NOT NULL constraint of the variable name, but not its default value. To avoid the error shown in Example 2-11, they are assigned their own default values.

Example 2-12 Using %TYPE Correctly with NOT NULL Referenced Type

SQL> DECLARE
  2    name        VARCHAR2(20) NOT NULL := 'JoHn SmItH';
  3    upper_name  name%TYPE := UPPER(name);
  4    lower_name  name%TYPE := LOWER(name);
  5    init_name   name%TYPE := INITCAP(name);
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('name: ' || name);
  8    DBMS_OUTPUT.PUT_LINE('upper_name: ' || upper_name);
  9    DBMS_OUTPUT.PUT_LINE('lower_name: ' || lower_name);
 10    DBMS_OUTPUT.PUT_LINE('init_name:  ' || init_name);
 11  END;
 12  /
name: JoHn SmItH
upper_name: JOHN SMITH
lower_name: john smith
init_name:  John Smith
 
PL/SQL procedure successfully completed.
 
SQL> 

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. When you use table_name.column_name.%TYPE to declare a data item, you need not know the referenced data type or its attributes (such as precision, scale, and length), and if they change, you need not update your code.

Example 2-13 shows that referencing items do not inherit column constraints or default values from database columns.

Example 2-13 Using %TYPE to Declare Variables of the Types of Table Columns

SQL> CREATE TABLE employees_temp (
  2    empid  NUMBER(6) NOT NULL PRIMARY KEY,
  3    deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid
  4      CHECK (deptid BETWEEN 100 AND 200),
  5    deptname VARCHAR2(30) DEFAULT 'Sales'
  6  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2    v_empid    employees_temp.empid%TYPE;
  3    v_deptid   employees_temp.deptid%TYPE;
  4    v_deptname employees_temp.deptname%TYPE;
  5  BEGIN
  6    v_empid := NULL;  -- Null constraint not inherited
  7    v_deptid := 50;   -- Check constraint not inherited
  8    DBMS_OUTPUT.PUT_LINE
  9      ('v_deptname: ' || v_deptname);  -- Default value not inherited
 10  END;
 11  /
v_deptname:
 
PL/SQL procedure successfully completed.
 
SQL> 

See Also:

Using the %ROWTYPE Attribute

The %ROWTYPE attribute lets you declare a record that represents a row in a table or view. For each column in the referenced table or view, the record has a field with the same name and data type. To reference a field in the record, use record_name.field_name. The record fields do not inherit the constraints or default values of the corresponding columns, as Example 2-14 shows.

If the referenced item table or view changes, your declaration is automatically updated. You need not change your code when, for example, columns are added or dropped from the table or view.

Example 2-14 Using %ROWTYPE to Declare a Record that Represents a Table Row

SQL> CREATE TABLE employees_temp (
  2    empid  NUMBER(6) NOT NULL PRIMARY KEY,
  3    deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid
  4      CHECK (deptid BETWEEN 100 AND 200),
  5    deptname VARCHAR2(30) DEFAULT 'Sales'
  6  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2    emprec  employees_temp%ROWTYPE;
  3  BEGIN
  4    emprec.empid := NULL;  -- Null constraint not inherited
  5    emprec.deptid := 50;   -- Check constraint not inherited
  6    DBMS_OUTPUT.PUT_LINE
  7      ('emprec.deptname: ' || emprec.deptname);
  8        -- Default value not inherited
  9  END;
 10  /
emprec.deptname:
 
PL/SQL procedure successfully completed.
 
SQL> 

See Also:

Example 3-15

The record emprec in Example 2-14 has a field for every column in the table employees_temp. The record dept_rec in Example 2-15 has columns for a subset of columns in the departments table.

Example 2-15 Declaring a Record that Represents a Subset of Table Columns

SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT department_id, department_name
  4        FROM departments;
  5  
  6    dept_rec c1%ROWTYPE;  -- includes subset of columns in table
  7  
  8  BEGIN
  9    NULL;
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL> 

The record join_rec in Example 2-15 has columns from two tables, employees and departments.

Example 2-16 Declaring a Record that Represents a Row from a Join

SQL> DECLARE
  2    CURSOR c2 IS
  3      SELECT employee_id, email, employees.manager_id, location_id
  4        FROM employees, departments
  5          WHERE employees.department_id = departments.department_id;
  6  
  7     join_rec  c2%ROWTYPE;  -- includes columns from two tables
  8  
  9  BEGIN
 10    NULL;
 11  END;
 12  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

Aggregate Assignment

A %ROWTYPE declaration cannot include an initialization clause, but there are two ways to assign values to all fields of a record at once:

  • If their declarations refer to the same table or cursor, you can assign one record to another, as in Example 2-17.

  • Use the SELECT or FETCH statement to assign a list of column values to a record.

    The column names must appear in the order in which they were defined in the CREATE TABLE or CREATE VIEW statement that created the referenced table or view. There is no constructor for a record type, so you cannot assign a list of column values to a record by using an assignment statement.

Example 2-17 Assigning One Record to Another, Correctly and Incorrectly

SQL> DECLARE
  2    dept_rec1  departments%ROWTYPE;
  3    dept_rec2  departments%ROWTYPE;
  4  
  5    CURSOR c1 IS SELECT department_id, location_id
  6      FROM departments;
  7  
  8    dept_rec3 c1%ROWTYPE;
  9    dept_rec4 c1%ROWTYPE;
 10  
 11  BEGIN
 12    dept_rec1 := dept_rec2;  -- declarations refer to same table
 13    dept_rec3 := dept_rec4;  -- declarations refer to same cursor
 14    dept_rec2 := dept_rec3;
 15  END;
 16  /
  dept_rec2 := dept_rec3;
               *
ERROR at line 14:
ORA-06550: line 14, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored
 
SQL> 

Example 2-18 uses the SELECT INTO statement to assign a list of column values to a record.

Example 2-18 Using SELECT INTO for Aggregate Assignment

SQL> DECLARE
  2    dept_rec departments%ROWTYPE;
  3  BEGIN
  4    SELECT * INTO dept_rec
  5      FROM departments
  6        WHERE department_id = 30
  7          AND ROWNUM < 2;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Using Aliases

Select-list items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases, such as complete_name in Example 2-19.

Example 2-19 Using an Alias for an Expression Associated with %ROWTYPE

SQL> BEGIN
  2    FOR item IN
  3      (SELECT (first_name || ' ' || last_name) complete_name
  4        FROM employees
  5           WHERE ROWNUM < 11
  6      ) LOOP
  7        DBMS_OUTPUT.PUT_LINE
  8          ('Employee name: ' || item.complete_name);
  9      END LOOP;
 10  END;
 11  /
Employee name: Ellen Abel
Employee name: Sundar Ande
Employee name: Mozhe Atkinson
Employee name: David Austin
Employee name: Hermann Baer
Employee name: Shelli Baida
Employee name: Amit Banda
Employee name: Elizabeth Bates
Employee name: Sarah Bell
Employee name: David Bernstein
 
PL/SQL procedure successfully completed.
 
SQL> 

Restrictions on Declarations

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements.

PL/SQL does allow the forward declaration of subprograms. For more information, see Creating Nested Subprograms that Invoke Each Other.

Some languages enable you to declare a list of variables that have the same data type. PL/SQL does not allow this. You must declare each variable separately. To save space, you can put more than one declaration on a line. For example:

SQL> DECLARE
  2    i, j, k, l SMALLINT;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
  i, j, k, l SMALLINT;
   *
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00103: Encountered the symbol "," when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table long double ref
char time timestamp interval date binary national character
nchar
ORA-06550: line 2, column 14:
PLS-00103: Encountered the symbol "SMALLINT" when expecting one of the
following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between ||
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted
 
 
SQL> DECLARE
  2    i SMALLINT; j SMALLINT; k SMALLINT; l SMALLINT;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Naming Conventions

The same naming conventions apply to PL/SQL constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example:

Topics:

Scope

Within the same scope, all declared identifiers must be unique. Even if their data types differ, variables and parameters cannot share the same name. An error occurs when the duplicate identifier is referenced, as in Example 2-20.

Example 2-20 Duplicate Identifiers in Same Scope

SQL> DECLARE
  2    id  BOOLEAN;
  3    id  VARCHAR2(5);  -- duplicate identifier
  4  BEGIN
  5    id := FALSE;
  6  END;
  7  /
  id := FALSE;
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00371: at most one declaration for 'ID' is permitted
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
 
SQL> 

For the scoping rules that apply to identifiers, see Scope and Visibility of PL/SQL Identifiers.

Case Sensitivity

Like all identifiers, the names of constants, variables, and parameters are not case sensitive, as Example 2-21 shows.

Example 2-21 Case Insensitivity of Identifiers

SQL> DECLARE
  2     zip_code INTEGER;
  3     Zip_Code INTEGER;
  4  BEGIN
  5    zip_code := 90120;
  6  END;
  7  /
  zip_code := 90120;
  *
ERROR at line 5:
ORA-06550: line 5, column 3:
PLS-00371: at most one declaration for 'ZIP_CODE' is permitted
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
 
SQL> 

Name Resolution

In ambiguous SQL statements, the names of database columns take precedence over the names of local variables and formal parameters. For example, if a variable and a column with the same name are used in a WHERE clause, SQL considers both names to refer to the column.

Caution:

When a variable name is interpreted as a column name, data can be deleted unintentionally, as Example 2-22 shows. Example 2-22 also shows two ways to avoid this error.

Example 2-22 Using a Block Label for Name Resolution

SQL> CREATE TABLE employees2 AS
  2    SELECT last_name FROM employees;
 
Table created.
 
SQL> 
SQL> -- Deletes everyone, because both LAST_NAMEs refer to the column:
SQL> 
SQL> BEGIN
  2    DELETE FROM employees2
  3      WHERE last_name = last_name;
  4    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
  5  END;
  6  /
Deleted 107 rows.
 
PL/SQL procedure successfully completed.
 
SQL> ROLLBACK;
 
Rollback complete.
 
SQL> 
SQL> -- Avoid error by giving column and variable different names:
SQL> 
SQL> DECLARE
  2    last_name    VARCHAR2(10) := 'King';
  3    v_last_name  VARCHAR2(10) := 'King';
  4  BEGIN
  5    DELETE FROM employees2
  6      WHERE last_name = v_last_name;
  7    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
  8  END;
  9  /
Deleted 2 rows.
 
PL/SQL procedure successfully completed.
 
SQL> ROLLBACK;
 
Rollback complete.
 
SQL> 
SQL> -- Avoid error by qualifying variable with block name:
SQL> 
SQL> <<main>> -- Label block for future reference
  2  DECLARE
  3    last_name    VARCHAR2(10) := 'King';
  4    v_last_name  VARCHAR2(10) := 'King';
  5  BEGIN
  6    DELETE FROM employees2
  7      WHERE last_name = main.last_name;
  8    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
  9  END;
 10  /
Deleted 2 rows.
 
PL/SQL procedure successfully completed.
 
SQL> ROLLBACK;
 
Rollback complete.
 
SQL> 

You can use a subprogram name to qualify references to local variables and formal parameters, as in Example 2-23.

Example 2-23 Using a Subprogram Name for Name Resolution

SQL> DECLARE
  2    FUNCTION dept_name (department_id IN NUMBER)
  3      RETURN departments.department_name%TYPE
  4    IS
  5      department_name  departments.department_name%TYPE;
  6    BEGIN
  7      SELECT department_name INTO dept_name.department_name
  8        --   ^column              ^local variable
  9        FROM departments
 10          WHERE department_id = dept_name.department_id;
 11          --    ^column         ^formal parameter
 12      RETURN department_name;
 13    END;
 14  BEGIN
 15    FOR item IN (SELECT department_id FROM departments)
 16    LOOP
 17      DBMS_OUTPUT.PUT_LINE
 18        ('Department: ' || dept_name(item.department_id));
 19    END LOOP;
 20  END;
 21  /
Department: Administration
Department: Marketing
Department: Purchasing
Department: Human Resources
Department: Shipping
Department: IT
Department: Public Relations
Department: Sales
Department: Executive
Department: Finance
Department: Accounting
Department: Treasury
Department: Corporate Tax
Department: Control And Credit
Department: Shareholder Services
Department: Benefits
Department: Manufacturing
Department: Construction
Department: Contracting
Department: Operations
Department: IT Support
Department: NOC
Department: IT Helpdesk
Department: Government Sales
Department: Retail Sales
Department: Recruiting
Department: Payroll
 
PL/SQL procedure successfully completed.
 
SQL>

See Also:

Appendix B, "How PL/SQL Resolves Identifier Names" for more information about name resolution

Synonyms

You can use the SQL statement CREATE SYNONYM to create synonyms to provide location transparency for remote schema objects. You cannot create synonyms for items declared within PL/SQL subprograms or packages.

See:

Oracle Database SQL Language Reference for information about the SQL statement CREATE SYNONYM

Scope and Visibility of PL/SQL Identifiers

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is the region of a PL/SQL unit from which you can reference the identifier. The visibility of an identifier is the region of a PL/SQL unit from which you can reference the identifier without qualifying it.

An identifier declared in a PL/SQL unit is local to that unit and global to its subunits. If a subunit redeclares a global identifier, then inside the subunit, both identifiers are in scope, but only the local identifier is visible. To reference the global identifier, the subunit must qualify it.

You cannot declare an identifier twice in the same PL/SQL unit, but you can declare the same identifier in two different units. The two items represented by the identifier are distinct, and changing one does not affect the other.

A PL/SQL unit cannot reference identifiers declared in other units at the same level, because those identifiers are neither local nor global to the block.

Example 2-24 shows the scope and visibility of several global and local identifiers. The global identifier a is redeclared in the first sub-block.

Example 2-24 Scope and Visibility of Identifiers

SQL> DECLARE
  2    a CHAR;  -- Scope of a (CHAR) begins
  3    b REAL;  -- Scope of b begins
  4  BEGIN
  5    -- Visible: a (CHAR), b
  6  
  7    DECLARE
  8      a INTEGER;  -- Scope of a (INTEGER) begins
  9      c REAL;     -- Scope of c begins
 10    BEGIN
 11      -- Visible: a (INTEGER), b, c
 12      NULL;
 13    END;          -- Scopes of a (INTEGER) and c end
 14  
 15    DECLARE
 16      d REAL;     -- Scope of d begins
 17    BEGIN
 18      -- Visible: a (CHAR), b, d
 19      NULL;
 20    END;          -- Scope of d ends
 21  
 22    -- Visible: a (CHAR), b
 23  END;            -- Scopes of a (CHAR) and b end
 24  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Example 2-25 declares the variable birthdate in a labeled block, outer, redeclares it in a sub-block, and then references it in the sub-block by qualifying its name with the block label.

Example 2-25 Qualifying a Redeclared Global Identifier with a Block Label

SQL> <<outer>>
  2  DECLARE
  3    birthdate DATE := '09-AUG-70';
  4  BEGIN
  5    DECLARE
  6      birthdate DATE;
  7    BEGIN
  8      birthdate := '29-SEP-70';
  9  
 10      IF birthdate = outer.birthdate THEN
 11        DBMS_OUTPUT.PUT_LINE ('Same Birthday');
 12      ELSE
 13        DBMS_OUTPUT.PUT_LINE ('Different Birthday');
 14      END IF;
 15    END;
 16  END;
 17  /
Different Birthday
 
PL/SQL procedure successfully completed.
 
SQL>

Example 2-26 declares the variable rating in a procedure, check_credit, redeclares it in a function within the procedure, and then references it in the function by qualifying its name with the procedure name. (The built-in SQL function TO_CHAR returns the character equivalent of its argument. For more information about TO_CHAR, see Oracle Database SQL Language Reference.)

Example 2-26 Qualifying an Identifier with a Subprogram Name

SQL> CREATE OR REPLACE PROCEDURE check_credit (limit NUMBER) AS
  2    rating NUMBER := 3;
  3  
  4    FUNCTION check_rating RETURN BOOLEAN IS
  5      rating  NUMBER := 1;
  6      over_limit  BOOLEAN;
  7    BEGIN
  8      IF check_credit.rating <= limit THEN
  9        over_limit := FALSE;
 10      ELSE
 11        over_limit := TRUE;
 12        rating := limit;
 13      END IF;
 14      RETURN over_limit;
 15    END check_rating;
 16  BEGIN
 17    IF check_rating THEN
 18      DBMS_OUTPUT.PUT_LINE
 19        ('Credit rating over limit (' || TO_CHAR(limit) || ').  '
 20        || 'Rating: ' || TO_CHAR(rating));
 21      ELSE
 22        DBMS_OUTPUT.PUT_LINE
 23          ('Credit rating OK.  ' || 'Rating: ' || TO_CHAR(rating));
 24     END IF;
 25  END;
 26  /
 
Procedure created.
 
SQL> BEGIN
  2    check_credit(1);
  3  END;
  4  /
Credit rating over limit (1).  Rating: 3
 
PL/SQL procedure successfully completed.
 
SQL> 

Within the same scope, give labels and subprograms unique names to avoid confusion and unexpected results.

Example 2-27 has both a block and a subprogram named echo. Both the block and the subprogram declare a variable named x. Within the subprogram, echo.x refers to the local variable x, not to the global variable x.

Example 2-27 Label and Subprogram with Same Name in Same Scope

SQL> <<echo>>
  2  DECLARE
  3    x  NUMBER := 5;
  4  
  5    PROCEDURE echo AS
  6      x  NUMBER := 0;
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE('x = ' || x);
  9      DBMS_OUTPUT.PUT_LINE('echo.x = ' || echo.x);
 10    END;
 11  
 12  BEGIN
 13     echo;
 14  END;
 15  /
x = 0
echo.x = 0
 
PL/SQL procedure successfully completed.
 
SQL> 

Example 2-28 has both a block and a subprogram named echo. Both the block and the subprogram declare a variable named x. Within the subprogram, echo.x refers to the local variable x, not to the global variable x.

Example 2-28 has two labels for the outer block, compute_ratio and another_label. The second label is reused in the inner block. Within the inner block, another_label.denominator refers to the local variable denominator, not to the global variable denominator, which results in the error ZERO_DIVIDE.

Example 2-28 Block with Multiple and Duplicate Labels

SQL> <<compute_ratio>>
  2  <<another_label>>
  3  DECLARE
  4    numerator   NUMBER := 22;
  5    denominator NUMBER := 7;
  6  BEGIN
  7    <<another_label>>
  8    DECLARE
  9      denominator NUMBER := 0;
 10    BEGIN
 11      DBMS_OUTPUT.PUT_LINE('Ratio with compute_ratio.denominator = ');
 12      DBMS_OUTPUT.PUT_LINE(numerator/compute_ratio.denominator);
 13  
 14      DBMS_OUTPUT.PUT_LINE('Ratio with another_label.denominator = ');
 15      DBMS_OUTPUT.PUT_LINE(numerator/another_label.denominator);
 16  
 17    EXCEPTION
 18      WHEN ZERO_DIVIDE THEN
 19        DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide '
 20          || numerator || ' by ' || denominator);
 21      WHEN OTHERS THEN
 22        DBMS_OUTPUT.PUT_LINE('Unexpected error.');
 23    END inner_label;
 24  END compute_ratio;
 25  /
Ratio with compute_ratio.denominator =
3.14285714285714285714285714285714285714
Ratio with another_label.denominator =
Divide-by-zero error: cannot divide 22 by 0
 
PL/SQL procedure successfully completed.
 
SQL> 

Assigning Values to Variables

You can assign a default value to a variable when you declare it (as explained in Variables) or after you have declared it, with an assignment statement. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:

bonus := salary * 0.15;

The expression following the assignment operator (:=) can be arbitrarily complex, but it must yield a data type that is the same as, or convertible to, the data type of the variable.

Variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. Unless you explicitly initialize a variable, its value is NULL, as Example 2-29 shows.

Example 2-29 Variable Initialized to NULL by Default

SQL> DECLARE
  2    counter INTEGER;
  3  BEGIN
  4     counter := counter + 1;
  5  
  6     IF counter IS NULL THEN
  7        DBMS_OUTPUT.PUT_LINE('counter is NULL.');
  8     END IF;
  9  END;
 10  /
counter is NULL.
 
PL/SQL procedure successfully completed.
 
SQL> 

To avoid unexpected results, never reference a variable before assigning it a value.

Topics:

Assigning BOOLEAN Values

Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable, either as literals or as the results of expressions.

In Example 2-30, the BOOLEAN variable done is initialized to NULL by default, assigned the literal value FALSE, compared to a literal BOOLEAN value, and assigned the value of a BOOLEAN expression.

Example 2-30 Assigning BOOLEAN Values

SQL> DECLARE
  2    done    BOOLEAN;            -- Initialize to NULL by default
  3    counter NUMBER := 0;
  4  BEGIN
  5    done := FALSE;              -- Assign literal value
  6    WHILE done != TRUE          -- Compare to literal value
  7    LOOP
  8      counter := counter + 1;
  9      done := (counter > 500);  -- Assign value of BOOLEAN expression
 10    END LOOP;
 11  END;
 12  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Assigning SQL Query Results to PL/SQL Variables

You can use the SELECT INTO statement to assign values to a variable. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list, as in Example 2-31.

Example 2-31 Assigning Query Results to Variables

SQL> DECLARE
  2    emp_id    employees.employee_id%TYPE := 100;
  3    emp_name  employees.last_name%TYPE;
  4    wages     NUMBER(7,2);
  5  BEGIN
  6    SELECT last_name, salary + (salary * nvl(commission_pct,0))
  7      INTO emp_name, wages
  8        FROM employees
  9          WHERE employee_id = emp_id;
 10  
 11    DBMS_OUTPUT.PUT_LINE
 12      ('Employee ' || emp_name || ' might make ' || wages);
 13  END;
 14  /
Employee King might make 24000
 
PL/SQL procedure successfully completed.
 
SQL> 

Because SQL does not have a BOOLEAN type, you cannot select column values into a BOOLEAN variable. For more information about assigning variables with the DML statements, including situations when the value of a variable is undefined, see Data Manipulation Language (DML) Statements.

PL/SQL Expressions and Comparisons

The simplest PL/SQL expression consists of a single variable, which yields a value directly. You can build arbitrarily complex PL/SQL expressions from operands and operators. An operand is a variable, constant, literal, placeholder, or function call. An operator is either unary or binary, operating on either one operand or two operands, respectively. An example of a unary operator is negation (-). An example of a binary operator is addition (+).

An example of a simple arithmetic expression is:

-X / 2 + 3

PL/SQL evaluates an expression by combining the values of the operands as specified by the operators. An expression always returns a single value. PL/SQL determines the data type of this value by examining the expression and the context in which it appears.

Topics:

Concatenation Operator

The concatenation operator (||) appends one string operand to another. Each string can be CHAR, VARCHAR2, CLOB, or the equivalent Unicode-enabled type. If either string is a CLOB, the result is a temporary CLOB; otherwise, it is a VARCHAR2 value.

Example 2-32 and many other examples in this book use the concatenation operator.

Example 2-32 Concatenation Operator

SQL> DECLARE
  2    x VARCHAR2(4) := 'suit';
  3    y VARCHAR2(4) := 'case';
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE (x || y);
  6  END;
  7  /
suitcase
 
PL/SQL procedure successfully completed.
 
SQL> 

Operator Precedence

The operations within an expression are evaluated in order of precedence. Table 2-2 shows operator precedence from highest to lowest. Operators with equal precedence are applied in no particular order.

Table 2-2 Operator Precedence

Operator Operation

**

exponentiation

+, -

identity, negation

*, /

multiplication, division

+, -, ||

addition, subtraction, concatenation

=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

comparison

NOT

logical negation

AND

conjunction

OR

inclusion


You can use parentheses to control the order of evaluation. When parentheses are nested, the most deeply nested subexpression is evaluated first. You can use parentheses to improve readability, even when you do not need them to control the order of evaluation. (In Example 2-33, the built-in SQL function TO_CHAR returns the character equivalent of its argument. For more information about TO_CHAR, see Oracle Database SQL Language Reference.)

Example 2-33 Operator Precedence

SQL> DECLARE
  2    salary      NUMBER := 60000;
  3    commission  NUMBER := 0.10;
  4  BEGIN
  5    -- Division has higher precedence than addition:
  6  
  7    DBMS_OUTPUT.PUT_LINE('5 + 12 / 4 = ' || TO_CHAR(5 + 12 / 4));
  8    DBMS_OUTPUT.PUT_LINE('12 / 4 + 5 = ' || TO_CHAR(12 / 4 + 5));
  9  
 10    -- Parentheses override default operator precedence:
 11  
 12    DBMS_OUTPUT.PUT_LINE('8 + 6 / 2 = ' || TO_CHAR(8 + 6 / 2));
 13    DBMS_OUTPUT.PUT_LINE('(8 + 6) / 2 = ' || TO_CHAR((8 + 6) / 2));
 14  
 15    -- Most deeply nested subexpression is evaluated first:
 16  
 17    DBMS_OUTPUT.PUT_LINE('100 + (20 / 5 + (7 - 3)) = '
 18                         || TO_CHAR(100 + (20 / 5 + (7 - 3))));
 19  
 20    -- Parentheses, even when unnecessary, improve readability:
 21  
 22    DBMS_OUTPUT.PUT_LINE('(salary * 0.05) + (commission * 0.25) = '
 23                         || TO_CHAR((salary * 0.05) + (commission * 0.25))
 24                        );
 25  
 26    DBMS_OUTPUT.PUT_LINE('salary * 0.05 + commission * 0.25 = '
 27                         || TO_CHAR(salary * 0.05 + commission * 0.25)
 28                        );
 29  END;
 30  /
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
 
PL/SQL procedure successfully completed.
 
SQL> 

Logical Operators

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

Table 2-3 Logical Truth Table

x y x AND y x OR y NOT x

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

TRUE

NULL

NULL

TRUE

FALSE

FALSE

TRUE

FALSE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

NULL

FALSE

NULL

TRUE

NULL

TRUE

NULL

TRUE

NULL

NULL

FALSE

FALSE

NULL

NULL

NULL

NULL

NULL

NULL

NULL


Be careful to avoid unexpected results in expressions involving NULL. For more information, see Handling NULL Values in Comparisons and Conditional Statements.

As Table 2-3 and Example 2-34 show, AND returns TRUE if and only if both operands are TRUE. (Several examples use the print_boolean procedure that Example 2-34 creates.)

Example 2-34 AND Operator

SQL> CREATE OR REPLACE PROCEDURE print_boolean (
  2    name   VARCHAR2,
  3    value  BOOLEAN
  4  ) IS
  5  BEGIN
  6    IF value IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE (name || ' = NULL');
  8    ELSIF value = TRUE THEN
  9      DBMS_OUTPUT.PUT_LINE (name || ' = TRUE');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE (name || ' = FALSE');
 12    END IF;
 13  END;
 14  /
 
Procedure created.
 
SQL> DECLARE
  2  
  3    PROCEDURE print_x_and_y (
  4      x  BOOLEAN,
  5      y  BOOLEAN
  6    ) IS
  7    BEGIN
  8      print_boolean ('x', x);
  9      print_boolean ('y', y);
 10      print_boolean ('x AND y', x AND y);
 11    END;
 12  
 13  BEGIN
 14    print_x_and_y (FALSE, FALSE);
 15    print_x_and_y (TRUE, FALSE);
 16    print_x_and_y (FALSE, TRUE);
 17    print_x_and_y (TRUE, TRUE);
 18  
 19    print_x_and_y (TRUE, NULL);
 20    print_x_and_y (FALSE, NULL);
 21    print_x_and_y (NULL, TRUE);
 22    print_x_and_y (NULL, FALSE);
 23  END;
 24  /
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
 
PL/SQL procedure successfully completed.
 
SQL>

As Table 2-3 and Example 2-35 show, OR returns TRUE if either operand is TRUE. (Example 2-35 invokes the print_boolean procedure created in Example 2-34.)

Example 2-35 OR Operator

SQL> DECLARE
  2  
  3    PROCEDURE print_x_or_y (
  4      x  BOOLEAN,
  5      y  BOOLEAN
  6    ) IS
  7    BEGIN
  8      print_boolean ('x', x);
  9      print_boolean ('y', y);
 10      print_boolean ('x OR y', x OR y);
 11    END;
 12  
 13  BEGIN
 14    print_x_or_y (FALSE, FALSE);
 15    print_x_or_y (TRUE, FALSE);
 16    print_x_or_y (FALSE, TRUE);
 17    print_x_or_y (TRUE, TRUE);
 18  
 19    print_x_or_y (TRUE, NULL);
 20    print_x_or_y (FALSE, NULL);
 21    print_x_or_y (NULL, TRUE);
 22    print_x_or_y (NULL, FALSE);
 23  END;
 24  /
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
 
PL/SQL procedure successfully completed.
 
SQL> 

As Table 2-3 and Example 2-36 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-36 invokes the print_boolean procedure created in Example 2-34.)

Example 2-36 NOT Operator

SQL> DECLARE
  2  
  3    PROCEDURE print_not_x (
  4      x  BOOLEAN
  5    ) IS
  6    BEGIN
  7      print_boolean ('x', x);
  8      print_boolean ('NOT x', NOT x);
  9    END;
 10  
 11  BEGIN
 12    print_not_x (TRUE);
 13    print_not_x (FALSE);
 14    print_not_x (NULL);
 15  END;
 16  /
x = TRUE
NOT x = FALSE
x = FALSE
NOT x = TRUE
x = NULL
NOT x = NULL
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

Order of Evaluation

As with all operators, the order of evaluation for logical operators is determined by the operator precedence shown in Table 2-2, and can be changed by parentheses, as in Example 2-37. (Example 2-37 invokes the print_boolean procedure created in Example 2-34.)

Example 2-37 Changing Order of Evaluation of Logical Operators

SQL> DECLARE
  2    x  BOOLEAN := FALSE;
  3    y  BOOLEAN := FALSE;
  4  
  5  BEGIN
  6    print_boolean ('NOT x AND y', NOT x AND y);
  7    print_boolean ('NOT (x AND y)', NOT (x AND y));
  8    print_boolean ('(NOT x) AND y', (NOT x) AND y);
  9  END;
 10  /
NOT x AND y = FALSE
NOT (x AND y) = TRUE
(NOT x) AND y = FALSE
 
PL/SQL procedure successfully completed.
 
SQL>

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 the result can be determined. This lets you write expressions that might otherwise cause errors.

In Example 2-38, short-circuit evaluation prevents the expression in line 8 from causing an error.

Example 2-38 Short-Circuit Evaluation

SQL> DECLARE
  2    on_hand  INTEGER := 0;
  3    on_order INTEGER := 100;
  4  BEGIN
  5    -- Does not cause divide-by-zero error;
  6    -- evaluation stops after first expression
  7  
  8    IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
  9      DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');
 10    END IF;
 11  END;
 12  /
On hand quantity is zero.
 
PL/SQL procedure successfully completed.
 
SQL> 

When the value of on_hand is zero, the left operand yields 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.

Short-circuit evaluation applies to IF statements, CASE statements, and CASE expressions in PL/SQL.

Comparison Operators

Comparison operators compare one expression to another. The result is always either TRUE, FALSE, OR NULL. Typically, you use comparison operators in conditional control statements and in the WHERE clauses of SQL data manipulation statements.

The comparison operators are:

Note:

Using CLOB values with comparison operators can create temporary LOB values. Be sure that your temporary tablespace is large enough to handle them.

Table 2-4 Relational Operators

Operator Meaning

=

equal to

<>, !=, ~=, ^=

not equal to

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to


Example 2-39 invokes the print_boolean procedure created in Example 2-34 to print values of some expressions that include relational operators.

Example 2-39 Relational Operators

SQL> BEGIN
  2    print_boolean ('(2 + 2 =  4)', 2 + 2 = 4);
  3  
  4    print_boolean ('(2 + 2 <> 4)', 2 + 2 <> 4);
  5    print_boolean ('(2 + 2 != 4)', 2 + 2 != 4);
  6    print_boolean ('(2 + 2 ~= 4)', 2 + 2 ~= 4);
  7    print_boolean ('(2 + 2 ^= 4)', 2 + 2 ^= 4);
  8  
  9    print_boolean ('(1 < 2)', 1 < 2);
 10  
 11    print_boolean ('(1 > 2)', 1 > 2);
 12  
 13    print_boolean ('(1 <= 2)', 1 <= 2);
 14  
 15    print_boolean ('(1 >= 1)', 1 >= 1);
 16  END;
 17  /
(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
 
PL/SQL procedure successfully completed.
 
SQL>
IS NULL Operator

The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL.

To test whether a value is NULL, use IF value IS NULL, as the procedure print_boolean in Example 2-34 does at line 6.

LIKE Operator

The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.

The pattern can include the two "wildcard" characters underscore (_) and percent sign (%). Underscore matches exactly one character. Percent sign (%) matches zero or more characters.

Case is significant. The string 'Johnson' matches the pattern 'J%s_n' but not 'J%S_N', as Example 2-40 shows.

Example 2-40 LIKE Operator

SQL> DECLARE
  2  
  3    PROCEDURE compare (
  4      value   VARCHAR2,
  5      pattern VARCHAR2
  6    ) IS
  7    BEGIN
  8      IF value LIKE pattern THEN
  9        DBMS_OUTPUT.PUT_LINE ('TRUE');
 10      ELSE
 11        DBMS_OUTPUT.PUT_LINE ('FALSE');
 12      END IF;
 13    END;
 14  
 15  BEGIN
 16    compare('Johnson', 'J%s_n');
 17    compare('Johnson', 'J%S_N');
 18  END;
 19  /
TRUE
FALSE
 
PL/SQL procedure successfully completed.
 
SQL> 

To search for the percent sign or underscore, define an escape character and put it before the percent sign or underscore.

Example 2-41 uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard.

Example 2-41 Escape Character in Pattern

SQL> DECLARE
  2  
  3    PROCEDURE half_off (sale_sign VARCHAR2) IS
  4    BEGIN
  5      IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
  6        DBMS_OUTPUT.PUT_LINE ('TRUE');
  7      ELSE
  8        DBMS_OUTPUT.PUT_LINE ('FALSE');
  9      END IF;
 10    END;
 11  
 12  BEGIN
 13    half_off('Going out of business!');
 14    half_off('50% off!');
 15  END;
 16  /
FALSE
TRUE
 
PL/SQL procedure successfully completed.
 
SQL> 
BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that x >= a and x <= b.

Example 2-42 invokes the print_boolean procedure created in Example 2-34 to print values of some expressions that include the BETWEEN operator.

Example 2-42 BETWEEN Operator

SQL> BEGIN
  2    print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);
  3    print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);
  4    print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);
  5    print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);
  6  END;
  7  /
2 BETWEEN 1 AND 3 = TRUE
2 BETWEEN 2 AND 3 = TRUE
2 BETWEEN 1 AND 2 = TRUE
2 BETWEEN 3 AND 4 = FALSE
 
PL/SQL procedure successfully completed.
 
SQL> 
IN Operator

The IN operator tests set membership. x IN (set) means that x is equal to any member of set.

Example 2-43 invokes the print_boolean procedure created in Example 2-34 to print values of some expressions that include the IN operator.

Example 2-43 IN Operator

SQL> DECLARE
  2    letter VARCHAR2(1) := 'm';
  3  BEGIN
  4    print_boolean (
  5      'letter IN (''a'', ''b'', ''c'')',
  6      letter IN ('a', 'b', 'c')
  7    );
  8  
  9    print_boolean (
 10      'letter IN (''z'', ''m'', ''y'', ''p'')',
 11      letter IN ('z', 'm', 'y', 'p')
 12    );
 13  END;
 14  /
letter IN ('a', 'b', 'c') = FALSE
letter IN ('z', 'm', 'y', 'p') = TRUE
 
PL/SQL procedure successfully completed.
 
SQL> 

Example 2-44 shows what happens when set contains a NULL value. (Example 2-44 invokes the print_boolean procedure created in Example 2-34.)

Example 2-44 Using the IN Operator with Sets with NULL Values

SQL> DECLARE
  2    a INTEGER; -- Initialized to NULL by default
  3    b INTEGER := 10;
  4    c INTEGER := 100;
  5  BEGIN
  6    print_boolean ('100 IN (a, b, c)', 100 IN (a, b, c));
  7    print_boolean ('100 NOT IN (a, b, c)', 100 NOT IN (a, b, c));
  8  
  9    print_boolean ('100 IN (a, b)', 100 IN (a, b));
 10    print_boolean ('100 NOT IN (a, b)', 100 NOT IN (a, b));
 11  
 12    print_boolean ('a IN (a, b)', a IN (a, b));
 13    print_boolean ('a NOT IN (a, b)', a NOT IN (a, b));
 14  END;
 15  /
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
 
PL/SQL procedure successfully completed.
 
SQL> 

BOOLEAN Expressions

PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called BOOLEAN expressions, consist of simple or complex expressions separated by relational operators. Often, BOOLEAN expressions are connected by the logical operators AND, OR, and NOT. A BOOLEAN expression always yields TRUE, FALSE, or NULL.

In a SQL statement, BOOLEAN expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, BOOLEAN expressions are the basis for conditional control.

Topics:

BOOLEAN Arithmetic Expressions

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments:

number1 := 75;
number2 := 70;

The following expression is true:

number1 > number2

In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. For example, the following IF condition might not yield TRUE:

DECLARE
   fraction BINARY_FLOAT := 1/3;
BEGIN
   IF fraction = 11/33 THEN
      DBMS_OUTPUT.PUT_LINE('Fractions are equal (luckily!)');
   END IF;
END;
/

BOOLEAN Character Expressions

You can compare character values for equality or inequality. By default, comparisons are based on the binary values of each byte in the string. For example, given the assignments:

string1 := 'Kathy';
string2 := 'Kathleen';

The following expression is true:

string1 > string2

By setting the initialization parameter NLS_COMP=ANSI, you can make comparisons use the collating sequence identified by the NLS_SORT initialization parameter. A collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.

Depending on the value of the NLS_SORT parameter, you can perform comparisons that are case-insensitive and even accent-insensitive. A case-insensitive comparison still returns true if the letters of the operands are different in terms of uppercase and lowercase. An accent-insensitive comparison is case-insensitive, and also returns true if the operands differ in accents or punctuation characters. For example, the character values 'True' and 'TRUE' are considered identical by a case-insensitive comparison; the character values 'Cooperate', 'Co-Operate', and 'coöperate' are all considered the same. To make comparisons case-insensitive, add _CI to the end of your usual value for the NLS_SORT parameter. To make comparisons accent-insensitive, add _AI to the end of the NLS_SORT value.

There are semantic differences between the CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, see Differences Between CHAR and VARCHAR2 Data Types.

Many types can be converted to character types. For example, you can compare, assign, and do other character operations using CLOB variables. For details on the possible conversions, see PL/SQL Data Type Conversion.

BOOLEAN Date Expressions

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments:

date1 := '01-JAN-91';
date2 := '31-DEC-90';

The following expression is true:

date1 > date2

Guidelines for BOOLEAN Expressions

It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because 100 < tax yields a BOOLEAN value, which cannot be compared with the number 500:

100 < tax < 500  -- not allowed

The debugged version follows:

(100 < tax) AND (tax < 500)

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

Example 2-45 Using BOOLEAN Variables in Conditional Tests

SQL> DECLARE
  2    done BOOLEAN;
  3  BEGIN
  4    -- The following WHILE loops are equivalent
  5  
  6    done := FALSE;
  7    WHILE done = FALSE
  8    LOOP
  9      done := TRUE;
 10    END LOOP;
 11  
 12    done := FALSE;
 13    WHILE NOT (done = TRUE)
 14    LOOP
 15      done := TRUE;
 16    END LOOP;
 17  
 18    done := FALSE;
 19    WHILE NOT done
 20    LOOP
 21      done := TRUE;
 22    END LOOP;
 23  END;
 24  /
 
PL/SQL procedure successfully completed.
 
SQL> 

CASE Expressions

There are two types of expressions used in CASE statements: simple and searched. These expressions correspond to the type of CASE statement in which they are used. See Using the Simple CASE Statement.

Topics:

Simple CASE Expression

A simple CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a subprogram call. The CASE expression uses a selector, an expression whose value determines which alternative to return.

A CASE expression has the form illustrated in Example 2-46. The selector (grade) is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. If there are no matches, then the optional ELSE clause is performed.

Example 2-46 Using the WHEN Clause with a CASE Statement

SQL> DECLARE
  2    grade CHAR(1) := 'B';
  3    appraisal VARCHAR2(20);
  4  BEGIN
  5    appraisal :=
  6      CASE grade
  7        WHEN 'A' THEN 'Excellent'
  8        WHEN 'B' THEN 'Very Good'
  9        WHEN 'C' THEN 'Good'
 10        WHEN 'D' THEN 'Fair'
 11        WHEN 'F' THEN 'Poor'
 12        ELSE 'No such grade'
 13      END;
 14    DBMS_OUTPUT.PUT_LINE
 15      ('Grade ' || grade || ' is ' || appraisal);
 16  END;
 17  /
Grade B is Very Good
 
PL/SQL procedure successfully completed.
 
SQL>

The optional ELSE clause works similarly to the ELSE clause in an IF statement. If the value of the selector is not one of the choices covered by a WHEN clause, the ELSE clause is executed. If no ELSE clause is provided and none of the WHEN clauses are matched, the expression returns NULL.

Searched CASE Expression

A searched CASE expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 2-47.

A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.

Example 2-47 Using a Search Condition with a CASE Statement

SQL> DECLARE
  2    grade      CHAR(1) := 'B';
  3    appraisal  VARCHAR2(120);
  4    id         NUMBER  := 8429862;
  5    attendance NUMBER := 150;
  6    min_days   CONSTANT NUMBER := 200;
  7  
  8    FUNCTION attends_this_school (id NUMBER)
  9     RETURN BOOLEAN IS
 10    BEGIN
 11      RETURN TRUE;
 12    END;
 13  
 14  BEGIN
 15    appraisal :=
 16      CASE
 17        WHEN attends_this_school(id) = FALSE
 18          THEN 'Student not enrolled'
 19        WHEN grade = 'F' OR attendance < min_days
 20          THEN 'Poor (poor performance or bad attendance)'
 21        WHEN grade = 'A' THEN 'Excellent'
 22        WHEN grade = 'B' THEN 'Very Good'
 23        WHEN grade = 'C' THEN 'Good'
 24        WHEN grade = 'D' THEN 'Fair'
 25        ELSE 'No such grade'
 26      END;
 27    DBMS_OUTPUT.PUT_LINE
 28      ('Result for student ' || id || ' is ' || appraisal);
 29  END;
 30  /
Result for student 8429862 is Poor (poor performance or bad attendance)
 
PL/SQL procedure successfully completed.
 
SQL>

The search conditions are evaluated sequentially. The BOOLEAN value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. After any WHEN clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields TRUE, the optional ELSE clause is executed. If no WHEN clause is executed and no ELSE clause is supplied, the value of the expression is NULL.

Handling NULL Values in Comparisons and Conditional Statements

When using NULL values, remember the following rules:

  • Comparisons involving NULL values always yield NULL.

  • Applying the logical operator NOT to a NULL value yields NULL.

  • In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed.

  • If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. Instead, use a searched CASE syntax with WHEN expression IS NULL.

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

Example 2-48 NULL Value in Unequal Comparison

SQL> DECLARE
  2    x NUMBER := 5;
  3    y NUMBER := NULL;
  4  BEGIN
  5    IF x != y THEN  -- yields NULL, not TRUE
  6      DBMS_OUTPUT.PUT_LINE('x != y');  -- not executed
  7    ELSIF x = y THEN -- also yields NULL
  8      DBMS_OUTPUT.PUT_LINE('x = y');
  9    ELSE
 10      DBMS_OUTPUT.PUT_LINE
 11        ('Can''t tell if x and y are equal or not.');
 12     END IF;
 13  END;
 14  /
Can't tell if x and y are equal or not.
 
PL/SQL procedure successfully completed.
 
SQL> 

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

Example 2-49 NULL Value in Equal Comparison

SQL> DECLARE
  2    a NUMBER := NULL;
  3    b NUMBER := NULL;
  4  BEGIN
  5    IF a = b THEN  -- yields NULL, not TRUE
  6      DBMS_OUTPUT.PUT_LINE('a = b');  -- not executed
  7    ELSIF a != b THEN  -- yields NULL, not TRUE
  8      DBMS_OUTPUT.PUT_LINE('a != b');  -- not executed
  9    ELSE
 10      DBMS_OUTPUT.PUT_LINE('Can''t tell if two NULLs are equal');
 11    END IF;
 12  END;
 13  /
Can't tell if two NULLs are equal
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

NULL Values and the NOT Operator

Applying the logical operator NOT to a null yields NULL. Therefore, the following two IF statements are not always equivalent:

SQL> DECLARE
  2    x    INTEGER := 2;
  3    Y    INTEGER := 5;
  4    high INTEGER;
  5  BEGIN
  6    IF x > y THEN high := x;
  7    ELSE high := y;
  8    END IF;
  9  
 10    IF NOT x > y THEN high := y;
 11    ELSE high := x;
 12    END IF;
 13  END;
 14  /
 
PL/SQL procedure successfully completed.
 
SQL> 

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.

NULL Values and Zero-Length Strings

PL/SQL treats any zero-length string like a NULL value. This includes values returned by character functions and BOOLEAN expressions. For example, the following statements assign nulls to the target variables:

SQL> DECLARE
  2     null_string  VARCHAR2(80) := TO_CHAR('');
  3     address      VARCHAR2(80);
  4     zip_code     VARCHAR2(80) := SUBSTR(address, 25, 0);
  5     name         VARCHAR2(80);
  6     valid        BOOLEAN      := (name != '');
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Use the IS NULL operator to test for null strings, as follows:

IF v_string IS NULL THEN ...

NULL Values and the Concatenation Operator

The concatenation operator ignores null operands. For example:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce');
  3  END;
  4  /
applesauce
 
PL/SQL procedure successfully completed.
 
SQL> 

NULL Values as Arguments to Built-In Functions

If a NULL argument is passed to a built-in function, a NULL value is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be NULL. If a search is successful, the corresponding result is returned. In Example 2-50, if the column manager_id is NULL, DECODE returns the value 'nobody'.

Example 2-50 NULL Value as Argument to DECODE Function

SQL> DECLARE
  2    manager  VARCHAR2(40);
  3    name     employees.last_name%TYPE;
  4  BEGIN
  5    -- NULL is a valid argument to DECODE.
  6    -- In this case, manager_id is NULL
  7    -- and the DECODE function returns 'nobody'.
  8  
  9    SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
 10      INTO manager, name
 11        FROM employees
 12          WHERE employee_id = 100;
 13  
 14    DBMS_OUTPUT.PUT_LINE
 15      (name || ' is managed by ' || manager);
 16  END;
 17  /
King is managed by nobody
 
PL/SQL procedure successfully completed.
 
SQL>

The function NVL returns the value of its second argument if its first argument is NULL. In Example 2-51, if the column specified in the query is NULL, the function returns the value -1 to signify a nonexistent employee in the output.

Example 2-51 NULL Value as Argument to NVL Function

SQL> DECLARE
  2    manager employees.manager_id%TYPE;
  3    name    employees.last_name%TYPE;
  4  BEGIN
  5    -- NULL is a valid argument to NVL.
  6    -- In this case, manager_id is null
  7    -- and the NVL function returns -1.
  8  
  9    SELECT NVL(manager_id, -1), last_name
 10      INTO manager, name
 11        FROM employees
 12          WHERE employee_id = 100;
 13  
 14     DBMS_OUTPUT.PUT_LINE
 15       (name || ' is managed by employee Id: ' || manager);
 16  END;
 17  /
King is managed by employee Id: -1
 
PL/SQL procedure successfully completed.
 
SQL>

The function REPLACE returns the value of its first argument if its second argument is NULL, whether the optional third argument is present or not. For example, the call to REPLACE in Example 2-52 does not make any change to the value of old_string.

Example 2-52 NULL Value as Second Argument to REPLACE Function

SQL> DECLARE
  2    string_type  VARCHAR2(60);
  3    old_string   string_type%TYPE := 'Apples and oranges';
  4    v_string     string_type%TYPE := 'more apples';
  5  
  6    -- NULL is a valid argument to REPLACE,
  7    -- but does not match anything,
  8    -- so no replacement is done.
  9  
 10    new_string string_type%TYPE := REPLACE(old_string, NULL, v_string);
 11  BEGIN
 12    DBMS_OUTPUT.PUT_LINE('Old string = ' || old_string);
 13    DBMS_OUTPUT.PUT_LINE('New string = ' || new_string);
 14  END;
 15  /
Old string = Apples and oranges
New string = Apples and oranges
 
PL/SQL procedure successfully completed.
 
SQL>

If its third argument is NULL, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the call to REPLACE in Example 2-53 removes all the dashes from dashed_string, instead of changing them to another character.

Example 2-53 NULL Value as Third Argument to REPLACE Function

SQL> DECLARE
  2    string_type  VARCHAR2(60);
  3    dashed       string_type%TYPE := 'Gold-i-locks';
  4  
  5    -- When the substitution text for REPLACE is NULL,
  6    -- the text being replaced is deleted.
  7  
  8    name         string_type%TYPE := REPLACE(dashed, '-', NULL);
  9  BEGIN
 10     DBMS_OUTPUT.PUT_LINE('Dashed name    = ' || dashed);
 11     DBMS_OUTPUT.PUT_LINE('Dashes removed = ' || name);
 12  END;
 13  /
Dashed name    = Gold-i-locks
Dashes removed = Goldilocks
 
PL/SQL procedure successfully completed.
 
SQL>

If its second and third arguments are NULL, REPLACE just returns its first argument.

PL/SQL Error-Reporting Functions

PL/SQL has two built-in error-reporting functions, SQLCODE and SQLERRM, for use in PL/SQL exception-handling code. For their descriptions, see SQLCODE Function and SQLERRM Function.

You cannot use the SQLCODE and SQLERRM functions in SQL statements.

Using SQL Functions in PL/SQL

You can use all SQL functions except the following in PL/SQL expressions:

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 make up 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.

See Also:

Oracle Database SQL Language Reference for information about SQL functions

Conditional Compilation

Using conditional compilation, you can customize the functionality in a PL/SQL application without having to remove any source code. For example, using conditional compilation you can customize a PL/SQL application to:

Topics:

How Does Conditional Compilation Work?

Conditional compilation uses selection directives, inquiry directives, and error directives to specify source text for compilation. Inquiry directives access values set up through name-value pairs in the PLSQL_CCFLAGS compilation parameter, which is described in PL/SQL Units and Compilation Parameters. Selection directives can test inquiry directives or static package constants.

The DBMS_DB_VERSION package provides database version and release constants that can be used for conditional compilation. The DBMS_PREPROCESSOR package provides subprograms for accessing the post-processed source text that is selected by conditional compilation directives in a PL/SQL unit.

Note:

The conditional compilation feature and related PL/SQL packages are available for Oracle Database release 10.1.0.4 and later releases.

Topics:

Conditional Compilation Control Tokens

The conditional compilation trigger character, $, identifies code that is processed before the application is compiled. A conditional compilation control token has the form:

preprocessor_control_token ::= $plsql_identifier

The $ must be at the beginning of the identifier name and there cannot be a space between the $ and the name. The $ can also be embedded in the identifier name, but it has no special meaning. The reserved preprocessor control tokens are $IF, $THEN, $ELSE, $ELSIF, $END, and $ERROR. For an example of the use of the conditional compilation control tokens, see Example 2-56.

Using Conditional Compilation Selection Directives

The conditional compilation selection directive evaluates static expressions to determine which text to include in the compilation. The selection directive is of the form:

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

boolean_static_expression must be a BOOLEAN static expression. For a description of BOOLEAN static expressions, see Using Static Expressions with Conditional Compilation. For information about PL/SQL IF-THEN control structures, see Testing Conditions (IF and CASE Statements).

Using Conditional Compilation Error Directives

The error directive $ERROR raises a user-defined exception and is of the form:

$ERROR varchar2_static_expression $END

varchar2_static_expression must be a VARCHAR2 static expression. For a description of VARCHAR2 static expressions, see Using Static Expressions with Conditional Compilation. See Example 2-55.

Using Conditional Compilation Inquiry Directives

The inquiry directive is used to check the compilation environment. The inquiry directive is of the form:

inquiry_directive ::= $$id

An inquiry directive can be predefined as described in Using Predefined Inquiry Directives with Conditional Compilation or be user-defined. The following describes the order of the processing flow when conditional compilation attempts to resolve an inquiry directive:

  1. The id is used as an inquiry directive in the form $$id for the search key.

  2. The two-pass algorithm proceeds as follows:

    The string in the PLSQL_CCFLAGS compilation parameter is scanned from right to left, searching with id for a matching name (case-insensitive); done if found.

    The predefined inquiry directives are searched; done if found.

  3. If the $$id cannot be resolved to a value, then the PLW-6003 warning message is reported if the source text is not wrapped. The literal NULL is substituted as the value for undefined inquiry directives. If the PL/SQL code is wrapped, then the warning message is disabled so that the undefined inquiry directive is not revealed.

For example, consider the following session setting:

ALTER SESSION SET
  PLSQL_CCFLAGS = 'plsql_ccflags:true, debug:true, debug:0';

The value of $$debug is 0 and the value of $$plsql_ccflags is true. The value of $$plsql_ccflags resolves to the user-defined PLSQL_CCFLAGS inside the value of the PLSQL_CCFLAGS compiler parameter. This occurs because a user-defined directive overrides the predefined one.

Consider the following session setting:

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true'

Now the value of $$debug is true, the value of $$plsql_ccflags is 'debug:true', the value of $$my_id is the literal NULL, and the use of $$my_id raises PLW-6003 if the source text is not wrapped.

For an example of the use of an inquiry directive, see Example 2-56.

Using Predefined Inquiry Directives with Conditional Compilation

Predefined inquiry directive names, which can be used in conditional expressions, include:

  • PLSQL_LINE, a PLS_INTEGER literal whose value indicates the line number reference to $$PLSQL_LINE in the current PL/SQL unit

    An example of $$PLSQL_LINE in a conditional expression is:

    $IF $$PLSQL_LINE = 32 $THEN ...
    
  • PLSQL_UNIT, a VARCHAR2 literal whose value indicates the current PL/SQL unit

    For a named PL/SQL unit, $$PLSQL_UNIT contains, but might not be limited to, the unit name. For an anonymous block, $$PLSQL_UNIT contains the empty string.

    An example of $$PLSQL_UNIT in a conditional expression is:

    IF $$PLSQL_UNIT = 'AWARD_BONUS' THEN ...
    

    The preceding example shows the use of PLSQL_UNIT in regular PL/SQL. Because $$PLSQL_UNIT = 'AWARD_BONUS' is a VARCHAR2 comparison, not a static expression, it is not supported with $IF. One valid use of $IF with PLSQL_UNIT is to determine an anonymous block, as follows:

    $IF $$PLSQL_UNIT IS NULL $THEN ...
    
  • PL/SQL compilation parameters

The values of the literals PLSQL_LINE and PLSQL_UNIT can be defined explicitly with the compilation parameter PLSQL_CCFLAGS. For information about compilation parameters, see PL/SQL Units and Compilation Parameters.

Using Static Expressions with Conditional Compilation

Only static expressions which can be fully evaluated by the compiler are allowed during conditional compilation processing. Any expression that contains references to variables or functions that require the execution of the PL/SQL are not available during compilation and cannot be evaluated. For information about PL/SQL data types, see Predefined PL/SQL Scalar Data Types and Subtypes.

A static expression is either a BOOLEAN, PLS_INTEGER, or VARCHAR2 static expression. Static constants declared in packages are also static expressions.

Topics:

Boolean Static Expressions

BOOLEAN static expressions include:

  • TRUE, FALSE, and the literal NULL

  • Where x and y are PLS_INTEGER static expressions:

    • x > y

    • x < y

    • x >= y

    • x <= y

    • x = y

    • x <> y

  • Where x and y are PLS_INTEGER BOOLEAN expressions:

    • NOT x

    • x AND y

    • x OR y

    • x > y

    • x >= y

    • x = y

    • x <= y

    • x <> y

  • Where x is a static expression:

    • x IS NULL

    • x IS NOT NULL

PLS_INTEGER Static Expressions

PLS_INTEGER static expressions include:

  • -2147483648 to 2147483647, and the literal NULL

VARCHAR2 Static Expressions

VARCHAR2 static expressions include:

  • 'abcdef'

  • 'abc' || 'def'

  • Literal NULL

  • TO_CHAR(x), where x is a PLS_INTEGER static expression

  • TO_CHAR(x f, n) where x is a PLS_INTEGER static expression and f and n are VARCHAR2 static expressions

  • x || y where x and y are VARCHAR2 or PLS_INTEGER static expressions

Static Constants

Static constants are declared in a package specification as follows:

static_constant CONSTANT data_type := static_expression;

This is a valid declaration of a static constant if:

  • The declared data_type and the type of static_expression are the same

  • static_expression is a static expression

  • data_type is either BOOLEAN or PLS_INTEGER

The static constant must be declared in the package specification and referred to as package_name.constant_name, even in the body of the package_name package.

If a static package constant is used as the BOOLEAN expression in a valid selection directive in a PL/SQL unit, then the conditional compilation mechanism automatically places a dependency on the package referred to. If the package is altered, then the dependent unit becomes invalid and must be recompiled to pick up any changes. Only valid static expressions can create dependencies.

If you choose to use a package with static constants for controlling conditional compilation in multiple PL/SQL units, then create only the package specification and dedicate it exclusively for controlling conditional compilation because of the multiple dependencies. For control of conditional compilation in an individual unit, you can set a specific flag in the PL/SQL compilation parameter PLSQL_CCFLAGS. For information about PL/SQL compilation parameters, see PL/SQL Units and Compilation Parameters

In Example 2-54 the my_debug package defines constants for controlling debugging and tracing in multiple PL/SQL units. In the example, the constants debug and trace are used in static expressions in procedures my_proc1 and my_proc2, which places a dependency from the procedures to my_debug.

Example 2-54 Using Static Constants

SQL> CREATE PACKAGE my_debug IS
  2    debug CONSTANT BOOLEAN := TRUE;
  3    trace CONSTANT BOOLEAN := TRUE;
  4  END my_debug;
  5  /
 
Package created.
 
SQL> CREATE PROCEDURE my_proc1 IS
  2  BEGIN
  3    $IF my_debug.debug $THEN
  4      DBMS_OUTPUT.put_line('Debugging ON');
  5    $ELSE
  6      DBMS_OUTPUT.put_line('Debugging OFF');
  7    $END
  8  END my_proc1;
  9  /
 
Procedure created.
 
SQL> CREATE PROCEDURE my_proc2 IS
  2  BEGIN
  3    $IF my_debug.trace $THEN
  4      DBMS_OUTPUT.put_line('Tracing ON');
  5    $ELSE DBMS_OUTPUT.put_line('Tracing OFF');
  6    $END
  7  END my_proc2;
  8  /
 
Procedure created.
 
SQL>

Changing the value of one of the constants forces all the dependent units of the package to recompile with the new value. For example, changing the value of debug to FALSE causes my_proc1 to be recompiled without the debugging code. my_proc2 is also recompiled, but my_proc2 is unchanged because the value of trace did not change.

Using DBMS_DB_VERSION Package Constants

The DBMS_DB_VERSION package provides constants that are useful when making simple selections for conditional compilation. The PLS_INTEGER constants VERSION and RELEASE identify the current Oracle Database version and release numbers. The BOOLEAN constants VER_LE_9, VER_LE_9_1, VER_LE_9_2, VER_LE_10, VER_LE_10_1, and VER_LE_10_2 evaluate to TRUE or FALSE as follows:

  • VER_LE_v evaluates to TRUE if the database version is less than or equal to v; otherwise, it evaluates to FALSE.

  • VER_LE_v_r evaluates to TRUE if the database version is less than or equal to v and release is less than or equal to r; otherwise, it evaluates to FALSE.

  • All constants representing Oracle Database 10g release 1 or earlier are FALSE

Example 2-55 illustrates the use of a DBMS_DB_VERSION constant with conditional compilation. Both the Oracle Database version and release are checked. This example also shows the use of $ERROR.

Example 2-55 Using DBMS_DB_VERSION Constants

SQL> BEGIN
  2    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
  3      $ERROR 'unsupported database release'
  4    $END
  5    $ELSE
  6      DBMS_OUTPUT.PUT_LINE
  7        ('Release ' || DBMS_DB_VERSION.VERSION || '.' ||
  8         DBMS_DB_VERSION.RELEASE || ' is supported.');
  9  
 10    -- This COMMIT syntax is newly supported in 10.2:
 11    COMMIT WRITE IMMEDIATE NOWAIT;
 12  $END
 13  END;
 14  /
Release 11.1 is supported.
 
PL/SQL procedure successfully completed.
 
SQL>

For information about the DBMS_DB_VERSION package, see Oracle Database PL/SQL Packages and Types Reference.

Conditional Compilation Examples

This section provides examples using conditional compilation.

Topics:

Using Conditional Compilation to Specify Code for Database Versions

Example 2-56 uses conditional compilation to determine whether the BINARY_DOUBLE data type can be used in the calculations for PL/SQL units in the database. The BINARY_DOUBLE data type can only be used in a database version that is 10g or later.

Example 2-56 Using Conditional Compilation with Database Versions

SQL> -- Set flags for displaying debugging code and tracing info:
SQL> 
SQL> ALTER SESSION SET PLSQL_CCFLAGS =
  2    'my_debug:FALSE, my_tracing:FALSE';
 
Session altered.
 
SQL> 
SQL> CREATE OR REPLACE PACKAGE my_pkg AS
  2    SUBTYPE my_real IS
  3      $IF DBMS_DB_VERSION.VERSION < 10 $THEN
  4        NUMBER;
  5        -- Check database version
  6      $ELSE
  7        BINARY_DOUBLE;
  8      $END
  9  
 10      my_pi my_real;
 11      my_e my_real;
 12  END my_pkg;
 13  /
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg AS
  2  BEGIN
  3    -- Set values for future calculations based on DB version
  4  
  5    $IF DBMS_DB_VERSION.VERSION < 10 $THEN
  6      my_pi := 3.14159265358979323846264338327950288420;
  7      my_e  := 2.71828182845904523536028747135266249775;
  8    $ELSE
  9      my_pi := 3.14159265358979323846264338327950288420d;
 10      my_e  := 2.71828182845904523536028747135266249775d;
 11    $END
 12  END my_pkg;
 13  /
 
Package body created.
 
SQL> CREATE OR REPLACE PROCEDURE circle_area(radius my_pkg.my_real) IS
  2    my_area      my_pkg.my_real;
  3    my_data_type  VARCHAR2(30);
  4  BEGIN
  5    my_area := my_pkg.my_pi * radius;
  6  
  7    DBMS_OUTPUT.PUT_LINE
  8      ('Radius: ' || TO_CHAR(radius) || ' Area: ' || TO_CHAR(my_area));
  9  
 10    $IF $$my_debug $THEN
 11      -- If my_debug is TRUE, run debugging code
 12      SELECT DATA_TYPE INTO my_data_type
 13        FROM USER_ARGUMENTS
 14          WHERE OBJECT_NAME = 'CIRCLE_AREA'
 15            AND ARGUMENT_NAME = 'RADIUS';
 16  
 17       DBMS_OUTPUT.PUT_LINE
 18         ('Data type of the RADIUS argument is: ' || my_data_type);
 19    $END
 20  END;
 21  /
 
Procedure created.
 
SQL>

Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text

DBMS_PREPROCESSOR subprograms print or retrieve the post-processed source text of a PL/SQL unit after processing the conditional compilation directives. This post-processed text is the actual source used to compile a valid PL/SQL unit. Example 2-57 shows how to print the post-processed form of my_pkg in Example 2-56 with the PRINT_POST_PROCESSED_SOURCE procedure.

Example 2-57 Using PRINT_POST_PROCESSED_SOURCE to Display Source Code

SQL> CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
  2    ('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;
 
Call completed.
 
SQL> 

PRINT_POST_PROCESSED_SOURCE replaces unselected text with whitespace. The lines of code in Example 2-56 that are not included in the post-processed text are represented as blank lines. For information about the DBMS_PREPROCESSOR package, see Oracle Database PL/SQL Packages and Types Reference.

Conditional Compilation Restrictions

A conditional compilation directive cannot be used in the specification of an object type or in the specification of a schema-level nested table or varray. The attribute structure of dependent types and the column structure of dependent tables is determined by the attribute structure specified in object type specifications. Any changes to the attribute structure of an object type must be done in a controlled manner to propagate the changes to dependent objects. The mechanism for propagating changes is the SQL ALTER TYPE ATTRIBUTE statement. Use of a preprocessor directive allows changes to the attribute structure of the object type without the use of an ALTER TYPE ATTRIBUTE statement. As a consequence, dependent objects can "go out of sync" or dependent tables can become inaccessible.

The SQL parser imposes restrictions on the placement of directives when performing SQL operations such as the CREATE OR REPLACE statement or the execution of an anonymous block. When performing these SQL operations, the SQL parser imposes a restriction on the location of the first conditional compilation directive as follows:

  • A conditional compilation directive cannot be used in the specification of an object type or in the specification of a schema-level nested table or varray.

  • 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 may occur immediately after the keyword IS or AS.

  • In a schema-level subprogram with at least one formal parameter, the first conditional compilation directive may occur immediately after the opening parenthesis that follows the unit's name. For example:

    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 may occur immediately after the keyword BEGIN or immediately after the keyword DECLARE when the trigger block has a DECLARE section.

  • If an anonymous block uses a placeholder, then this cannot occur within a conditional compilation directive. For example:

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

Using PL/SQL to Create Web Applications

With PL/SQL, you can create applications that generate Web pages directly from the database, allowing you to make your database available on the Web and make back-office data accessible on the intranet.

The program flow of a PL/SQL Web application is similar to that in a CGI PERL script. Developers often use CGI scripts to produce Web pages dynamically, but such scripts are often not optimal for accessing the database. Delivering Web content with PL/SQL stored subprograms provides the power and flexibility of database processing. For example, you can use DML, dynamic SQL, and cursors. You also eliminate the process overhead of forking a new CGI process to handle each HTTP request.

You can implement a Web browser-based application entirely in PL/SQL with PL/SQL Gateway and the PL/SQL Web Toolkit.

PL/SQL gateway enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener. mod_plsql, one implementation of the PL/SQL gateway, is a plug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL stored subprograms.

PL/SQL Web Toolkit is a set of PL/SQL packages that provides a generic interface to use stored subprograms invoked by mod_plsql at run time.

See Also:

Oracle Database Advanced Application Developer's Guide for information about creating web applications

Using PL/SQL to Create Server Pages

PL/SQL Server Pages (PSPs) enable you to develop Web pages with dynamic content. They are an alternative to coding a stored subprogram that writes out the HTML code for a web page, one line at a time.

Using special tags, you can embed PL/SQL scripts into HTML source code. The scripts are executed when the pages are requested by Web clients such as browsers. A script can accept parameters, query or update the database, then display a customized page showing the results.

During development, PSPs can act like templates with a static part for page layout and a dynamic part for content. You can design the layouts using your favorite HTML authoring tools, leaving placeholders for the dynamic content. Then, you can write the PL/SQL scripts that generate the content. When finished, you simply load the resulting PSP files into the database as stored subprograms.

See Also:

Oracle Database Advanced Application Developer's Guide for information about creating web server pages