PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# 2
Fundamentals

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 small-scale aspects of the language. Like every other programming language, PL/SQL has a character set, reserved words, punctuation, datatypes, rigid syntax, and fixed rules of usage and statement formation. You use these basic elements of PL/SQL to represent real-world objects and operations.

##### Major Topics

Character Set
Lexical Units
Datatypes
User-Defined Subtypes
Datatype Conversion
Declarations
Naming Conventions
Scope and Visibility
Assignments
Expressions and Comparisons
Built-In Functions

## Character Set

You write a PL/SQL program as lines of text using a specific set of characters. The PL/SQL character set includes

- the upper and lowercase letters A .. Z, a .. z
- the numerals 0 .. 9
- tabs, spaces, and carriage returns
- the symbols ( ) + - * / < > = ! ~ ; : . ' @ % , " # $ ^ & _ | { } ? [ ]

PL/SQL is not case sensitive, so lowercase letters are equivalent to corresponding uppercase letters except within string and character literals.

## Lexical Units

A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:

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

For example, the line

    bonus := salary * 0.10;  -- compute bonus

contains the following lexical units:

- identifiers `bonus` and `salary`
- compound symbol `:=`
- simple symbols `*` and `;`
- numeric literal `0.10`
- comment `-- compute bonus`

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 illegal because the reserved words `END` and `IF` are joined:

    IF x > y THEN high := x; ENDIF;  -- illegal

However, you cannot embed spaces in lexical units except for string literals and comments. For example, the following line is illegal because the compound symbol for assignment (`:=`) is split:

    count : = count + 1;  -- illegal

To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare these `IF` statements for readability:

    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.

#### Simple Symbols

Simple symbols consist of one character; a list follows:

+ | 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

#### Compound Symbols

Compound symbols consist of one character; a list follows:

** | exponentiation operator
<> | relational operator
!= | relational operator
~= | relational operator
<= | relational operator
>= | relational operator
:= | assignment operator
=> | association operator
.. | range operator
|| | concatenation operator
<< | (beginning) label delimiter
>> | (ending) label delimiter
-- | single-line comment indicator
/* | (beginning) multi-line comment delimiter
*/ | (ending) multi-line comment delimiter

### 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 illegal, as the following examples show:

    mine&yours    -- illegal ampersand
    debit-amount  -- illegal hyphen
    on/off        -- illegal slash
    user id       -- illegal space

The next examples show that adjoining and trailing dollar signs, underscores, and number signs are legal:

    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. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:

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

The length of an identifier cannot exceed 30 characters. But, 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. So, 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 and so should not be redefined. For example, the words `BEGIN` and `END`, which bracket the executable part of a block or subprogram, are reserved. As the next example shows, if you try to redefine a reserved word, you get a compilation error:

    DECLARE
       end BOOLEAN;  -- illegal; causes compilation error

However, you can embed reserved words in an identifier, as the following example shows:

    DECLARE
       end_of_game BOOLEAN;  -- legal

Often, reserved words are written in upper case to promote readability. However, like other PL/SQL identifiers, reserved words can be written in lower or mixed case. 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 legal:

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

The maximum length 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.

Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word `TYPE` in a `CREATE` `TABLE` statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:

    SELECT acct, type, bal INTO ...  -- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as follows:

    SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in lower or mixed case (unless it was defined that way in the `CREATE` `TABLE` statement). For example, the following statement is invalid:

    SELECT acct, "type", bal INTO ...  -- causes compilation error

Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.

### 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 must be 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 the next example, it shifts three places to the left:

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

#### Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Several examples follow:

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

Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols.

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals `'Z'` and `'z'` to be different.

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

Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote:

    'Don''t leave without saving your work.'

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` and `FALSE` and the non-value `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.

### 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

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

    -- begin processing
    SELECT sal INTO salary FROM emp  -- get current salary
       WHERE empno = emp_id;
    bonus := salary * 0.15;  -- compute bonus amount

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 emp WHERE comm IS NULL;

#### Multi-line

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

    BEGIN
       /* Compute a 15% bonus for top-rated employees. */
       IF rating > 90 THEN
          bonus := salary * 0.15 /* bonus is based on salary */
       ELSE
          bonus := 0;
       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;

You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:

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

#### Restrictions

You cannot nest comments. Also, you cannot use single-line comments in a PL/SQL block that will be processed dynamically 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. So, use multi-line comments instead.

## Datatypes

Every constant and variable has a datatype, which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined datatypes. A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A `LOB` type holds values, called locators, that specify the location of large objects (graphic images for example) stored out-of-line.

Figure 2-1 shows the predefined datatypes available for your use. An additional scalar type, `MLSLABEL`, is available with Trusted Oracle, a specially secured version of Oracle. The scalar types fall into four families, which store number, character, Boolean, and date/time data, respectively.

#### Figure 2-1 Built-in Datatypes

This section discusses the scalar types and `LOB` types. The composite types are discussed in Chapter 4. The reference types are discussed in Chapter 5 and Chapter 9.

### Number Types

Number types allow you to store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.

#### BINARY_INTEGER

You use the `BINARY_INTEGER` datatype to store signed integers. Its magnitude range is -2147483647 .. 2147483647. Like `PLS_INTEGER` values, `BINARY_INTEGER` values require less storage than `NUMBER` values. However, most `BINARY_INTEGER` operations are slower than `PLS_INTEGER` operations. (See "PLS_INTEGER".)

##### BINARY_INTEGER Subtypes

A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following `BINARY_INTEGER` subtypes:

  `NATURAL`
  `NATURALN`
  `POSITIVE`
  `POSITIVEN`
  `SIGNTYPE`

The subtypes `NATURAL` and `POSITIVE` let you restrict an integer variable to non-negative or positive values, respectively. `NATURALN` and `POSITIVEN` prevent the assigning of nulls to an integer variable. `SIGNTYPE` lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.

#### NUMBER

You use the `NUMBER` datatype to store fixed-point or floating-point numbers of virtually any size. Its magnitude range is 1.0E-130 .. 9.99E125. You can specify precision, which is the total number of digits, and scale, which is the number of digits to the right of the decimal point. The syntax follows:

    NUMBER[(precision,scale)]

