Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
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
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

2 Fundamentals of the PL/SQL Language

There are six essentials in painting. The first is called spirit; the second, rhythm; the third, thought; the fourth, scenery; the fifth, the brush; and the last is the ink. —Ching Hao

The previous chapter provided an overview of PL/SQL. This chapter focuses on the detailed aspects of the language. Like other programming languages, PL/SQL has a character set, reserved words, punctuation, datatypes, and fixed syntax rules.

This chapter contains these topics:

Character Set

You write a PL/SQL program as lines of text using a specific set of characters:

Upper- and lower-case letters A .. Z and a .. z
Numerals 0 .. 9
Symbols ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]
Tabs, spaces, and carriage returns

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

Lexical Units

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

delimiters (simple and compound symbols)
identifiers, which include reserved words
literals
comments

To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation. The following line is not allowed because the reserved words END and IF are joined:

IF x > y THEN high := x; ENDIF;  -- not allowed, must be END IF

You cannot embed spaces inside lexical units except for string literals and comments. For example, the following line is not allowed because the compound symbol for assignment (:=) is split:

count : = count + 1;  -- not allowed, must be :=

To show structure, you can split lines using carriage returns, and indent lines using spaces or tabs. The formatting makes the IF statement on the right more readable:

IF x>y THEN max:=x;ELSE max:=y;END IF;     |     IF x > y THEN
                                           |        max := x;
                                           |     ELSE
                                           |        max := y;
                                           |     END IF;

Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction.

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

Symbol Meaning
:= 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. Some examples of identifiers follow:

X
t2
phone#
credit_limit
LastName
oracle$number

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are not allowed, as the following examples show:

mine&yours    -- not allowed because of ampersand
debit-amount  -- not allowed because of hyphen
on/off        -- not allowed because of slash
user id       -- not allowed because of space

Adjoining and trailing dollar signs, underscores, and number signs are allowed:

money$$$tree
SN## 
try_again_

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers them the same:

lastname
LastName  -- same as lastname
LASTNAME  -- same as lastname and LastName

The size of an identifier cannot exceed 30 characters. Every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:

lastname
last_name

Identifiers should be descriptive. Avoid obscure names such as cpm. Instead, use meaningful names such as cost_per_thousand.

Reserved Words

Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL. For example, the words BEGIN and END are reserved. Trying to redefine a reserved word causes a compilation error. Instead, you can embed reserved words as part of a longer identifier:

DECLARE
   -- end BOOLEAN;          -- not allowed; causes compilation error
   end_of_game BOOLEAN;  -- allowed
BEGIN
   NULL;
END;
/

Often, reserved words are written in upper case for readability. For a list of reserved words, see Appendix F.

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.

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. Some examples follow:

030   6   -14   0   +32767

A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:

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.

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. A few examples follow:

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

E stands for "times ten to the power of." 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 last example, the implicit decimal point shifted three places to the right. In this example, it shifts three places to the left:

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

As the following example shows, if the value of a numeric literal falls outside the range 1E-130 .. 10E125, you get a compilation error:

DECLARE
   n NUMBER;
BEGIN
   n := 10E127;  -- causes a 'numeric overflow or underflow' error
END;
/

Real literals can also use the trailing letters f and d to specify the types BINARY_FLOAT and BINARY_DOUBLE, respectively:

DECLARE
   x BINARY_FLOAT := sqrt(2.0f); -- Single-precision floating-point number
   y BINARY_DOUBLE := sqrt(2.0d); -- Double-precision floating-point number
BEGIN
   NULL;
END;
/

Character Literals

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

'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. Several examples follow:

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

All string literals except the null string ('') have datatype CHAR.

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.'

Doubling the quotation marks within a complicated literal, particularly one that represents a SQL statement, can be tricky. 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 do not need to escape other single quotation marks inside the literal:

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