To declare fixed-point numbers, for which you must specify scale, use the following form:

    NUMBER(precision,scale)

To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can "float" to any position, use the following form:

    NUMBER

To declare integers, which have no decimal point, use this form:

    NUMBER(precision)  -- same as NUMBER(precision,0)

You cannot use constants or variables to specify precision and scale; you must use integer literals. The maximum precision of a `NUMBER` value is 38 decimal digits. If you do not specify precision, it defaults to 38 or the maximum supported by your system, whichever is less.

Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of 0 rounds to the nearest whole number. If you do not specify scale, it defaults to 0.

##### NUMBER Subtypes

`` You can use the following `NUMBER` subtypes for compatibility with ANSI/ISO and IBM types or when you want a more descriptive name:

  `DEC`
  `DECIMAL`
  `DOUBLE` `PRECISION
  FLOAT`
  `INTEGER`
  `INT`
  `NUMERIC`
  `REAL`
  `SMALLINT`

Use the subtypes `DEC`, `DECIMAL`, and `NUMERIC` to declare fixed-point numbers with a maximum precision of 38 decimal digits.

Use the subtypes `DOUBLE` `PRECISION` and `FLOAT` to declare floating-point numbers with a maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits.

Use the subtypes `INTEGER`, `INT`, and `SMALLINT` to declare integers with a maximum precision of 38 decimal digits.

#### PLS_INTEGER

You use the `PLS_INTEGER` datatype to store signed integers. Its magnitude range is -2147483647 .. 2147483647. `PLS_INTEGER` values require less storage than `NUMBER` values. Also, `PLS_INTEGER` operations use machine arithmetic, so they are faster than `NUMBER` and `BINARY_INTEGER` operations, which use library arithmetic. For better performance, use `PLS_INTEGER` for all calculations that fall within its magnitude range.

Although `PLS_INTEGER` and `BINARY_INTEGER` have the same magnitude range, they are not fully compatible. When a `PLS_INTEGER` calculation overflows, an exception is raised. However, when a `BINARY_INTEGER` calculation overflows, no exception is raised if the result is assigned to a `NUMBER` variable.

Because of this small semantic difference, you might want to continue using `BINARY_INTEGER` in old applications for compatibility. In new applications, always use `PLS_INTEGER` for better performance.

### Character Types

Character types allow you to store alphanumeric data, represent words and text, and manipulate character strings.

#### CHAR

You use the `CHAR` datatype to store fixed-length character data. How the data is represented internally depends on the database character set, which might be 7-bit ASCII or EBCDIC Code Page 500, for example.

The `CHAR` datatype takes an optional parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:

    CHAR[(maximum_length)]

You cannot use a constant or variable to specify the maximum length; you must use an integer literal in the range 1 .. 32767.

If you do not specify a maximum length, it defaults to 1. Remember, you specify the maximum length in bytes, not characters. So, if a `CHAR(``n``)` variable stores multi-byte characters, its maximum length is less than `n` characters. The maximum width of a `CHAR` database column is 2000 bytes. So, you cannot insert `CHAR` values longer than 2000 bytes into a `CHAR` column.

You can insert any `CHAR(``n``)` value into a `LONG` database column because the maximum width of a `LONG` column is 2147483647 bytes or 2 gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a `LONG` column into a `CHAR(``n``)` variable.

##### CHAR Subtype

The `CHAR` subtype `CHARACTER` has the same range of values as its base type. That is, `CHARACTER` is just another name for `CHAR`. You can use this subtype for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than `CHAR`.

#### LONG

You use the `LONG` datatype to store variable-length character strings. The `LONG` datatype is like the `VARCHAR2` datatype, except that the maximum length of a `LONG` value is 32760 bytes.

You can insert any `LONG` value into a `LONG` database column because the maximum width of a `LONG` column is 2147483647 bytes. However, you cannot retrieve a value longer than 32760 bytes from a `LONG` column into a `LONG` variable.

`LONG` columns can store text, arrays of characters, or even short documents. You can reference `LONG` columns in `UPDATE`, `INSERT`, and (most) `SELECT` statements, but not in expressions, SQL function calls, or certain SQL clauses such as `WHERE`, `GROUP` `BY`, and `CONNECT` `BY`. For more information, see Oracle8 SQL Reference.

#### RAW

You use the `RAW` datatype to store binary data or byte strings. For example, a `RAW` variable might store a sequence of graphics characters or a digitized picture. Raw data is like `VARCHAR2` data, except that PL/SQL does not interpret raw data. Likewise, Net8 does no character set conversions when you transmit raw data from one system to another.

The `RAW` datatype takes a required parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:

    RAW(maximum_length)

You cannot use a constant or variable to specify the maximum length; you must use an integer literal in the range 1 .. 32767.

The maximum width of a `RAW` database column is 2000 bytes. So, you cannot insert `RAW` values longer than 2000 bytes into a `RAW` column.

You can insert any `RAW` value into a `LONG` `RAW` database column because the maximum width of a `LONG` `RAW` column is 2147483647 bytes. However, you cannot retrieve a value longer than 32767 bytes from a `LONG` `RAW` column into a `RAW` variable.

#### LONG RAW

You use the `LONG` `RAW` datatype to store binary data or byte strings. `LONG` `RAW` data is like `LONG` data, except that `LONG` `RAW` data is not interpreted by PL/SQL. The maximum length of a `LONG` `RAW` value is 32760 bytes.

You can insert any `LONG` `RAW` value into a `LONG` `RAW` database column because the maximum width of a `LONG` `RAW` column is 2147483647 bytes. However, you cannot retrieve a value longer than 32760 bytes from a `LONG` `RAW` column into a `LONG` `RAW` variable.

#### ROWID

Internally, every database table has a `ROWID` pseudocolumn, which stores hexadecimal strings called rowids. Each rowid represents the storage address of a row. You use the `ROWID` datatype to store rowids.

You can compare a `ROWID` variable with the `ROWID` pseudocolumn in the `WHERE` clause of an `UPDATE` or `DELETE` statement to identify the latest row fetched from a cursor. See "Fetching Across Commits".

With Oracle8, rowids have been extended to support partitioned tables and indexes. Extended rowids include a data object number, which identifies the database segment. Schema objects in the same segment (for example, a cluster of tables) have the same object number.

A rowid contains the following information, which is needed to locate a row:

- data object number
- data file (the first file is 1)
- data block in the data file
- row in the data block (the first row is 0)

Rowids provide the fastest way to access particular rows. Normally, a rowid uniquely identifies a row. However, rows in different tables stored in the same cluster can have the same rowid.

To manipulate rowids, you use the supplied package `DBMS_ROWID`, which is described in Oracle8 Application Developer's Guide.

#### VARCHAR2

You use the `VARCHAR2` datatype to store variable-length character data. How the data is represented internally depends on the database character set, which might be 7-bit ASCII or EBCDIC Code Page 500, for example.

The `VARCHAR2` datatype takes a required parameter that specifies a maximum length up to 32767 bytes. The syntax follows:

    VARCHAR2(maximum_length)

You cannot use a constant or variable to specify the maximum length; you must use an integer literal in the range 1 .. 32767.

Remember, you specify the maximum length of a `VARCHAR2(``n``)` variable in bytes, not characters. So, if a `VARCHAR2(``n``)` variable stores multi-byte characters, its maximum length is less than `n` characters. The maximum width of a `VARCHAR2` database column is 4000 bytes. Therefore, you cannot insert `VARCHAR2` values longer than 4000 bytes into a `VARCHAR2` column.

You can insert any `VARCHAR2(``n``)` value into a `LONG` database column because the maximum width of a `LONG` column is 2147483647 bytes. However, you cannot retrieve a value longer than 32767 bytes from a `LONG` column into a `VARCHAR2(``n``)` variable.

Semantic differences between the `CHAR` and `VARCHAR2` base types are discussed in Appendix C.

##### VARCHAR2 Subtypes

The `VARCHAR2` subtypes below have the same range of values as their base type. For example, `VARCHAR` is just another name for `VARCHAR2`.

  `STRING`
  `VARCHAR`

You can use these subtypes for compatibility with ANSI/ISO and IBM types.

Note: Currently, `VARCHAR` is synonymous with `VARCHAR2`. However, in future releases of PL/SQL, to accommodate emerging SQL standards, `VARCHAR` might become a separate datatype with different comparison semantics. So, it is a good idea to use `VARCHAR2` rather than `VARCHAR`.

### NLS Character Types

Although the widely used 7- or 8-bit ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, some Asian languages, such as Japanese, contain thousands of characters. These languages require 16 bits (two bytes) to represent each character. How does Oracle deal with such dissimilar languages?

Oracle provides National Language Support (NLS), which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments.

With NLS, number and date formats adapt automatically to the language conventions specified for a user session. Thus, NLS allows users around the world to interact with Oracle in their native languages. For more information about NLS, see Oracle8 SQL Reference.

PL/SQL V2 supports just one character set called the database character set, which is used for identifiers and source code. But, PL/SQL V8 supports a second character set called the national character set, which is used for NLS data. The datatypes `NCHAR` and `NVARCHAR2` store character strings formed from the national character set.

#### NCHAR

You use the `NCHAR` datatype to store fixed-length (blank-padded if necessary) NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as US7ASCII or a variable-width encoding such as JA16SJIS.

The `NCHAR` datatype takes an optional parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:

    NCHAR[(maximum_length)]

You cannot use a constant or variable to specify the maximum length; you must use an integer literal in the range 1 .. 32767.

If you do not specify a maximum length, it defaults to 1. How you specify the maximum length depends on the national character set. For fixed-width character sets, you specify the maximum length in characters. For variable-width character sets, you specify it in bytes. In the following example, the character set is JA16EUCFIXED (which is fixed-width), so you specify the maximum length in characters:

    my_string NCHAR(100);  -- maximum length is 100 characters

The maximum width of an `NCHAR` database column is 2000 bytes. So, you cannot insert `NCHAR` values longer than 2000 bytes into an `NCHAR` column. Remember, for fixed-width, multi-byte character sets, you cannot insert `NCHAR` values longer than the number of characters that fit in 2000 bytes.

If the `NCHAR` value is shorter than the defined width of the `NCHAR` column, Oracle blank-pads the value to the defined width. You cannot insert `CHAR` values into an `NCHAR` column. Likewise, you cannot insert `NCHAR` values into a `CHAR` column.

#### NVARCHAR2

You use the `NVARCHAR2` datatype to store variable-length NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as WE8EBCDIC37C or a variable-width encoding such as JA16DBCS.

The `NVARCHAR2` datatype takes a required parameter that specifies a maximum length up to 32767 bytes. The syntax follows:

    NVARCHAR2(maximum_length)

You cannot use a constant or variable to specify the maximum length; you must use an integer literal in the range 1 .. 32767.

How you specify the maximum length depends on the national character set. For fixed-width character sets, you specify the maximum length in characters. For variable-width character sets, you specify it in bytes. In the following example, the character set is JA16SJIS (which is variable-width), so you specify the maximum length in bytes:

    my_string NVARCHAR2(200);  -- maximum length is 200 bytes

The maximum width of a `NVARCHAR2` database column is 4000 bytes. Therefore, you cannot insert `NVARCHAR2` values longer than 4000 bytes into a `NVARCHAR2` column. Remember, for fixed-width, multi-byte character sets, you cannot insert `NVARCHAR2` values longer than the number of characters that fit in 4000 bytes.

You cannot `insertVARCHAR2` values into an `NVARCHAR2` column. Likewise, you cannot insert `NVARCHAR2` values into a `VARCHAR2` column.

### LOB Types

The `LOB` (large object) datatypes `BFILE`, `BLOB`, `CLOB`, and `NCLOB` let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.

The `LOB` types differ from the `LONG` and `LONG` `RAW` types in several ways. For example, `LOB`s (except `NCLOB`) can be attributes of an object type, but `LONG`s cannot. The maximum size of a `LOB` is four gigabytes, but the maximum size of a `LONG` is two gigabytes. Also, `LOB`s support random access to data, but `LONG`s support only sequential access.

`LOB` types store values, called locators, that specify the location of large objects stored in an external file, in-line (inside the row) or out-of-line (outside the row). Database columns of type `BLOB`, `CLOB`, `NCLOB`, or `BFILE` store the locators. `BLOB`, `CLOB`, and `NCLOB` data is stored in the database, in or outside the row. `BFILE` data is stored in operating system files outside the database.