-- To use delimiters [, {, <, and (, pair them with ], }, >, and ).
-- Here we pass a string literal representing a SQL statement
-- to a subprogram, without doubling the quotation marks around
-- 'INVALID'.
func_call(q'[select index_name from user_indexes where status = 'INVALID']');

-- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.
where_clause := nq'#where col_value like '%é'#';

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

'baker'
'Baker'

Boolean Literals

Boolean literals are the predefined values TRUE, FALSE, and NULL (which 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.

Datetime Literals

Datetime literals have various formats depending on the datatype. For example:

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

You can also specify whether a given interval value is YEAR TO MONTH or DAY TO SECOND. For example, current_timestamp - current_timestamp produces a value of type INTERVAL DAY TO SECOND by default. You can specify the type of the interval using the formats:

  • (interval_expression) DAY TO SECOND

  • (interval_expression) YEAR TO MONTH

For details on the syntax for the date and time types, see the Oracle Database SQL Reference. For examples of performing date/time arithmetic, see Oracle Database Application Developer's Guide - Fundamentals.

Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.

Single-Line Comments

Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. A few examples follow:

DECLARE
   howmany NUMBER;
BEGIN
-- begin processing
   SELECT count(*) INTO howmany FROM user_objects
      WHERE object_type = 'TABLE'; -- Check number of tables
   howmany := howmany * 2;         -- Compute some other value
END;
/

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- DELETE FROM employees WHERE comm_pct IS NULL;

Multi-line Comments

Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. Some examples follow:

DECLARE
   some_condition BOOLEAN;
   pi NUMBER := 3.1415926; radius NUMBER := 15; area NUMBER;
BEGIN
   /* Perform some simple tests and assignments */
   IF 2 + 2 = 4 THEN
      some_condition := TRUE; /* We expect this THEN to always be done */
   END IF;
/*
   The following line computes the area of a
   circle using pi, which is the ratio between
   the circumference and diameter.
*/
   area := pi * radius**2;
END;
/

You can use multi-line comment delimiters to comment-out whole sections of code:

/*
LOOP
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   ...
END LOOP;
*/

Restrictions on Comments

You cannot nest comments.

You cannot use single-line comments in a PL/SQL block that will be processed by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. In this case, use the /* */ notation instead.

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 datatype, and name the storage location so that you can reference it.

A couple of examples follow:

DECLARE
   birthday  DATE;
   emp_count SMALLINT := 0;

The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable.

The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

DECLARE
   pi     REAL := 3.14159;
   radius REAL := 1;
   area   REAL := pi * radius**2;
BEGIN
   NULL;
END;
/

By default, variables are initialized to NULL, so it is redundant to include ":= NULL" in a variable declaration.

To declare a constant, put the keyword CONSTANT before the type specifier:

DECLARE
   credit_limit CONSTANT REAL := 5000.00;
   max_days_in_year CONSTANT INTEGER := 366;
   urban_legend CONSTANT BOOLEAN := FALSE;
BEGIN
   NULL;
END;
/

This 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. Otherwise, you get a compilation error.

Using DEFAULT

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. For example, the declaration

blood_type CHAR := 'O';

can be rewritten as follows:

blood_type CHAR DEFAULT 'O';

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. For example:

hours_worked   INTEGER DEFAULT 40;
employee_count INTEGER := 0;

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Using NOT NULL

Besides assigning an initial value, declarations can impose the NOT NULL constraint:

DECLARE
   acct_id INTEGER(4) NOT NULL := 9999;

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.

The NOT NULL constraint must be followed by an initialization clause.

PL/SQL provide subtypes NATURALN and POSITIVEN that are predefined as NOT NULL. You can omit the NOT NULL constraint when declaring variables of these types, and you must include an initialization clause.

Using the %TYPE Attribute

The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:

DECLARE
   credit NUMBER(7,2);
   debit  credit%TYPE;
   name VARCHAR2(20) := 'JoHn SmItH';
-- If we increase the length of NAME, the other variables
-- become longer too.
   upper_name name%TYPE := UPPER(name);
   lower_name name%TYPE := LOWER(name);
   init_name name%TYPE := INITCAP(name);
BEGIN
   NULL;
END;
/

Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable. A %TYPE declaration can also include an initialization clause.

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in

DECLARE
-- If the length of the column ever changes, this code
-- will use the new length automatically.
   the_trigger user_triggers.trigger_name%TYPE;
BEGIN
   NULL;
END;
/

When you use table_name.column_name.TYPE to declare a variable, you do not need to know the actual datatype, and attributes such as precision, scale, and length. If the database definition of the column changes, the datatype of the variable changes accordingly at run time.

%TYPE variables do not inherit the NOT NULL column constraint. In the next example, even though the database column employee_id is defined as NOT NULL, you can assign a null to the variable my_empno:

DECLARE
   my_empno employees.employee_id%TYPE;
BEGIN
   my_empno := NULL;  -- this works
END;
/

Using the %ROWTYPE Attribute

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable:

DECLARE
-- %ROWTYPE can include all the columns in a table...
   emp_rec employees%ROWTYPE;
-- ...or a subset of the columns, based on a cursor.
   CURSOR c1 IS
      SELECT department_id, department_name FROM departments;
   dept_rec c1%ROWTYPE;
-- Could even make a %ROWTYPE with columns from multiple tables.
   CURSOR c2 IS
      SELECT employee_id, email, employees.manager_id, location_id
      FROM employees, departments
      WHERE employees.department_id = departments.department_id;
   join_rec c2%ROWTYPE;
BEGIN
-- We know EMP_REC can hold a row from the EMPLOYEES table.
   SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
-- We can refer to the fields of EMP_REC using column names
-- from the EMPLOYEES table.
   IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
      emp_rec.salary := emp_rec.salary * 1.15;
   END IF;
END;
/

Columns in a row and corresponding fields in a record have the same names and datatypes. However, fields in a %ROWTYPE record do not inherit the NOT NULL column constraint.

Aggregate Assignment

Although a %ROWTYPE declaration cannot include an initialization clause, there are ways to assign values to all fields in a record at once. You can assign one record to another if their declarations refer to the same table or cursor. For example, the following assignment is allowed:

DECLARE
   dept_rec1 departments%ROWTYPE;
   dept_rec2 departments%ROWTYPE;
   CURSOR c1 IS SELECT department_id, location_id FROM departments;
   dept_rec3 c1%ROWTYPE;
BEGIN
   dept_rec1 := dept_rec2;  -- allowed
-- dept_rec2 refers to a table, dept_rec3 refers to a cursor
-- dept_rec2 := dept_rec3;  -- not allowed
END;
/

You can assign a list of column values to a record by using the SELECT or FETCH statement, as the following example shows. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement.

DECLARE
   dept_rec departments%ROWTYPE;
BEGIN
   SELECT * INTO dept_rec FROM departments
      WHERE department_id = 30 and ROWNUM < 2;
END;
/

However, 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.

Using Aliases

Select-list items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases. The following example uses an alias called complete_name to represent the concatenation of two columns:

BEGIN
-- We assign an alias (COMPLETE_NAME) to the expression value, because
-- it has no column name.
   FOR item IN
   (
      SELECT first_name || ' ' || last_name complete_name
      FROM employees WHERE ROWNUM < 11
   )
   LOOP
-- Now we can refer to the field in the record using this alias.
      dbms_output.put_line('Employee name: ' || item.complete_name);
   END LOOP;
END;
/

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 "Declaring Nested PL/SQL Subprograms".

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. You must declare each variable separately:

DECLARE
-- Multiple declarations not allowed.
-- i, j, k, l SMALLINT;
-- Instead, declare each separately.
   i SMALLINT;
   j SMALLINT;
-- To save space, you can declare more than one on a line.
   k SMALLINT; l SMALLINT;
BEGIN
   NULL;
END;
/

PL/SQL Naming Conventions

The same naming conventions apply to all PL/SQL program items and units including constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example, you might use the procedure name raise_salary in any of the following ways:

raise_salary(...);                      -- simple
emp_actions.raise_salary(...);          -- qualified
raise_salary@newyork(...);              -- remote
emp_actions.raise_salary@newyork(...);  -- qualified and remote

In the first case, you simply use the procedure name. In the second case, you must qualify the name using dot notation because the procedure is stored in a package called emp_actions. In the third case, using the remote access indicator (@), you reference the database link newyork because the procedure is stored in a remote database. In the fourth case, you qualify the procedure name and reference a database link.


Synonyms

You can create synonyms to provide location transparency for remote schema objects such as tables, sequences, views, standalone subprograms, packages, and object types. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged subprograms.


Scoping

Within the same scope, all declared identifiers must be unique; even if their datatypes differ, variables and parameters cannot share the same name. In the following example, the second declaration is not allowed:

DECLARE
   valid_id BOOLEAN;
   valid_id VARCHAR2(5);  -- not allowed, duplicate identifier
BEGIN
-- The error occurs when the identifier is referenced, not
-- in the declaration part.
   valid_id := FALSE;
END;
/

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. For instance, PL/SQL considers the following names to be the same:

DECLARE
   zip_code INTEGER;
   Zip_Code INTEGER;  -- duplicate identifier, despite Z/z case difference
BEGIN
   zip_code := 90120; -- causes error because of duplicate identifiers
END;
/

Name Resolution

In potentially 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 both used in a WHERE clause, SQL considers that both cases refer to the column.

To avoid ambiguity, add a prefix to the names of local variables and formal parameters, or use a block label to qualify references.

CREATE TABLE employees2 AS SELECT last_name FROM employees;

<<MAIN>>
DECLARE
   last_name VARCHAR2(10) := 'King';
   my_last_name VARCHAR2(10) := 'King';
BEGIN
-- Deletes everyone, because both LAST_NAMEs refer to the column
   DELETE FROM employees2 WHERE last_name = last_name;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
   ROLLBACK;
-- OK, column and variable have different names
   DELETE FROM employees2 WHERE last_name = my_last_name;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
   ROLLBACK;
-- OK, block name specifies that 2nd LAST_NAME is a variable
   DELETE FROM employees2 WHERE last_name = main.last_name;
   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows.');
   ROLLBACK;
END;
/

DROP TABLE employees2;

The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:

DECLARE
   FUNCTION dept_name (department_id IN NUMBER)
      RETURN departments.department_name%TYPE
   IS
      department_name departments.department_name%TYPE;
   BEGIN
-- DEPT_NAME.DEPARTMENT_NAME specifies the local variable
-- instead of the table column
      SELECT department_name INTO dept_name.department_name
         FROM departments
         WHERE department_id = dept_name.department_id;
      RETURN department_name;
   END;

BEGIN
   FOR item IN (SELECT department_id FROM departments)
   LOOP
      dbms_output.put_line('Department: ' || dept_name(item.department_id));
   END LOOP;
END;
/

For a full discussion of name resolution, see Appendix D.

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 that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in the regions from which you can reference the identifier using an unqualified name. Figure 2-1 shows the scope and visibility of a variable named x, which is declared in an enclosing block, then redeclared in a sub-block.

Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.

Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two items represented by the identifier are distinct, and any change in one does not affect the other. However, a block cannot reference identifiers declared in other blocks at the same level because those identifiers are neither local nor global to the block.

Figure 2-1 Scope and Visibility

Description of lnpls007.gif follows
Description of the illustration lnpls007.gif

The example below illustrates the scope rules. Notice that the identifiers declared in one sub-block cannot be referenced in the other sub-block. That is because a block cannot reference identifiers declared in other blocks nested at the same level.

DECLARE
   a CHAR;
   b REAL;
BEGIN
   -- identifiers available here: a (CHAR), b
   DECLARE
      a INTEGER;
      c REAL;
   BEGIN
      -- identifiers available here: a (INTEGER), b, c
   END;
   DECLARE
      d REAL;
   BEGIN
      -- identifiers available here: a (CHAR), b, d
   END;
   -- identifiers available here: a (CHAR), b
END;
/

Recall that global identifiers can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a qualified name. The qualifier can be the label of an enclosing block:

<<outer>>
DECLARE
   birthdate DATE;
BEGIN
   DECLARE
      birthdate DATE;
   BEGIN
      ...
      IF birthdate = outer.birthdate THEN ...
   END;
   ...
END;
/

As the next example shows, the qualifier can also be the name of an enclosing subprogram:

PROCEDURE check_credit (...) IS
   rating NUMBER;
   FUNCTION valid (...) RETURN BOOLEAN IS
      rating NUMBER;
   BEGIN
      ...
      IF check_credit.rating < 3 THEN ...
   END;
BEGIN
   ...
END;
/

However, within the same scope, a label and a subprogram cannot have the same name.

Assigning Values to Variables

You can use assignment statements to assign values to variables. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:

bonus := salary * 0.15;

Unless you expressly initialize a variable, its value is undefined (NULL).Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL:

DECLARE
   counter INTEGER;
BEGIN
-- COUNTER is initially NULL, so 'COUNTER + 1' is also null.
   counter := counter + 1;
   IF counter IS NULL THEN
      dbms_output.put_line('Sure enough, COUNTER is NULL not 1.');
   END IF;
END;
/

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

The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.

Assigning Boolean Values

Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. You can assign these literal values, or expressions such as comparisons using relational operators.

DECLARE
   done BOOLEAN; -- DONE is initially NULL
   counter NUMBER := 0;
BEGIN
   done := FALSE; -- Assign a literal value
   WHILE done != TRUE -- Compare to a literal value
   LOOP
      counter := counter + 1;
      done := (counter > 500); -- If counter > 500, DONE = TRUE
   END LOOP;
END;
/

Assigning a SQL Query Result to a PL/SQL Variable

You can use the SELECT statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the INTO list. For example:

DECLARE
   emp_id   employees.employee_id%TYPE := 100;
   emp_name employees.last_name%TYPE;
   wages    NUMBER(7,2);
BEGIN
   SELECT last_name, salary + (salary * nvl(commission_pct,0))
      INTO emp_name, wages FROM employees
      WHERE employee_id = emp_id;
   dbms_output.put_line('Employee ' || emp_name || ' might make ' || wages);
END;
/

Because SQL does not have a Boolean type, you cannot select column values into a Boolean variable.

PL/SQL Expressions and Comparisons

Expressions are constructed using operands and operators. An operand is a variable, constant, literal, or function call that contributes a value to an expression. An example of a simple arithmetic expression follows:

-X / 2 + 3

Unary operators such as the negation operator (-) operate on one operand; binary operators such as the division operator (/) operate on two operands. PL/SQL has no ternary operators.

The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates an expression by combining the values of the operands in ways specified by the operators. An expression always returns a single value. PL/SQL determines the datatype of this value by examining the expression and the context in which it appears.


Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Table 2-1 shows the default order of operations from first to last (top to bottom).

Table 2-1 Order of Operations

Operator Operation
**
exponentiation
+, - identity, negation
*, / multiplication, division
+, -, || addition, subtraction, concatenation
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN comparison
NOT logical negation
AND conjunction
OR inclusion

Operators with higher precedence are applied first. In the example below, both expressions yield 8 because division has a higher precedence than addition. Operators with the same precedence are applied in no particular order.

5 + 12 / 4
12 / 4 + 5

You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence:

(8 + 6) / 2

In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:

100 + (20 / 5 + (7 - 3))

The following example shows that you can always use parentheses to improve readability, even when they are not needed:

(salary * 0.05) + (commission * 0.25)

Logical Operators

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

Table 2-2 Logic 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

As the truth table shows, AND returns TRUE only if both its operands are true. On the other hand, OR returns TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.

NOT NULL returns NULL, because nulls are indeterminate. Be careful to avoid unexpected results in expressions involving nulls; see "Handling Null Values in Comparisons and Conditional Statements".


Order of Evaluation

When you do not use parentheses to specify the order of evaluation, operator precedence determines the order. Compare the following expressions:

NOT (valid AND done)     |     NOT valid AND done

If the Boolean variables valid and done have the value FALSE, the first expression yields TRUE. However, the second expression yields FALSE because NOT has a higher precedence than AND. Therefore, the second expression is equivalent to:

(NOT valid) AND done

In the following example, notice that when valid has the value FALSE, the whole expression yields FALSE regardless of the value of done:

valid AND done

Likewise, in the next example, when valid has the value TRUE, the whole expression yields TRUE regardless of the value of done:

valid OR done

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 an error. Consider the following OR expression:

DECLARE
   on_hand  INTEGER := 0;
   on_order INTEGER := 100;
BEGIN
-- Does not cause divide-by-zero error; evaluation stops after 1st expr.
   IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
      dbms_output.put_line('There are no more widgets left!');
   END IF;
END;
/

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.


Comparison Operators

Comparison operators compare one expression to another. The result is always true, false, or null. Typically, you use comparison operators in conditional control statements and in the WHERE clause of SQL data manipulation statements. Here are some examples of comparisons for different types:

DECLARE
   PROCEDURE assert(assertion VARCHAR2, truth BOOLEAN)
   IS
   BEGIN
      IF truth IS NULL THEN
         dbms_output.put_line('Assertion ' || assertion || ' is unknown (NULL)');
      ELSIF truth = TRUE THEN
         dbms_output.put_line('Assertion ' || assertion || ' is TRUE');
      ELSE
         dbms_output.put_line('Assertion ' || assertion || ' is FALSE');
      END IF;
   END;
BEGIN
   assert('2 + 2 = 4', 2 + 2 = 4);
   assert('10 > 1', 10 > 1);
   assert('10 <= 1', 10 <= 1);
   assert('5 BETWEEN 1 AND 10', 5 BETWEEN 1 AND 10);
   assert('NULL != 0', NULL != 0);
   assert('3 IN (1,3,5)', 3 IN (1,3,5));
   assert('''A'' < ''Z''', 'A' < 'Z');
   assert('''baseball'' LIKE ''%all%''', 'baseball' LIKE '%all%');
   assert('''suit'' || ''case'' = ''suitcase''', 'suit' || 'case' = 'suitcase');
END;
/

Relational Operators
Operator Meaning
=
equal to
<>, !=, ~=, ^= not equal to
<
less than
> greater than
<=
less than or equal to
>= greater than or equal to


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 nulls always yield NULL. Test whether a value is null as follows:

IF variable IS NULL THEN ...

LIKE Operator

You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.

The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression is true:

ename LIKE 'J%S_N'

To search for the percent sign and underscore characters, you define an escape character and put that character before the percent sign or underscore. The following example uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard:

IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN...


BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression is false:

45 BETWEEN 38 AND 44

IN Operator

The IN operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. For example, the following expression tests whether a value is part of a set of values:

letter IN ('a','b','c')

Be careful when inverting this condition. Expressions of the form:

value NOT IN set

yield FALSE if the set contains a null.


Concatenation Operator

Double vertical bars (||) serve as the concatenation operator, which appends one string (CHAR, VARCHAR2, CLOB, or the equivalent Unicode-enabled type) to another. For example, the expression

'suit' || 'case'

returns the following value:

'suitcase'

If both operands have datatype CHAR, the concatenation operator returns a CHAR value. If either operand is a CLOB value, the operator returns a temporary CLOB. Otherwise, it returns a VARCHAR2 value.

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. There are three kinds of Boolean expressions: arithmetic, character, and date.


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

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 Appendix B.

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 Character and String Types".


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 PL/SQL Boolean Expressions
  • 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;
    /
    
  • 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)
    
    
  • A Boolean variable is itself either true or false. You can just use the variable in a conditional test, rather than comparing it to the literal values TRUE and FALSE. For example, the following loops are all equivalent:

    DECLARE
       done BOOLEAN ;
    BEGIN
    -- Each WHILE loop is equivalent
       done := FALSE;
       WHILE done = FALSE
       LOOP
          done := TRUE;
       END LOOP;
    
       done := FALSE;
       WHILE NOT (done = TRUE)
       LOOP
          done := TRUE;
       END LOOP;
    
       done := FALSE;
       WHILE NOT done
       LOOP
          done := TRUE;
       END LOOP;
    END;
    /
    
  • Using CLOB values with comparison operators, or functions such as LIKE and BETWEEN, can create temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs.