PL/SQL operates on `LOB`s through the locators. For example, when you retrieve a `BLOB` column value, only a locator is returned. Locators cannot span transactions or sessions. So, you cannot save a locator in a PL/SQL variable during one transaction or session, then use it in another transaction or session. To manipulate `LOB`s, you use the supplied package `DBMS_LOB`. For more information about `LOB`s and package `DBMS_LOB`, see Oracle8 Application Developer's Guide.

#### BFILE

You use the `BFILE` datatype to store large binary objects in operating system files outside the database. Every `BFILE` variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name (logical path names are not supported).

`BFILE`s are read-only. You cannot modify them. The maximum number of open `BFILE`s is set by the Oracle initialization parameter `SESSION_MAX_OPEN_FILES`, which is system dependent.

Also, `BFILE`s do not participate in transactions. The underlying operating system maintains file integrity. The size of a `BFILE` is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes). Your DBA makes sure that the `BFILE` exists and that Oracle has read permissions on it.

#### BLOB

You use the `BLOB` datatype to store large binary objects in the database in-line or out-of-line. Every `BLOB` variable stores a locator, which points to a large binary object. The size of a `BLOB` cannot exceed four gigabytes.

`BLOB`s participate fully in transactions. Changes made by package `DBMS_LOB` or the OCI can be committed or rolled back. However, `BLOB` locators cannot span transactions or sessions.

#### CLOB

You use the `CLOB` datatype to store large blocks of single-byte character data in the database, in-line or out-of-line. (Variable-width character sets are not supported.) Every `CLOB` variable stores a locator, which points to a large block of character data. The size of a `CLOB` cannot exceed four gigabytes.

`CLOB`s participate fully in transactions. Changes made by package `DBMS_LOB` or the OCI can be committed or rolled back. However, `CLOB` locators cannot span transactions or sessions.

#### NCLOB

You use the `NCLOB` datatype to store large blocks of single-byte or fixed-width multi-byte `NCHAR` data in the database, in-line or out-of-line. (Variable-width character sets are not supported.) Every `NCLOB` variable stores a locator, which points to a large block of `NCHAR` data. The size of an `NCLOB` cannot exceed four gigabytes.

`NCLOB`s participate fully in transactions. Changes made by package `DBMS_LOB` or the OCI can be committed or rolled back. However, `NCLOB` locators cannot span transactions or sessions.

### Other Types

The following types allow you to store and manipulate logical (true, false) values, date/time values, and Trusted Oracle operating system labels.

#### BOOLEAN

You use the `BOOLEAN` datatype to store the logical values `TRUE` and `FALSE` and the non-value `NULL`, which stands for a missing, inapplicable, or unknown value. Only logic operations are allowed on `BOOLEAN` variables.

The `BOOLEAN` datatype takes no parameters. Only the values `TRUE` and `FALSE` and the non-value `NULL` can be assigned to a `BOOLEAN` variable. You cannot insert the values `TRUE` and `FALSE` into a database column. Also, you cannot select or fetch column values into a `BOOLEAN` variable.

#### DATE

You use the `DATE` datatype to store fixed-length date/time values. `DATE` values include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight. The date function `SYSDATE` returns the current date and time.

Valid dates range from January 1, 4712 BC to December 31, 4712 AD. A Julian date is the number of days since January 1, 4712 BC. Julian dates allow continuous dating from a common reference. You can use the date format model `'J'` with date functions `TO_DATE` and `TO_CHAR` to convert between `DATE` values and their Julian equivalents.

In date expressions, PL/SQL automatically converts character values in the default date format to `DATE` values. The default date format is set by the Oracle initialization parameter `NLS_DATE_FORMAT`. For example, the default might be `'DD-MON-YY'`, which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

You can add and subtract dates. For example, the following statement returns the number of days since an employee was hired:

    SELECT SYSDATE - hiredate INTO days_worked FROM emp WHERE empno = 7499;

In arithmetic expressions, PL/SQL interprets integer literals as days. For instance, `SYSDATE` + 1 is tomorrow.

For more information about date functions and format models, see Oracle8 SQL Reference.

#### MLSLABEL

Trusted Oracle provides the `MLSLABEL` datatype, which stores Trusted Oracle's internal representation of labels generated by multi-level secure operating systems. Trusted Oracle uses the labels to control database access.

For compatibility with Trusted Oracle applications, the Oracle server lets you use `MLSLABEL` to define database columns. However, such columns can store only nulls. For more information, see your Trusted Oracle documentation.

## User-Defined Subtypes

Each PL/SQL base type specifies a set of values and a set of operations applicable to items of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.

PL/SQL predefines several subtypes in package `STANDARD`. For example, PL/SQL predefines the subtype `CHARACTER`, as follows:

    SUBTYPE CHARACTER IS CHAR;

The subtype `CHARACTER` specifies the same set of values as its base type `CHAR`. Thus, `CHARACTER` is an unconstrained subtype.

Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables.

### Defining Subtypes

You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax

    SUBTYPE subtype_name IS base_type;

where `subtype_name` is a type specifier used in subsequent declarations and `base_type` is any scalar or user-defined PL/SQL type. To specify `base_type`, you can use `%TYPE`, which provides the datatype of a variable or database column. Also, you can use `%ROWTYPE`, which provides the rowtype of a cursor, cursor variable, or database table. Some examples follow:

    DECLARE
       SUBTYPE EmpDate IS DATE;           -- based on DATE type
       SUBTYPE Counter IS NATURAL;        -- based on NATURAL subtype
       TYPE NameList IS TABLE OF VARCHAR2(10);
       SUBTYPE EmpRoster IS NameList;     -- based on TABLE type
       TYPE TimeRec IS RECORD (minutes INTEGER, hours INTEGER);
       SUBTYPE Time IS TimeRec;           -- based on RECORD type
       SUBTYPE ID_Num IS emp.empno%TYPE;  -- based on column type
       CURSOR c1 IS SELECT * FROM dept;
       SUBTYPE DeptFile IS c1%ROWTYPE;    -- based on cursor rowtype

However, you cannot specify a constraint on the base type. For example, the following definitions are illegal:

    DECLARE
       SUBTYPE Accumulator IS NUMBER(7,2); -- illegal; must be NUMBER
       SUBTYPE Delimiter IS CHAR(1);       -- illegal; must be CHAR
       SUBTYPE Word IS VARCHAR2(15);       -- illegal

Although you cannot define constrained subtypes directly, you can use a simple workaround to define size-constrained subtypes indirectly. Simply declare a size-constrained variable, then use `%TYPE` to provide its datatype, as shown in the following example:

    DECLARE
       temp VARCHAR2(15);
       SUBTYPE Word IS temp%TYPE; -- maximum size of Word is 15

Likewise, if you define a subtype using `%TYPE` to provide the datatype of a database column, the subtype adopts the size constraint (if any) of the column. However, the subtype does not adopt other kinds of constraints such as `NOT` `NULL`.

### Using Subtypes

Once you define a subtype, you can declare items of that type. In the example below, you declare two variables of type `Counter`. Notice how the subtype name indicates the intended use of the variables.

    DECLARE
       SUBTYPE Counter IS NATURAL;
       rows      Counter;
       employees Counter;

The following example shows that you can constrain a user-defined subtype when declaring variables of that type:

    DECLARE
       SUBTYPE Accumulator IS NUMBER;
       total Accumulator(7,2);

Subtypes can increase reliability by detecting out-of-range values. In the example below, you restrict the subtype `Scale` to storing integers in the range -9 .. 9. If your program tries to store a number outside that range in a `Scale` variable, PL/SQL raises an exception.

    DECLARE
       temp NUMBER(1,0);
       SUBTYPE Scale IS temp%TYPE;
       x_axis Scale;  -- magnitude range is -9 .. 9
       y_axis Scale;
    BEGIN
       x_axis := 10;  -- raises VALUE_ERROR

#### Type Compatibility

An unconstrained subtype is interchangeable with its base type. For example, given the following declarations, the value of `amount` can be assigned to `total` without conversion:

    DECLARE
       SUBTYPE Accumulator IS NUMBER;
       amount NUMBER(7,2);
       total  Accumulator;
    BEGIN
       ...
       total := amount;

Different subtypes are interchangeable if they have the same base type. For instance, given the following declarations, the value of `finished` can be assigned to `debugging`:

    DECLARE
       SUBTYPE Sentinel IS BOOLEAN;
       SUBTYPE Switch IS BOOLEAN;
       finished  Sentinel;
       debugging Switch;
    BEGIN
       ...
       debugging := finished;

Different subtypes are also interchangeable if their base types are in the same datatype family. For example, given the following declarations, the value of `verb` can be assigned to `sentence`:

    DECLARE
       SUBTYPE Word IS CHAR;
       SUBTYPE Text IS VARCHAR2;
       verb     Word;
       sentence Text;
    BEGIN
       ...
       sentence := verb;

## Datatype Conversion

Sometimes it is necessary to convert a value from one datatype to another. For example, if you want to examine a rowid, you must convert it to a character string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.

### Explicit Conversion

To convert values from one datatype to another, you use built-in functions. For example, to convert a `CHAR` value to a `DATE` or `NUMBER` value, you use the function `TO_DATE` or `TO_NUMBER`, respectively. Conversely, to convert a `DATE` or `NUMBER` value to a `CHAR` value, you use the function `TO_CHAR`. For more information about these functions, see Oracle8 SQL Reference.

### Implicit Conversion

When it makes sense, PL/SQL can convert the datatype of a value implicitly. This allows you to use literals, variables, and parameters of one type where another type is expected. In the example below, the `CHAR` variables `start_time` and `finish_time` hold string values representing the number of seconds past midnight. The difference between those values must be assigned to the `NUMBER` variable `elapsed_time`. So, PL/SQL converts the `CHAR` values to `NUMBER` values automatically.

    DECLARE
       start_time   CHAR(5);
       finish_time  CHAR(5);
       elapsed_time NUMBER(5);
    BEGIN
       /* Get system time as seconds past midnight. */
       SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual;
       -- do something
       /* Get system time again. */
       SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual;
       /* Compute elapsed time in seconds. */
       elapsed_time := finish_time - start_time;
       INSERT INTO results VALUES (elapsed_time, ...);
    END;

Before assigning a selected column value to a variable, PL/SQL will, if necessary, convert the value from the datatype of the source column to the datatype of the variable. This happens, for example, when you select a `DATE` column value into a `VARCHAR2` variable.

Likewise, before assigning the value of a variable to a database column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column. If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. Table 2-1 shows which implicit conversions PL/SQL can do.

##### Table 2-1 Implicit Conversions

| BIN_INT | CHAR | DATE | LONG | NUMBER | PLS_INT | RAW | ROWID | VARCHAR2
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
BIN_INT |  | X |  | X | X | X |  |  | X
CHAR | X |  | X | X | X | X | X | X | X
DATE |  | X |  | X |  |  |  |  | X
LONG |  | X |  |  |  |  | X |  | X
NUMBER | X | X |  | X |  | X |  |  | X
PLS_INT | X | X |  | X | X |  |  |  | X
RAW |  | X |  | X |  |  |  |  | X
ROWID |  | X |  |  |  |  |  |  | X
VARCHAR2 | X | X | X | X | X | X | X | X |

It is your responsibility to ensure that values are convertible. For instance, PL/SQL can convert the `CHAR` value `'02-JUN-92'` to a `DATE` value but cannot convert the `CHAR` value `'YESTERDAY'` to a `DATE` value. Similarly, PL/SQL cannot convert a `VARCHAR2` value containing alphabetic characters to a `NUMBER` value.

### Implicit versus Explicit Conversion

Generally, to rely on implicit datatype conversions is a poor programming practice because they can hamper performance and might change from one software release to the next. Also, implicit conversions are context sensitive and therefore not always predictable. Instead, use datatype conversion functions. That way, your applications will be more reliable and easier to maintain.

### DATE Values

When you select a `DATE` column value into a `CHAR` or `VARCHAR2` variable, PL/SQL must convert the internal binary value to a character value. So, PL/SQL calls the function `TO_CHAR`, which returns a character string in the default date format. To get other information such as the time or Julian date, you must call `TO_CHAR` with a format mask.