CASE Expressions

A 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 procedure call.

The CASE expression uses a selector, an expression whose value determines which alternative to return. A CASE expression has the following form:

CASE selector
   WHEN expression1 THEN result1
   WHEN expression2 THEN result2
   ...
   WHEN expressionN THEN resultN
  [ELSE resultN+1]
END

The selector 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. For example:

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

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.

An alternative to the CASE expression is the CASE statement, where each WHEN clause can be an entire PL/SQL block. For details, see "Using the CASE Statement".

Searched CASE Expression

PL/SQL also provides a searched CASE expression, which lets you test different conditions instead of comparing a single expression to various values. It has the form:

CASE
   WHEN search_condition1 THEN result1
   WHEN search_condition2 THEN result2
   ...
   WHEN search_conditionN THEN resultN
  [ELSE resultN+1]
END;

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. For example:

DECLARE
   grade CHAR(1) := 'B';
   appraisal VARCHAR2(120);
   id NUMBER := 8429862;
   attendance NUMBER := 150;
   min_days CONSTANT NUMBER := 200;
FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS
   BEGIN RETURN TRUE; END;
BEGIN
   appraisal :=
      CASE
         WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
-- Have to put this condition early to detect
-- good students with bad attendance
         WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)'
         WHEN grade = 'A' THEN 'Excellent'
         WHEN grade = 'B' THEN 'Very Good'
         WHEN grade = 'C' THEN 'Good'
         WHEN grade = 'D' THEN 'Fair'
         ELSE 'No such grade'
      END;
   dbms_output.put_line('Result for student ' || id ||
      ' is ' || appraisal);