A conversion is also necessary when you insert a `CHAR` or `VARCHAR2` value into a `DATE` column. So, PL/SQL calls the function `TO_DATE`, which expects the default date format. To insert dates in other formats, you must call `TO_DATE` with a format mask.

### RAW and LONG RAW Values

When you select a `RAW` or `LONG` `RAW` column value into a `CHAR` or `VARCHAR2` variable, PL/SQL must convert the internal binary value to a character value. In this case, PL/SQL returns each binary byte of `RAW` or `LONG` `RAW` data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, PL/SQL returns the binary byte 11111111 as the pair of characters `'FF'`. The function `RAWTOHEX` does the same conversion.

A conversion is also necessary when you insert a `CHAR` or `VARCHAR`2 value into a `RAW` or `LONG` `RAW` column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte. If either character does not represent the hexadecimal equivalent of a nibble, PL/SQL raises an exception.

### NLS Values

When passed an uppercase character set name, the built-in function `NLS_CHARSET_ID` returns the corresponding character set ID number. Conversely, when passed a character set ID number, the function `NLS_CHARSET_NAME` returns the corresponding character set name.

If you pass the value `'CHAR_CS'` or `'NCHAR_CS'` to `NLS_CHARSET_ID`, it returns the database or national character set ID number, respectively. For a list of character set names, see Oracle8 Reference.

## 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:

    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:

    pi     REAL := 3.14159;
    radius REAL := 1;
    area   REAL := pi * radius**2;

By default, variables are initialized to `NULL`. For example, the following declarations are equivalent:

    birthday DATE;
    birthday DATE := NULL;

In constant declarations, the keyword `CONSTANT` must precede the type specifier, as the following example shows:

    credit_limit CONSTANT REAL := 5000.00;

This declaration names a constant of type `REAL` and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated. (The processing of a declaration by the PL/SQL compiler is called elaboration.)

### Using DEFAULT

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

    blood_type CHAR := 'O';
    valid      BOOLEAN := FALSE;

can be rewritten as follows:

    blood_type CHAR DEFAULT 'O';
    valid      BOOLEAN DEFAULT FALSE;

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

    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, as the following example shows:

    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. For example, the following declaration is illegal:

    acct_id INTEGER(5) NOT NULL;  -- illegal; not initialized

Recall that the subtypes `NATURALN` and `POSITIVEN` are predefined as `NOT` `NULL`. For instance, the following declarations are equivalent:

    emp_count NATURAL NOT NULL := 0;
    emp_count NATURALN := 0;

In `NATURALN` and `POSITIVEN` declarations, the type specifier must be followed by an initialization clause. Otherwise, you get a compilation error. For example, the following declaration is illegal:

    line_items POSITIVEN;  -- illegal; not initialized

### Using %TYPE

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

    credit REAL(7,2);
    debit  credit%TYPE;

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. The next example shows that a `%TYPE` declaration can include an initialization clause:

    balance         NUMBER(7,2);
    minimum_balance balance%TYPE := 10.00;

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

    my_dname scott.dept.dname%TYPE;

Using `%TYPE` to declare `my_dname` has two advantages. First, you need not know the exact datatype of `dname`. Second, if the database definition of `dname` changes, the datatype of `my_dname` changes accordingly at run time.

Note, however, that a `NOT` `NULL` column constraint does not apply to variables declared using `%TYPE`. In the next example, even though the database column `empno` is defined as `NOT` `NULL`, you can assign a null to the variable `my_empno`:

    DECLARE
       my_empno emp.empno%TYPE;
       ...
    BEGIN
       my_empno := NULL;  -- this works

### Using %ROWTYPE

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. In the example below, you declare two records. The first record stores a row selected from the `emp` table. The second record stores a row fetched from cursor `c1`.

    DECLARE
       emp_rec emp%ROWTYPE;
       CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
       dept_rec c1%ROWTYPE;

Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you select column values into a record named `emp_rec`:

    DECLARE
       emp_rec emp%ROWTYPE;
       ...
    BEGIN
       SELECT * INTO emp_rec FROM emp WHERE ...

The column values returned by the `SELECT` statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the `deptno` field as follows:

    IF emp_rec.deptno = 20 THEN ...

Also, you can assign the value of an expression to a specific field, as the following examples show:

    emp_rec.ename := 'JOHNSON';
    emp_rec.sal := emp_rec.sal * 1.15;

In the final example, you use `%ROWTYPE` to define a packaged cursor:

    CREATE PACKAGE emp_actions AS
       /* Declare cursor specification. */
       CURSOR c1 RETURN emp%ROWTYPE;
       ...
    END emp_actions;

    CREATE PACKAGE BODY emp_actions AS
       /* Define cursor body. */
       CURSOR c1 RETURN emp%ROWTYPE IS
          SELECT * FROM emp WHERE sal > 3000;
       ...
    END emp_actions;

#### Aggregate Assignment

A `%ROWTYPE` declaration cannot include an initialization clause. However, there are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor. For example, the following assignment is legal:

    DECLARE
       dept_rec1 dept%ROWTYPE;
       dept_rec2 dept%ROWTYPE;
       CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
       dept_rec3 c1%ROWTYPE;
    BEGIN
       ...
       dept_rec1 := dept_rec2;

However, because `dept_rec2` is based on a table and `dept_rec3` is based on a cursor, the following assignment is illegal:

    dept_rec2 := dept_rec3;  -- illegal

Second, you can assign a list of column values to a record by using the `SELECT` or `FETCH` statement, as the example below 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 dept%ROWTYPE;
       ...
    BEGIN
       SELECT deptno, dname, loc INTO dept_rec FROM dept
          WHERE deptno = 30;

However, you cannot assign a list of column values to a record by using an assignment statement. So, the following syntax is illegal:

    record_name := (value1, value2, value3, ...);  -- illegal

Although you can retrieve entire records, you cannot insert or update them. For example, the following statement is illegal:

    INSERT INTO dept VALUES (dept_rec);  -- illegal

#### Using Aliases

Select-list items fetched from a cursor associated with `%ROWTYPE` must have simple names or, if they are expressions, must have aliases. In the following example, you use an alias called `wages`:

    -- available online in file 'examp4'
    DECLARE
       CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename
          FROM emp;
       my_rec  my_cursor%ROWTYPE;
    BEGIN
       OPEN my_cursor;
       LOOP
          FETCH my_cursor INTO my_rec;
          EXIT WHEN my_cursor%NOTFOUND;
          IF my_rec.wages > 2000 THEN
             INSERT INTO temp VALUES (NULL, my_rec.wages,
                my_rec.ename);
          END IF;
       END LOOP;
       CLOSE my_cursor;
    END;

### Restrictions

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of `maxi` is illegal:

    maxi INTEGER := 2 * mini;  -- illegal
    mini INTEGER := 15;

However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Forward Declarations".

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is illegal:

    i, j, k SMALLINT;  -- illegal

The legal version follows:

    i SMALLINT;
    j SMALLINT;
    k SMALLINT;

## 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, stand-alone subprograms, and packages. However, you cannot create synonyms for items declared within subprograms or packages. That includes constants, variables, cursors, cursor variables, exceptions, and packaged procedures.

### Scoping

Within the same scope, all declared identifiers must be unique. So, even if their datatypes differ, variables and parameters cannot share the same name. For example, two of the following declarations are illegal:

    DECLARE
       valid_id BOOLEAN;
       valid_id VARCHAR2(5);  -- illegal duplicate identifier
       FUNCTION bonus (valid_id IN INTEGER) RETURN REAL IS ...
                              -- illegal triplicate identifier

For the scoping rules that apply to identifiers, see "Scope and Visibility".

### Case Sensitivity

Like other 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;  -- same as zip_code
       ZIP_CODE INTEGER;  -- same as zip_code and Zip_Code

### Name Resolution

In potentially ambiguous SQL statements, the names of local variables and formal parameters take precedence over the names of database tables. For example, the following `UPDATE` statement fails because PL/SQL assumes that `emp` refers to the loop counter:

    FOR emp IN 1..5 LOOP
       ...
       UPDATE emp SET bonus = 500 WHERE ...
    END LOOP;

Likewise, the following `SELECT` statement fails because PL/SQL assumes that `emp` refers to the formal parameter:

    PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS
       avg_sal REAL;
    BEGIN
       SELECT AVG(sal) INTO avg_sal FROM emp WHERE ...

In such cases, you can prefix the table name with a username, as follows, but a better programming practice is to rename the variable or formal parameter:

    PROCEDURE calc_bonus (emp NUMBER, bonus OUT REAL) IS
       avg_sal REAL;
    BEGIN
       SELECT AVG(sal) INTO avg_sal FROM scott.emp WHERE ...

Unlike the names of tables, the names of columns take precedence over the names of local variables and formal parameters. For example, the following `DELETE` statement removes all employees from the `emp` table, not just ``KING'`, because Oracle assumes that both `enames` in the `WHERE` clause refer to the database column:

    DECLARE
       ename VARCHAR2(10) := 'KING';
    BEGIN
       DELETE FROM emp WHERE ename = ename;

In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with `my_`, as follows:

    DECLARE
       my_ename VARCHAR2(10);

Or, use a block label to qualify references, as in

    <<main>>
    DECLARE
       ename VARCHAR2(10) := 'KING';
    BEGIN
       DELETE FROM emp WHERE ename = main.ename;

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

    FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS
       job CHAR(10);
    BEGIN
       SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;

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

## Scope and Visibility

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-2 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-2 Scope and Visibility

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, as the following example shows:

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

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

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

## Assignments

Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to `NULL`. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:

    DECLARE
       count INTEGER;
       ...
    BEGIN
       count := count + 1;  -- assigns a null to count

The expression on the right of the assignment operator yields `NULL` because `count` is null. To avoid unexpected results, never reference a variable before you assign it a value.

You can use assignment statements to assign values to a variable. 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 datatype that is the same as or convertible to the datatype of the variable.

### Boolean Values

Only the values `TRUE` and `FALSE` and the non-value `NULL` can be assigned to a Boolean variable. For example, given the declaration

    DECLARE
       done BOOLEAN;

the following statements are legal:

    BEGIN
       done := FALSE;
       WHILE NOT done LOOP
          ...
       END LOOP;

When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

    done := (count > 500);

### Database Values

Alternatively, 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. An example follows:

    DECLARE
       my_empno emp.empno%TYPE;
       my_ename emp.ename%TYPE;
       wages    NUMBER(7,2);
    BEGIN
       ...
       SELECT ename, sal + comm
          INTO last_name, wages FROM emp
          WHERE empno = emp_id;

However, you cannot select column values into a Boolean variable.

## 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 (finds the current value of) an expression by combining the values of the operands in ways specified by the operators. This always yields a single value and datatype. PL/SQL determines the datatype 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-2 shows the default order of operations from first to last (top to bottom).

##### Table 2-2 Order of Operations

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

Operators with higher precedence are applied first. For example, both of the following expressions yield 8 because division has a higher precedence than addition:

    5 + 12 / 4
    12 / 4 + 5

Operators with the same precedence are applied in no particular order.

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-3. `AND` and `OR` are binary operators; `NOT` is a unary operator.

##### Table 2-3 Logic Truth Table

x | y | x AND y | x OR y | NOT x
--- | --- | --- | --- | ---
TRUE | TRUE | TRUE | TRUE | FALSE
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | TRUE | FALSE | TRUE | TRUE
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | TRUE | NULL | TRUE | NULL
NULL | FALSE | FALSE | 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. It follows that if you apply the `NOT` operator to a null, the result is also indeterminate. Be careful. Nulls can cause unexpected results; see "Handling Nulls".

#### 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 allows you to write expressions that might otherwise cause an error. Consider the following `OR` expression:

    DECLARE
       ...
       on_hand  INTEGER;
       on_order INTEGER;
    BEGIN
       ..
       IF (on_hand = 0) OR (on_order / on_hand < 5) THEN
          ...
       END IF;
    END;

When the value of `on_hand` is zero, the left operand yields `TRUE`, so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the `OR` operator, the right operand would cause a division by zero error. In any case, it is a poor programming practice to rely on short-circuit evaluation.