END;
/

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 working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

  • Comparisons involving nulls always yield NULL

  • Applying the logical operator NOT to a null 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. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls 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.

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

In the next example, 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.

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

NULLs and the NOT Operator

Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN     |     IF NOT x > y THEN
   high := x;     |        high := y;
ELSE              |     ELSE
   high := y;     |        high := x;
END IF;           |     END IF;

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.


NULLs and Zero-Length Strings

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

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

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

IF my_string IS NULL THEN ...

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

'apple' || NULL || NULL || 'sauce'

returns the following value:

'applesauce'

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null 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 the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
   the_manager VARCHAR2(40);
   name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to DECODE. In this case, manager_id is null
-- and the DECODE function returns 'nobody'.
   SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
      INTO the_manager, name FROM employees WHERE employee_id = 100;
   dbms_output.put_line(name || ' is managed by ' || the_manager);
END;
/

The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
   the_manager employees.manager_id%TYPE;
   name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to NVL. In this case, manager_id is null
-- and the NVL function returns -1.
   SELECT NVL(manager_id, -1), last_name
      INTO the_manager, name FROM employees WHERE employee_id = 100;
   dbms_output.put_line(name || ' is managed by employee #' || the_manager);
END;
/

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 following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
   string_type VARCHAR2(60);
   old_string string_type%TYPE := 'Apples and oranges';
   my_string  string_type%TYPE := 'more apples';
-- NULL is a valid argument to REPLACE, but does not match
-- anything so no replacement is done.
   new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
   dbms_output.put_line('Old string = ' || old_string);
   dbms_output.put_line('New string = ' || new_string);
END;
/

If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
   string_type VARCHAR2(60);
   dashed string_type%TYPE := 'Gold-i-locks';
-- When the substitution text for REPLACE is NULL,
-- the text being replaced is deleted.
   name   string_type%TYPE := REPLACE(dashed, '-', NULL);
BEGIN
   dbms_output.put_line('Dashed name    = ' || dashed);
   dbms_output.put_line('Dashes removed = ' || name);
END;
/

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

Summary of PL/SQL Built-In Functions

PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories:

error reporting
number
character
datatype conversion
date
object reference
miscellaneous

Table 2-3 shows the functions in each category. For descriptions of the error-reporting functions, see Chapter 13. For descriptions of the other functions, see Oracle Database SQL Reference.

Except for the error-reporting functions SQLCODE and SQLERRM, you can use all the functions in SQL statements. Also, except for the object-reference functions DEREF, REF, and VALUE and the miscellaneous functions DECODE, DUMP, and VSIZE, you can use all the functions in procedural statements.

Although the SQL aggregate functions (such as AVG and COUNT) and the SQL analytic functions (such as CORR and LAG) are not built into PL/SQL, you can use them in SQL statements (but not in procedural statements).