### Comparison Operators

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

#### Relational Operators

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator:

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`. Therefore, to test for nullity (the state of being null), do not use the statement

    IF variable = NULL THEN ...

Instead, use the following statement:

    IF variable IS NULL THEN ...

#### LIKE Operator

You use the `LIKE` operator to compare a character value to a pattern. Case is significant. `LIKE` returns the Boolean value `TRUE` if the character 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 yields `TRUE`:

    ename LIKE 'J%SON'

#### 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 yields `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 statement does not delete rows in which the `ename` column is null:

    DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');

Furthermore, expressions of the form

    value NOT IN set

yield `FALSE` if the set contains a null. For example, instead of deleting rows in which the `ename` column is not null and not `'KING'`, the following statement deletes no rows:

    DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');

### Concatenation Operator

Double vertical bars (||) serve as the concatenation operator, which appends one string to another, as the following example shows:

    'suit' || 'case' = 'suitcase'

If both operands have datatype `CHAR`, the concatenation operator returns a `CHAR` value. 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.

#### 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 yields `TRUE`:

    number1 > number2

#### Character Expressions

Likewise, you can compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. 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. For example, given the assignments

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

the following expression yields `TRUE`:

    string1 > string2

However, there are semantic differences between the `CHAR` and `VARCHAR2` base types that come into play when you compare character values. For more information, refer to Appendix C.

#### 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 yields `TRUE`:

    date1 > date2

#### Guidelines

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

    count := 1;
    IF count = 1.0 THEN ...

It is a good idea to use parentheses when doing comparisons. For example, the following expression is illegal because `100 < tax` yields `TRUE` or `FALSE`, which cannot be compared with the number 500:

    100 < tax < 500  -- illegal

The debugged version follows:

    (100 < tax) AND (tax < 500)

A Boolean variable is itself either true or false. So, comparisons with the Boolean values `TRUE` and `FALSE` are redundant. For example, assuming the variable `done` is of type `BOOLEAN`, the `WHILE` statement

    WHILE NOT (done = TRUE) LOOP
       ...
    END LOOP;

can be simplified as follows:

    WHILE NOT done LOOP
       ...
    END LOOP;

### Handling Nulls

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

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.

    x := 5;
    y := NULL;
    ...
    IF x != y THEN  -- yields NULL, not TRUE
       sequence_of_statements;  -- not executed
    END IF;

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.

    a := NULL;
    b := NULL;
    ...
    IF a = b THEN  -- yields NULL, not TRUE
       sequence_of_statements;  -- not executed
    END IF;

#### 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`. So, if either or both `x` and `y` are null, the first `IF` statement assigns the value of `y` to `high`, but the second `IF` statement assigns the value of `x` to `high`. If neither `x` nor `y` is null, both `IF` statements assign the same value to `high`.

#### 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:

    null_string := TO_VARCHAR2('');
    zip_code := SUBSTR(address, 25, 0);
    valid := (name != '');

So, use the `IS` `NULL` operator to test for null strings, as follows:

    IF my_string IS NULL THEN ...

#### Concatenation Operator

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

    'apple' || NULL || NULL || 'sauce' = 'applesauce'

#### 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:

    SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000)
       INTO credit_limit FROM accts WHERE acctno = my_acctno;

The function `NVL` returns the value of its second argument if its first argument is null. In the example below, if `hire_date` is null, `NVL` returns the value of `SYSDATE`. Otherwise, `NVL` returns the value of `hire_date`:

    start_date := NVL(hire_date, SYSDATE);

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 instance, after the assignment

    new_string := REPLACE(old_string, NULL, my_string);

the values of `old_string` and `new_string` are the same.

If its third argument is null, `REPLACE` returns its first argument with every occurrence of its second argument removed. For example, after the assignments

    syllabified_name := 'Gold-i-locks';
    name := REPLACE(syllabified_name, '-', NULL);

the value of `name` is `'goldilocks'`

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

## 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
- conversion
- date
- miscellaneous

Table 2-4 shows the functions in each category. For descriptions of the error-reporting functions, see Chapter 11. For descriptions of the other functions, see Oracle8 SQL Reference.

You can use all the functions in SQL statements except the error-reporting functions `SQLCODE` and `SQLERRM`. Also, you can use all the functions in procedural statements except the miscellaneous functions `DECODE`, `DUMP`, and `VSIZE`.

The SQL group functions `AVG`, `MIN`, `MAX`, `COUNT`, `SUM`, `STDDEV`, and `VARIANCE` are not built into PL/SQL. Nevertheless, you can use them in SQL statements (but not in procedural statements).

##### Table 2-4 Built-in Functions

Error | Number | Character | Conversion | Date | Misc
--- | --- | --- | --- | --- | ---
    SQLCODE
    SQLERRM
    ABS
    ACOS
    ASIN
    ATAN
    ATAN2
    CEIL
    COS
    COSH
    EXP
    FLOOR
    LN
    LOG
    MOD
    POWER
    ROUND
    SIGN
    SIN
    SINH
    SQRT
    TAN
    TANH
    TRUNC
    ASCII
    CHR
    CONCAT
    INITCAP
    INSTR
    INSTRB
    LENGTH
    LENGTHB
    LOWER
    LPAD
    LTRIM
    NLS_INITCAP
    NLS_LOWER
    NLS_UPPER
    NLSSORT
    REPLACE
    RPAD
    RTRIM
    SOUNDEX
    SUBSTR
    SUBSTRB
    TRANSLATE
    UPPER
    CHARTOROWID
    CONVERT
    HEXTORAW
    NLS_CHARSET_ID
    NLS_CHARSET_NAME
    RAWTOHEX
    ROWIDTOCHAR
    TO_CHAR
    TO_DATE
    TO_LABEL
    TO_MULTI_BYTE
    TO_NUMBER
    TO_SINGLE_BYTE
    ADD_MONTHS
    LAST_DAY
    MONTHS_BETWEEN
    NEW_TIME
    NEXT_DAY
    ROUND
    SYSDATE
    TRUNC
    DECODE
    DUMP
    GREATEST
    GREATEST_LB
    LEAST
    LEAST_UB
    NVL
    UID
    USER
    USERENV
    VSIZE

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index