Table 2-3 Built-In Functions

Error Number Character Conversion Date Obj Ref Misc
SQLCODE

SQLERRM

ABS

ACOS

ASIN

ATAN

ATAN2

BITAND

CEIL

COS

COSH

EXP

FLOOR

LN

LOG

MOD

POWER

REMAINDER

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC

ASCII

ASCIISTR

CHR

COMPOSE

CONCAT

DECOMPOSE

INITCAP

INSTR

INSTR2

INSTR4

INSTRB

INSTRC

LENGTH

LENGTH2

LENGTH4

LENGTHB

LENGTHC

LOWER

LPAD

LTRIM

NCHR

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

REGEXP_INSTR

REGEXP_LIKE

REGEXP_REPLACE

REGEXP_SUBSTR

REPLACE

RPAD

RTRIM

SOUNDEX

SUBSTR

SUBSTR2

SUBSTR4

SUBSTRB

SUBSTRC

TRANSLATE

TRIM

UNISTR

UPPER

CHARTOROWID

CONVERT

HEXTORAW

RAWTOHEX

RAWTONHEX

ROWIDTOCHAR

TO_BINARY_DOUBLE

TO_BLOB

TO_BINARY_FLOAT

TO_CHAR

TO_CLOB

TO_DATE

TO_MULTI_BYTE

TO_NCHAR

TO_NCLOB

TO_NUMBER

TO_SINGLE_BYTE

ADD_MONTHS

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT

FROM_TZ

LAST_DAY

LOCALTIMESTAMP

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND

SESSIONTIMEZONE

SYS_EXTRACT_UTC

SYSDATE

SYSTIMESTAMP

TO_DSINTERVAL

TO_TIME

TO_TIME_TZ

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TRUNC

TZ_OFFSET

DEREF

REF

TREAT

VALUE

BFILENAME

COALESCE

DECODE

DUMP

EMPTY_BLOB

EMPTY_CLOB

GREATEST

LEAST

NANVL

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NULLIF

NVL

SYS_CONTEXT

SYS_GUID

UID

USER

USERENV

VSIZE