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

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

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

3 PL/SQL Data Types

Every constant, variable, and parameter has a data type (also called a type) that determines its storage format, constraints, valid range of values, and operations that can be performed on it. PL/SQL provides many predefined data types and subtypes, and lets you define your own PL/SQL subtypes.

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values. Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables.

This chapter explains the basic, frequently used predefined PL/SQL data types and subtypes, how to define and use your own PL/SQL subtypes, and PL/SQL data type conversion. Later chapters explain specialized predefined data types.

Table 3-1 lists the categories of predefined PL/SQL data types, describes the data they store, and tells where to find information about the specialized data types.

Table 3-1 Categories of Predefined PL/SQL Data Types

Data Type Category Data Description

Scalar

Single values with no internal components.

Composite

Data items that have internal components that can be accessed individually. Explained in Chapter 5, "Using PL/SQL Collections and Records."

Reference

Pointers to other data items. Explained in Using Cursor Variables (REF CURSORs).

Large Object (LOB)

Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.


Topics:

Predefined PL/SQL Scalar Data Types and Subtypes

Scalar data types store single values with no internal components. Table 3-2 lists the predefined PL/SQL scalar data types and describes the data they store.

Table 3-2 Categories of Predefined PL/SQL Scalar Data Types

Category Data Description

Numeric

Numeric values, on which you can perform arithmetic operations.

Character

Alphanumeric values that represent single characters or strings of characters, which you can manipulate.

BOOLEAN

Logical values, on which you can perform logical operations.

Datetime

Dates and times, which you can manipulate.

Interval

Time intervals, which you can manipulate.


Topics:

Predefined PL/SQL Numeric Data Types and Subtypes

Numeric data types let you store numeric data, represent quantities, and perform calculations. Table 3-3 lists the predefined PL/SQL numeric types and describes the data they store.

Table 3-3 Predefined PL/SQL Numeric Data Types

Data Type Data Description

PLS_INTEGER or BINARY_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

BINARY_FLOAT

Single-precision IEEE 754-format floating-point number

BINARY_DOUBLE

Double-precision IEEE 754-format floating-point number

NUMBER

Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.


Topics:

PLS_INTEGER and BINARY_INTEGER Data Types

The PLS_INTEGER and BINARY_INTEGER data types are identical. For simplicity, this document uses "PLS_INTEGER" to mean both PLS_INTEGER and BINARY_INTEGER.

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

The PLS_INTEGER data type has the following advantages over the NUMBER data type and NUMBER subtypes:

  • PLS_INTEGER values require less storage.

  • PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

For efficiency, use PLS_INTEGER values for all calculations that fall within its range. For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.

Note:

When a calculation with two PLS_INTEGER data types overflows the PLS_INTEGER range, an overflow exception is raised even if the result is assigned to a NUMBER data type.

Table 3-4 lists the predefined subtypes of the PLS_INTEGER data type and describes the data they store.

Table 3-4 Predefined Subtypes of PLS_INTEGER Data Type

Data Type Data Description

NATURAL

Nonnegative PLS_INTEGER value

NATURALN

Nonnegative PLS_INTEGER value with NOT NULL constraint

POSITIVE

Positive PLS_INTEGER value

POSITIVEN

Positive PLS_INTEGER value with NOT NULL constraint

SIGNTYPE

PLS_INTEGER value -1, 0, or 1 (useful for programming tri-state logic)

SIMPLE_INTEGER

PLS_INTEGER value with NOT NULL constraint


SIMPLE_INTEGER Subtype of PLS_INTEGER

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics.

You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER provides significantly better performance than PLS_INTEGER when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

Topics:

Overflow Semantics

The overflow semantics of SIMPLE_INTEGER differ significantly from those of PLS_INTEGER. An arithmetic operation that increases a PLS_INTEGER value to greater than 2,147,483,647 or decrease it to less than -2,147,483,648 causes error ORA-01426. In contrast, when the following PL/SQL block is run from SQL*Plus, it runs without error:

SQL> DECLARE
  2    n SIMPLE_INTEGER := 2147483645;
  3  BEGIN
  4    FOR j IN 1..4 LOOP
  5      n := n + 1;
  6      DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
  7    END LOOP;
  8    FOR j IN 1..4 LOOP
  9      n := n - 1;
 10      DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
 11    END LOOP;
 12  END;
 13  /
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645
 
PL/SQL procedure successfully completed.
 
SQL> 
Overloading Rules
  • In overloaded subprograms, SIMPLE_INTEGER and PLS_INTEGER actual parameters can be substituted for each other.

  • If all of their operands or arguments have the data type SIMPLE_INTEGER, the following produce SIMPLE_INTEGER results, using two's complement arithmetic and ignoring overflows:

    • Operators:

      • Addition (+)

      • Subtraction (-)

      • Multiplication (*)

    • Built-in functions:

      • MAX

      • MIN

      • ROUND

      • SIGN

      • TRUNC

    • CASE expression

    If some but not all operands or arguments have the data type SIMPLE_INTEGER, those of the data type SIMPLE_INTEGER are implicitly cast to PLS_INTEGER NOT NULL.

Integer Literals

Integer literals in the SIMPLE_INTEGER range have the dataype SIMPLE_INTEGER. This relieves you from explicitly casting each integer literal to SIMPLE_INTEGER in arithmetic expressions computed using two's complement arithmetic.

If and only if all operands and arguments have the dataype SIMPLE_INTEGER, PL/SQL uses two's complement arithmetic and ignores overflows. Because overflows are ignored, values can wrap from positive to negative or from negative to positive; for example:

230 + 230 = 0x40000000 + 0x40000000 = 0x80000000 = -231

-231 + -231 = 0x80000000 + 0x80000000 = 0x00000000 = 0

To ensure backward compatibility, when all operands in an arithmetic expression are integer literals, PL/SQL treats the integer literals as if they were cast to PLS_INTEGER.

Cast Operations

A cast operation that coerces a PLS_INTEGER value to the SIMPLE_INTEGER data type makes no conversion if the source value is not NULL. If the source value is NULL, a run-time exception is raised.

A cast operation that coerces a SIMPLE_INTEGER value to the PLS_INTEGER data type makes no conversion. This operation always succeeds (no exception is raised).

Compiler Warnings

The compiler issues a warning in the following cases:

  • An operation mixes SIMPLE_INTEGER values with values of other numeric types.

  • A SIMPLE_INTEGER value is passed as a parameter, a bind, or a define where a PLS_INTEGER is expected.

BINARY_FLOAT and BINARY_DOUBLE Data Types

The BINARY_FLOAT and BINARY_DOUBLE data types represent single-precision and double-precision IEEE 754-format floating-point numbers, respectively.

A BINARY_FLOAT literal ends with f (for example, 2.07f). A BINARY_DOUBLE literal ends with d (for example, 3.000094d).

BINARY_FLOAT and BINARY_DOUBLE computations do not raise exceptions; therefore, you must check the values that they produce for conditions such as overflow and underflow, using the predefined constants listed and described in Table 3-5. For example:

SELECT COUNT(*)
  FROM employees
    WHERE salary < BINARY_FLOAT_INFINITY;

Table 3-5 Predefined PL/SQL BINARY_FLOAT and BINARY_DOUBLE ConstantsFoot 1 

Constant Description

BINARY_FLOAT_NANFootref 1

BINARY_FLOAT value for which the condition IS NAN (not a number) is true

BINARY_FLOAT_INFINITYFootref 1

Single-precision positive infinity

BINARY_FLOAT_MAX_NORMALFootref 1

Maximum normal BINARY_FLOAT value

BINARY_FLOAT_MIN_NORMALFootref 1

Minimum normal BINARY_FLOAT value

BINARY_FLOAT_MAX_SUBNORMALFootref 1

Maximum subnormal BINARY_FLOAT value

BINARY_FLOAT_MIN_SUBNORMALFootref 1

Minimum subnormal BINARY_FLOAT value

BINARY_DOUBLE_NANFootref 1

BINARY_DOUBLE value for which the condition IS NAN (not a number) is true

BINARY_DOUBLE_INFINITYFootref 1

Double-precision positive infinity

BINARY_DOUBLE_MAX_NORMAL

Maximum normal BINARY_DOUBLE value

BINARY_DOUBLE_MIN_NORMAL

Minimum normal BINARY_DOUBLE value

BINARY_DOUBLE_MAX_SUBNORMAL

Maximum subnormal BINARY_DOUBLE value

BINARY_DOUBLE_MIN_SUBNORMAL

Minimum subnormal BINARY_DOUBLE value


Footnote 1 Also predefined by SQL

In the IEEE-754 standard, subnormal ranges of values are intended to reduce problems caused by underflow to zero.

BINARY_FLOAT and BINARY_DOUBLE data types are primarily for high-speed scientific computation, as explained in Writing Computation-Intensive PL/SQL Programs.

See Also:

Guidelines for Overloading with Numeric Types, for information about writing libraries that accept different numeric types

SIMPLE_FLOAT and SIMPLE_DOUBLE are predefined subtypes of the BINARY_FLOAT and BINARY_DOUBLE data types, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.

You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness, SIMPLE_FLOAT and SIMPLE_DOUBLE provide significantly better performance than BINARY_FLOAT and BINARY_DOUBLE when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_FLOAT and SIMPLE_DOUBLE values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

NUMBER Data Type

The NUMBER data type stores fixed-point or floating-point numbers with absolute values in the range 1E-130 up to (but not including) 1.0E126. A NUMBER variable can also represent 0.

Oracle recommends using only NUMBER literals and results of NUMBER computations that are within the specified range. Otherwise, the following happen:

  • Any value that is too small is rounded to zero.

  • A literal value that is too large causes a compilation error.

  • A computation result that is too large is undefined, causing unreliable results and possibly run-time errors.

A NUMBER value has both precision (its total number of digits) and scale (the number of digits to the right of the decimal point).

The syntax for specifying a fixed-point NUMBER is:

NUMBER(precision, scale)

For example:

NUMBER(8,2)

For an integer, the scale is zero. The syntax for specifying an integer NUMBER is:

NUMBER(precision)

For example:

NUMBER(2)

In a floating-point number, the decimal point can float to any position. The syntax for specifying a floating-point NUMBER is:

NUMBER

Both precision and scale must be integer literals, not constants or variables.

For precision, the maximum value is 38. The default value is 39 or 40, or the maximum for your system, whichever is least.

For scale, the minimum and maximum values are -84 and 127, respectively. The default value is zero.

Scale determines where rounding occurs. For example, a value whose scale is 2 is rounded to the nearest hundredth (3.454 becomes 3.45 and 3.456 becomes 3.46). A negative scale causes rounding to the left of the decimal point. For example, a value whose scale is -3 is rounded to the nearest thousand (34462 becomes 34000 and 34562 becomes 35000). A value whose scale is 0 is rounded to the nearest integer (3.4562 becomes 3 and 3.56 becomes 4).

For more information about the NUMBER data type, see Oracle Database SQL Language Reference.

Table 3-6 lists the predefined subtypes of the NUMBER data type and describes the data they store.

Table 3-6 Predefined Subtypes of NUMBER Data Type

Data Type Description

DEC, DECIMAL, or NUMERIC

Fixed-point NUMBER with maximum precision of 38 decimal digits

DOUBLE PRECISION or FLOAT

Floating-point NUMBER with maximum precision of 126 binary digits (approximately 38 decimal digits)

INT, INTEGER, or SMALLINT

Integer with maximum precision of 38 decimal digits

REAL

Floating-point NUMBER with maximum precision of 63 binary digits (approximately 18 decimal digits)


Predefined PL/SQL Character Data Types and Subtypes

Character data types let you store alphanumeric values that represent single characters or strings of characters, which you can manipulate. Table 3-7 describes the predefined PL/SQL character types and describes the data they store.

Table 3-7 Predefined PL/SQL Character Data TypesFoot 1 

Data Type Data Description

CHAR

Fixed-length character string with maximum size of 32,767 bytes

VARCHAR2

Variable-length character string with maximum size of 32,767 bytes

RAW

Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL

NCHAR

Fixed-length national character string with maximum size of 32,767 bytes

NVARCHAR2

Variable-length national character string with maximum size of 32,767 bytes

LONGFootref 1

Variable-length character string with maximum size of 32,760 bytes

LONG RAWFootref 1

Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL

ROWIDFootref 1

Physical row identifier, the address of a row in an ordinary table

UROWID

Universal row identifier (physical, logical, or foreign row identifier)


Footnote 1 Supported only for backward compatibility with existing applications

Topics:

CHAR and VARCHAR2 Data Types

The CHAR and VARCHAR2 data types store fixed-length and variable-length character strings, respectively. All string literals have data type CHAR.

How CHAR and VARCHAR2 data is represented internally depends on the database character set specified with the CHARACTER SET clause of the CREATE DATABASE statement, which is described in Oracle Database SQL Language Reference.

The syntax for specifying a CHAR or VARCHAR2 data item is:

[ CHAR | VARCHAR2 ] [( maximum_size [ CHAR | BYTE ] )]

For example:

CHAR
VARCHAR2
CHAR(10 CHAR)
VARCHAR2(32 BYTE)

The maximum_size must be an integer literal in the range 1..32767, not a constant or variable. The default value is one.

The default size unit (CHAR or BYTE) is determined by the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL subprogram is compiled, the setting of this parameter is recorded, so that the same setting is used when the subprogram is recompiled after being invalidated. For more information about NLS_LENGTH_SEMANTICS, see Oracle Database Reference.

The maximum size of a CHAR or VARCHAR2 data item is 32,767 bytes, whether you specify maximum_size in characters or bytes. The maximum number of characters in a CHAR or VARCHAR2 data item depends on how the character set is encoded. For a single-byte character set, the maximum size of a CHAR or VARCHAR2 data item is 32,767 characters. For an n-byte character set, the maximum size of a CHAR or VARCHAR2 data item is 32,767/n characters, rounded down to the nearest integer. For a multiple-byte character set, specify maximum_size in characters to ensure that a CHAR(n) or VARCHAR2(n) variable can store n multiple-byte characters.

If the character value that you assign to a character variable is longer than the maximum size of the variable, PL/SQL does not truncate the value or strip trailing blanks; it stops the assignment and raises the predefined exception VALUE_ERROR.

For example, given the declaration:

acronym CHAR(4);

the following assignment raises VALUE_ERROR:

acronym := 'SPCA ';  -- note trailing blank

If the character value that you insert into a database column is longer than the defined width of the column, PL/SQL does not truncate the value or strip trailing blanks; it stops the insertion and raises an exception.

To strip trailing blanks from a character value before assigning it to a variable or inserting it into a database column, use the built-in function RTRIM. For example, given the preceding declaration, the following assignment does not raise an exception:

acronym := RTRIM('SPCA ');  -- note trailing blank

For the syntax of RTRIM, see Oracle Database SQL Language Reference.

Differences Between CHAR and VARCHAR2 Data Types

CHAR and VARCHAR2 data types differ in the following:

Predefined Subtypes of Character Data Types

The CHAR data type has one predefined subtype, CHARACTER. The VARCHAR2 data type has two predefined subtypes, VARCHAR and STRING. Each of these subtypes has the same range of values as its base type, and can be used instead of its base type for compatibility with ANSI/ISO and IBM types.

Note:

In a future PL/SQL release, to accommodate emerging SQL standards, VARCHAR might become a separate data type, no longer synonymous with VARCHAR2.
Memory Allocation for Character Variables

For a CHAR variable, or for a VARCHAR2 variable whose maximum size is less than 2,000 bytes, PL/SQL allocates enough memory for the maximum size at compile time. For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example, if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables, PL/SQL allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

Blank-Padding Shorter Character Values

In each of the following situations, whether or not PL/SQL blank-pads the character value depends on the data type of the receiver:

  • The character value that you assign to a PL/SQL character variable is shorter than the maximum size of the variable.

  • The character value that you insert into a character database column is shorter than the defined width of the column.

  • The value that you retrieve from a character database column into a PL/SQL character variable is shorter than the maximum length of the variable.

If the data type of the receiver is CHAR, PL/SQL blank-pads the value to the maximum size. Information about trailing blanks in the original value is lost.

For example, the value assigned to last_name in the following statement has six trailing blanks, not only one:

last_name CHAR(10) := 'CHEN ';  -- note trailing blank

If the data type of the receiver is VARCHAR2, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, and no information is lost.

Comparing Character Values

You can use relational operators in Table 2-4 to compare character values. One character value is greater than another if it follows it in the collating sequence used for the database character set. In the following example, the IF condition is TRUE:

SQL> DECLARE
  2    last_name1 VARCHAR2(10) := 'COLES';
  3    last_name2 VARCHAR2(10) := 'COLEMAN';
  4  BEGIN
  5    IF last_name1 > last_name2 THEN
  6      DBMS_OUTPUT.PUT_LINE
  7        (last_name1 || ' is greater than ' || last_name2);
  8    ELSE
  9      DBMS_OUTPUT.PUT_LINE
 10        (last_name2 || ' is greater than ' || last_name1 );
 11    END IF;
 12  END;
 13  /
COLES is greater than COLEMAN
 
PL/SQL procedure successfully completed.
 
SQL>

To be equal, two character values must have the same length.

If both values have data type CHAR, PL/SQL blank-pads the shorter value to the length of the longer value before comparing them. In Example 3-1, the IF condition is TRUE.

If either value has data type VARCHAR2, PL/SQL does not adjust their lengths before comparing them. In both Example 3-2 and Example 3-3, the IF condition is FALSE.

Example 3-1 Comparing Two CHAR Values

SQL> DECLARE
  2    last_name1 CHAR(5)  := 'BELLO';     -- no trailing blanks
  3    last_name2 CHAR(10) := 'BELLO   ';  -- trailing blanks
  4  BEGIN
  5    IF last_name1 = last_name2 THEN
  6      DBMS_OUTPUT.PUT_LINE
  7        (last_name1 || ' is equal to ' || last_name2);
  8    ELSE
  9      DBMS_OUTPUT.PUT_LINE
 10      (last_name2 || ' is not equal to ' || last_name1);
 11    END IF;
 12  END;
 13  /
BELLO is equal to BELLO
 
PL/SQL procedure successfully completed.
 
SQL> 

Example 3-2 Comparing Two VARCHAR2 Values

SQL> DECLARE
  2    last_name1 VARCHAR2(10) := 'DOW';     -- no trailing blanks
  3    last_name2 VARCHAR2(10) := 'DOW   ';  -- trailing blanks
  4  BEGIN
  5    IF last_name1 = last_name2 THEN
  6      DBMS_OUTPUT.PUT_LINE
  7        (last_name1 || ' is equal to ' || last_name2 );
  8    ELSE
  9      DBMS_OUTPUT.PUT_LINE
 10      (last_name2 || ' is not equal to ' || last_name1);
 11    END IF;
 12  END;
 13  /
DOW    is not equal to DOW
 
PL/SQL procedure successfully completed.
 
SQL>

Example 3-3 Comparing CHAR Value and VARCHAR2 Value

SQL> DECLARE
  2    last_name1 VARCHAR2(10) := 'STAUB';
  3    last_name2 CHAR(10)     := 'STAUB';  -- PL/SQL blank-pads value
  4  BEGIN
  5    IF last_name1 = last_name2 THEN
  6      DBMS_OUTPUT.PUT_LINE
  7        (last_name1 || ' is equal to ' || last_name2);
  8    ELSE
  9      DBMS_OUTPUT.PUT_LINE
 10        (last_name2 || ' is not equal to ' || last_name1 );
 11    END IF;
 12  END;
 13  /
STAUB      is not equal to STAUB
 
PL/SQL procedure successfully completed.
 
SQL>
Maximum Sizes of Values Inserted into Character Database Columns

The largest CHAR value that you can insert into a CHAR database column is 2,000 bytes.

The largest VARCHAR2 value that you can insert into a VARCHAR2 database column is 4,000 bytes.

You can insert any CHAR or VARCHAR2 value into a LONG database column, because the maximum width of a LONG column is 2,147,483,648 bytes (2 GB). However, you cannot retrieve a value longer than 32,767 bytes from a LONG column into a CHAR or VARCHAR2 variable. (The LONG data type is supported only for backward compatibility with existing applications. For more information, see LONG and LONG RAW Data Types.)

RAW Data Type

The RAW data type stores binary or byte strings, such as sequences of graphics characters or digitized pictures. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data. Oracle Net does no character set conversions when you transmit raw data from one system to another.

The syntax for specifying a RAW data item is:

RAW (maximum_size)

For example:

RAW(256)

The maximum_size, in bytes, must be an integer literal in the range 1..32767, not a constant or variable. The default value is one.

The largest RAW value that you can insert into a RAW database column is 2,000 bytes.

You can insert any RAW value into a LONG RAW database column, because the maximum width of a LONG RAW column is 2,147,483,648 bytes (2 GB). However, you cannot retrieve a value longer than 32,767 bytes from a LONG RAW column into a RAW variable. (The LONG RAW data type is supported only for backward compatibility with existing applications. For more information, see LONG and LONG RAW Data Types.)

NCHAR and NVARCHAR2 Data Types

The NCHAR and NVARCHAR2 data types store fixed-length and variable-length national character strings, respectively.

National character strings are composed of characters from the national character set, which is used to represent languages that have thousands of characters, each of which requires two or three bytes (Japanese, for example).

How NCHAR and NVARCHAR2 data is represented internally depends on the national character set specified with the NATIONAL CHARACTER SET clause of the CREATE DATABASE statement, which is described in Oracle Database SQL Language Reference.

Topics:

AL16UTF16 and UTF8 Encodings

The national character set represents data as Unicode, using either the AL16UTF16 or UTF8 encoding. Table 3-8 compares AL16UTF16 and UTF8 encodings.

Table 3-8 Comparison of AL16UTF16 and UTF8 Encodings

Encoding Character Size (Bytes) Advantage Disadvantage

AL16UTF16 (default)

2

Easy to calculate string lengths, which you must do in order to avoid truncation errors when mixing programming languages.

Strings composed mostly of ASCII or EBCDIC characters take more space than necessary.

UTF8

1, 2, or 3

If most characters use only one byte, you can fit more characters into a variable or table column.

Possibility of truncation errors when transferring the data to a buffer measured in bytes.


For maximum reliability, Oracle recommends using the default AL16UTF16 encoding wherever practical. To use UTF8 encoding, specify it in the NATIONAL CHARACTER SET clause of the CREATE DATABASE statement.

To determine how many bytes a Unicode string needs, use the built-in function LENGTHB.

For more information about the NATIONAL CHARACTER SET clause of the CREATE DATABASE statement and the LENGTHB function, see Oracle Database SQL Language Reference.

For more information about the national character set, see Oracle Database Globalization Support Guide.

NCHAR Data Type

The NCHAR data type stores fixed-length national character strings. Because this type can always accommodate multiple-byte characters, you can use it to store any Unicode character data.

The syntax for specifying an NCHAR data item is:

NCHAR [(maximum_size)]

For example:

NCHAR
NCHAR(100)

The maximum_size must be an integer literal, not a constant or variable. It represents the maximum number of characters, not the maximum number of bytes, which is 32,767. The largest maximum_size you can specify is 32767/2 with AL16UTF16 encoding and 32767/3 with UTF8 encoding. The default value is one.

The largest NCHAR value that you can insert into an NCHAR database column is 2,000 bytes.

If the NCHAR value is shorter than the defined width of the NCHAR column, PL/SQL blank-pads the value to the defined width.

You can interchange CHAR and NCHAR values in statements and expressions. It is always safe to convert a CHAR value to an NCHAR value, but converting an NCHAR value to a CHAR value might cause data loss if the character set for the CHAR value cannot represent all the characters in the NCHAR value. Such data loss usually results in characters that look like question marks (?).

NVARCHAR2 Data Type

The NVARCHAR2 data type stores variable-length national character strings. Because this type can always accommodate multiple-byte characters, you can use it to store any Unicode character data.

The syntax for specifying an NVARCHAR2 data item is:

NVARCHAR2 (maximum_size)

For example:

NVARCHAR2(300)

The maximum_size must be an integer literal, not a constant or variable. It represents the maximum number of characters, not the maximum number of bytes, which is 32,767. The largest maximum_size you can specify is 32767/2 with AL16UTF16 encoding and 32767/3 with UTF8 encoding. The default value is one.

The largest NVARCHAR2 value that you can insert into an NVARCHAR2 database column is 4,000 bytes.

You can interchange VARCHAR2 and NVARCHAR2 values in statements and expressions. It is always safe to convert a VARCHAR2 value to an NVARCHAR2 value, but converting an NVARCHAR2 value to a VARCHAR2 value might cause data loss if the character set for the VARCHAR2 value cannot represent all the characters in the NVARCHAR2 value. Such data loss usually results in characters that look like question marks (?).

LONG and LONG RAW Data Types

Note:

The LONG and LONG RAW data types are supported only for backward compatibility with existing applications. For new applications, use CLOB or NCLOB instead of LONG, and BLOB or BFILE instead of LONG RAW. Oracle recommends that you also replace existing LONG and LONG RAW data types with LOB data types. See Predefined PL/SQL Large Object (LOB) Data Types.

The LONG data type stores variable-length character strings. The LONG data type is like the VARCHAR2 data type, except that the maximum size of a LONG value is 32,760 bytes (as opposed to 32,767 bytes).

The LONG RAW data type stores binary or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The maximum size of a LONG RAW value is 32,760 bytes.

Because the maximum width of a LONG or LONG RAW database column is 2,147,483,648 bytes (2 GB), you can insert any LONG value into a LONG column and any LONG RAW value into a LONG RAW column. However, you cannot retrieve a value longer than 32,760 bytes from a LONG column into a LONG variable, or from a LONG RAW column into a LONG RAW variable.

LONG database columns can store text, arrays of characters, and even short documents.

See Also:

Oracle Database SQL Language Reference for information about referencing LONG columns in SQL statements

ROWID and UROWID Data Types

Internally, every database table has a ROWID pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row. A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. The ROWID data type can store only physical rowids, while the UROWID (universal rowid) data type can store physical, logical, or foreign (not database) rowids.

Note:

The ROWID data type is supported only for backward compatibility with existing applications. For new applications, use the UROWID data type.

Physical rowids are useful for fetching across commits, as in Example 6-42.

When you retrieve a rowid into a ROWID variable, you can use the built-in function ROWIDTOCHAR, which converts the binary value into an 18-byte character string. Conversely, the function CHARTOROWID converts a ROWID character string into a rowid. If the conversion fails because the character string does not represent a valid rowid, PL/SQL raises the predefined exception SYS_INVALID_ROWID. This also applies to implicit conversions.

To convert between UROWID variables and character strings, use regular assignment statements without any function call. The values are implicitly converted between UROWID and character types.

See Also:

Predefined PL/SQL BOOLEAN Data Type

The BOOLEAN data type stores logical values, which you can use in logical operations. The logical values are the Boolean values TRUE and FALSE and the value NULL.

The syntax for specifying an BOOLEAN data item is:

BOOLEAN

SQL has no data type equivalent to BOOLEAN; therefore you cannot use BOOLEAN variables or parameters in the following:

  • SQL statements

  • Built-in SQL functions (such as TO_CHAR)

  • PL/SQL functions invoked from SQL statements

You cannot insert the value TRUE or FALSE into a database column. You cannot retrieve the value of a database column into a BOOLEAN variable.

To represent BOOLEAN values in output, use IF-THEN or CASE constructs to translate BOOLEAN values into another type (for example, 0 or 1, 'Y' or 'N', 'true' or 'false').

Predefined PL/SQL Datetime and Interval Data Types

The data types in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has a date and time data type stores values called datetimes. A variable that has an interval data type stores values called intervals. A datetime or interval consists of fields, which determine its value. The following list shows the valid values for each field:

Field Name Valid Datetime Values Valid Interval Values
YEAR -4712 to 9999 (excluding year 0) Any nonzero integer
MONTH 01 to 12 0 to 11
DAY 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale) Any nonzero integer
HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
SECOND 00 to 59.9(n), where 9(n) is the precision of time fractional seconds 0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR -12 to 14 (range accommodates daylight savings time changes) Not applicable
TIMEZONE_MINUTE 00 to 59 Not applicable
TIMEZONE_REGION Found in the dynamic performance view V$TIMEZONE_NAMES Not applicable
TIMEZONE_ABBR Found in the dynamic performance view V$TIMEZONE_NAMES Not applicable

Except for TIMESTAMP WITH LOCAL TIMEZONE, these types are all part of the SQL92 standard. For information about datetime and interval format models, literals, time-zone names, and SQL functions, see Oracle Database SQL Language Reference.

Topics:

DATE Data Type

You use the DATE data type to store fixed-length datetimes, which 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.

To compare dates for equality, regardless of the time portion of each date, use the function result TRUNC(date_variable) in comparisons, GROUP BY operations, and so on.

To find just the time portion of a DATE variable, subtract the date portion: date_variable - TRUNC(date_variable).

Valid dates range from January 1, 4712 BC to December 31, 9999 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 the 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. In arithmetic expressions, PL/SQL interprets integer literals as days. For example, SYSDATE + 1 signifies the same time tomorrow.

TIMESTAMP Data Type

The data type TIMESTAMP, which extends the data type DATE, stores the year, month, day, hour, minute, and second. The syntax is:

TIMESTAMP[(precision)

where the optional parameter precision specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..9. The default is 6.

The default timestamp format is set by the Oracle initialization parameter NLS_TIMESTAMP_FORMAT.

Example 3-4 declares a variable of type TIMESTAMP and assigns a literal value to it. The fractional part of the seconds field is 0.275.

Example 3-4 Assigning a Literal Value to a TIMESTAMP Variable

SQL> DECLARE
  2    checkout TIMESTAMP(3);
  3  BEGIN
  4    checkout := '22-JUN-2004 07:48:53.275';
  5    DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout));
  6  END;
  7  /
22-JUN-04 07.48.53.275 AM
 
PL/SQL procedure successfully completed.
 
SQL>

In Example 3-5, the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN functions are used to manipulate TIMESTAMPs.

Example 3-5 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

SQL> DECLARE
  2    right_now  TIMESTAMP;
  3    yesterday  TIMESTAMP;
  4    sometime   TIMESTAMP;
  5    scn1       INTEGER;
  6    scn2       INTEGER;
  7    scn3       INTEGER;
  8  BEGIN
  9    right_now := SYSTIMESTAMP;
 10    scn1 := TIMESTAMP_TO_SCN(right_now);
 11    DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1);
 12  
 13    yesterday := right_now - 1;
 14    scn2 := TIMESTAMP_TO_SCN(yesterday);
 15    DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2);
 16  
 17    -- Find arbitrary SCN between yesterday and today
 18  
 19    scn3 := (scn1 + scn2) / 2;
 20    sometime := SCN_TO_TIMESTAMP(scn3);
 21    DBMS_OUTPUT.PUT_LINE
 22      ('SCN ' || scn3 || ' was in effect at ' || TO_CHAR(sometime));
 23  END;
 24  /
Current SCN is 3945848
SCN from yesterday is 3899547
SCN 3922698 was in effect at 03-JAN-08 10.00.06.000000 PM
 
PL/SQL procedure successfully completed.
 
SQL>

TIMESTAMP WITH TIME ZONE Data Type

The data type TIMESTAMP WITH TIME ZONE, which extends the data type TIMESTAMP, includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC,) formerly Greenwich Mean Time (GMT). The syntax is:

TIMESTAMP[(precision)] WITH TIME ZONE

where the optional parameter precision specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..9. The default is 6.

The default timestamp with time zone format is set by the Oracle initialization parameter NLS_TIMESTAMP_TZ_FORMAT.

Example 3-6 declares a variable of type TIMESTAMP WITH TIME ZONE and assign a literal value to it. The time-zone displacement is +02:00.

Example 3-6 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable

SQL> DECLARE
  2    logoff TIMESTAMP(3) WITH TIME ZONE;
  3  BEGIN
  4    logoff := '10-OCT-2004 09:42:37.114 AM +02:00';
  5    DBMS_OUTPUT.PUT_LINE (TO_CHAR(logoff));
  6  END;
  7  /
10-OCT-04 09.42.37.114 AM +02:00
 
PL/SQL procedure successfully completed.
 
SQL>

You can also specify the time zone by using a symbolic name. The specification can include a long form such as 'US/Pacific', an abbreviation such as 'PDT', or a combination. For example, the following literals all represent the same time. The third form is most reliable because it specifies the rules to follow at the point when switching to daylight savings time.

TIMESTAMP '15-APR-2004 8:00:00 -8:00'
TIMESTAMP '15-APR-2004 8:00:00 US/Pacific'
TIMESTAMP '31-OCT-2004 01:30:00 US/Pacific PDT'

You can find the available names for time zones in the TIMEZONE_REGION and TIMEZONE_ABBR columns of the static data dictionary view V$TIMEZONE_NAMES.

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of their time-zone displacements. For example, the following two values are considered identical because, in UTC, 8:00 AM Pacific Standard Time is the same as 11:00 AM Eastern Standard Time:

'29-AUG-2004 08:00:00 -8:00'
'29-AUG-2004 11:00:00 -5:00'

TIMESTAMP WITH LOCAL TIME ZONE Data Type

The data type TIMESTAMP WITH LOCAL TIME ZONE, which extends the data type TIMESTAMP, includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. You can also use named time zones, as with TIMESTAMP WITH TIME ZONE.

The syntax is:

TIMESTAMP[(precision)] WITH LOCAL TIME ZONE

where the optional parameter precision specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..9. The default is 6.

This data type differs from TIMESTAMP WITH TIME ZONE in that when you insert a value into a database column, the value is normalized to the database time zone, and the time-zone displacement is not stored in the column. When you retrieve the value, Oracle returns it in your local session time zone.

Both Example 3-7 and Example 3-8 declare a variable of type TIMESTAMP WITH LOCAL TIME ZONE and assign it a value. The value in Example 3-7 is an appropriate local time, but the value in Example 3-8 includes a time zone displacement, which causes an error.

Example 3-7 Correct Assignment to TIMESTAMP WITH LOCAL TIME ZONE

SQL> DECLARE
  2    logoff  TIMESTAMP(3) WITH LOCAL TIME ZONE;
  3  BEGIN
  4    logoff := '10-OCT-2004 09:42:37.114 AM ';
  5    DBMS_OUTPUT.PUT_LINE(TO_CHAR(logoff));
  6  END;
  7  /
10-OCT-04 09.42.37.114 AM
 
PL/SQL procedure successfully completed.
 
SQL>

Example 3-8 Incorrect Assigment to TIMESTAMP WITH LOCAL TIME ZONE

SQL> DECLARE
  2    logoff  TIMESTAMP(3) WITH LOCAL TIME ZONE;
  3  BEGIN
  4    logoff := '10-OCT-2004 09:42:37.114 AM +02:00';
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 4
 
SQL>

INTERVAL YEAR TO MONTH Data Type

Use the data type INTERVAL YEAR TO MONTH to store and manipulate intervals of years and months. The syntax is:

INTERVAL YEAR[(precision)] TO MONTH

where precision specifies the number of digits in the years field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..4. The default is 2.

Example 3-9 declares a variable of type INTERVAL YEAR TO MONTH and assigns a value of 101 years and 3 months to it, in three different ways.

Example 3-9 Assigning Literals to an INTERVAL YEAR TO MONTH Variable

SQL> DECLARE
  2    lifetime  INTERVAL YEAR(3) TO MONTH;
  3  BEGIN
  4    lifetime := INTERVAL '101-3' YEAR TO MONTH;  -- Interval literal
  5  
  6    lifetime := '101-3';  -- Implicit conversion from character type
  7  
  8    lifetime := INTERVAL '101' YEAR;  -- Specify only years
  9    lifetime := INTERVAL '3' MONTH;   -- Specify only months
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL>

INTERVAL DAY TO SECOND Data Type

You use the data type INTERVAL DAY TO SECOND to store and manipulate intervals of days, hours, minutes, and seconds. The syntax is:

INTERVAL DAY[(leading_precision)
  TO SECOND (fractional_seconds_precision)

where leading_precision and fractional_seconds_precision specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0..9. The defaults are 2 and 6, respectively.

Example 3-10 declares a variable of type INTERVAL DAY TO SECOND and assigns a value to it.

Example 3-10 Assigning Literals to an INTERVAL DAY TO SECOND Variable

SQL> DECLARE
  2    lag_time  INTERVAL DAY(3) TO SECOND(3);
  3  BEGIN
  4    lag_time := '7 09:24:30';
  5  
  6    IF lag_time > INTERVAL '6' DAY THEN
  7      DBMS_OUTPUT.PUT_LINE ('Greater than 6 days');
  8    ELSE
  9      DBMS_OUTPUT.PUT_LINE ('Less than 6 days');
 10    END IF;
 11  END;
 12  /
Greater than 6 days
 
PL/SQL procedure successfully completed.
 
SQL>

Datetime and Interval Arithmetic

PL/SQL lets you construct datetime and interval expressions. The following list shows the operators that you can use in such expressions:

Operand 1 Operator Operand 2 Result Type
datetime + interval datetime
datetime - interval datetime
interval + datetime datetime
datetime - datetime interval
interval + interval interval
interval - interval interval
interval * numeric interval
numeric * interval interval
interval / numeric interval

See Also:

Oracle Database SQL Language Reference for information about using SQL functions to perform arithmetic operations on datetime values

Avoiding Truncation Problems Using Date and Time Subtypes

The default precisions for some of the date and time types are less than the maximum precision. For example, the default for DAY TO SECOND is DAY(2) TO SECOND(6), while the highest precision is DAY(9) TO SECOND(9). To avoid truncation when assigning variables and passing subprogram parameters of these types, you can declare variables and subprogram parameters of the following subtypes, which use the maximum values for precision:

TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED

Predefined PL/SQL Large Object (LOB) Data Types

Large object (LOB) data types reference large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. LOB data types allow efficient, random, piecewise access to this data. Predefined PL/SQL LOB data types are listed and described in Table 3-9.

Table 3-9 Predefined PL/SQL Large Object (LOB) Data Types

Data Type Description Size

BFILE

Used to store large binary objects in operating system files outside the database.

System-dependent. Cannot exceed 4 gigabytes (GB).

BLOB

Used to store large binary objects in the database.

8 to 128 terabytes (TB)

CLOB

Used to store large blocks of character data in the database.

8 to 128 TB

NCLOB

Used to store large blocks of NCHAR data in the database.

8 to 128 TB


LOB Locators

To reference a large object that is stored in an external file, a LOB data type uses a LOB locator, which is stored in an external file, either inside the row (inline) or outside the row (out-of-line). In the external file, LOB locators are in columns of the types BFILE, BLOB, CLOB, and NCLOB.

PL/SQL operates on large objects through their LOB locators. For example, when you select a BLOB column value, PL/SQL returns only its locator. If PL/SQL returned the locator during a transaction, the locator includes a transaction ID, so you cannot use that locator to update that large object in another transaction. Likewise, you cannot save a locator during one session and then use it in another session.

Differences Between LOB Data Types and LONG and LONG RAW Data Types

LOB data types differ from LONG and LONG RAW data types in the following ways:

Difference LOB Data Types LONG and LONG RAW Data Types
Support Functionality enhanced in every release. Functionality static. Supported only for backward compatibility with existing applications.
Maximum size 8 to 128 TB 2 GB
Access Random Sequential
Can be object type attribute BFILE, BLOB, CLOB: YesNCLOB: No No

Topics:

BFILE Data Type

You use the BFILE data type 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.

BFILEs are read-only, so you cannot modify them. Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.

BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, which is system dependent.

BLOB Data Type

You use the BLOB data type to store large binary objects in the database, inline or out-of-line. Every BLOB variable stores a locator, which points to a large binary object.

BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.

CLOB Data Type

You use the CLOB data type to store large blocks of character data in the database, inline or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data.

CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions.

NCLOB Data Type

You use the NCLOB data type to store large blocks of NCHAR data in the database, inline or out-of-line. Both fixed-width and variable-width character sets are supported. Every NCLOB variable stores a locator, which points to a large block of NCHAR data.

NCLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. NCLOB locators can span transactions (for reads only), but they cannot span sessions.

User-Defined PL/SQL Subtypes

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values. Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables.

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

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);  -- allows only whole numbers

The subtype CHARACTER specifies the same set of values as its base type CHAR, so CHARACTER is an unconstrained subtype. But, the subtype INTEGER specifies only a subset of the values of its base type NUMBER, so INTEGER is a constrained subtype.

Topics:

Defining Subtypes

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

SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

where subtype_name is a type specifier used in subsequent declarations, base_type is any scalar or user-defined PL/SQL data type, and constraint applies only to base types that can specify precision and scale or a maximum size. A default value is not permitted; see Example 3-14.

Examples:

SQL> DECLARE
  2     SUBTYPE BirthDate IS DATE NOT NULL;         -- Based on DATE type
  3     SUBTYPE Counter IS NATURAL;                 -- Based on NATURAL subtype
  4  
  5     TYPE NameList IS TABLE OF VARCHAR2(10);
  6     SUBTYPE DutyRoster IS NameList;             -- Based on TABLE type
  7  
  8     TYPE TimeRec IS RECORD (minutes INTEGER, hours INTEGER);
  9     SUBTYPE FinishTime IS TimeRec;                -- Based on RECORD type
 10     SUBTYPE ID_Num IS employees.employee_id%TYPE; -- Based on column type
 11  BEGIN
 12    NULL;
 13  END;
 14  /
 
PL/SQL procedure successfully completed.
 
SQL>

You can use %TYPE or %ROWTYPE to specify the base type. When %TYPE provides the data type of a database column, the subtype inherits the size constraint (if any) of the column. The subtype does not inherit other kinds of column constraints, such as NOT NULL or check constraint, or the default value, as shown in Example 3-15. For more information, see Using the %TYPE Attribute and Using the %ROWTYPE Attribute.

Using Subtypes

After defining a subtype, you can declare items of that type. The subtype name indicates the intended use of the variable. You can constrain a user-defined subtype when declaring variables of that type. For example:

SQL> DECLARE
  2    SUBTYPE Counter IS NATURAL;
  3    rows Counter;
  4  
  5    SUBTYPE Accumulator IS NUMBER;
  6    total Accumulator(7,2);
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>

Subtypes can increase reliability by detecting out-of-range values. Example 3-11 restricts the subtype pinteger to storing integers in the range -9..9. When the program tries to store a number outside that range in a pinteger variable, PL/SQL raises an exception.

Example 3-11 Using Ranges with Subtypes

SQL> DECLARE
  2    v_sqlerrm VARCHAR2(64);
  3  
  4    SUBTYPE pinteger IS PLS_INTEGER RANGE -9..9;
  5    y_axis pinteger;
  6  
  7    PROCEDURE p (x IN pinteger) IS
  8      BEGIN
  9        DBMS_OUTPUT.PUT_LINE (x);
 10      END p;
 11  
 12  BEGIN
 13    y_axis := 9;
 14    p(10);
 15  
 16  EXCEPTION
 17    WHEN OTHERS THEN
 18      v_sqlerrm := SUBSTR(SQLERRM, 1, 64);
 19      DBMS_OUTPUT.PUT_LINE('Error: ' || v_sqlerrm);
 20  END;
 21  /
Error: ORA-06502: PL/SQL: numeric or value error
 
PL/SQL procedure successfully completed.
 
SQL> 

Topics:

Type Compatibility with Subtypes

An unconstrained subtype is interchangeable with its base type. Example 3-12 assigns the value of amount to total without conversion.

Example 3-12 Type Compatibility with the NUMBER Data Type

SQL> DECLARE
  2    SUBTYPE Accumulator IS NUMBER;
  3    amount  NUMBER(7,2);
  4    total   Accumulator;
  5  BEGIN
  6    amount := 10000.50;
  7    total  := amount;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL>

Different subtypes are interchangeable if they have the same base type:

SQL> DECLARE
  2    SUBTYPE b1 IS BOOLEAN;
  3    SUBTYPE b2 IS BOOLEAN;
  4    finished  b1;
  5    debugging b2;
  6  BEGIN
  7    finished  := FALSE;
  8    debugging := finished;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>

Different subtypes are also interchangeable if their base types are in the same data type family. For example, the value of verb can be assigned to sentence:

SQL> DECLARE
  2    SUBTYPE Word IS CHAR(15);
  3    SUBTYPE Text IS VARCHAR2(1500);
  4    verb     Word;
  5    sentence Text(150);
  6  BEGIN
  7    verb := 'program';
  8    sentence := verb;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>

Constraints and Default Values with Subtypes

Example 3-13 shows to assign a default value to a subtype variable.

Example 3-13 Assigning Default Value to Subtype Variable

SQL> DECLARE
  2    SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
  3    verb  v_word  := 'verb';
  4    noun  v_word  := 'noun';
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE (UPPER(verb));
  7     DBMS_OUTPUT.PUT_LINE (UPPER(noun));
  8  END;
  9  /
VERB
NOUN
 
PL/SQL procedure successfully completed.
 
SQL>

In Example 3-14, the procedure enforces the NOT NULL constraint, but not the size constraint.

Example 3-14 Subtype Constraints Inherited by Subprograms

SQL> DECLARE
  2    SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
  3    verb    v_word       := 'run';
  4    noun    VARCHAR2(10) := NULL;
  5  
  6    PROCEDURE word_to_upper (w IN v_word) IS
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE (UPPER(w));
  9    END word_to_upper;
 10  
 11  BEGIN
 12    word_to_upper('more than ten characters');
 13    word_to_upper(noun);
 14  END;
 15  /
MORE THAN TEN CHARACTERS
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13
 
SQL>

As Example 3-15 shows, subtypes do not inherit the column constraints NOT NULL or CHECK, but they do inherit column size constraints.

Example 3-15 Column Constraints Inherited by Subtypes

SQL> CREATE TABLE employees_temp (
  2    empid NUMBER(6) NOT NULL PRIMARY KEY,
  3    deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid
  4      CHECK (deptid BETWEEN 100 AND 200),
  5    deptname VARCHAR2(30) DEFAULT 'Sales'
  6  );
 
Table created.
 
SQL> 
SQL> DECLARE
  2     SUBTYPE v_empid_subtype    IS employees_temp.empid%TYPE;
  3     SUBTYPE v_deptid_subtype   IS employees_temp.deptid%TYPE;
  4     SUBTYPE v_deptname_subtype IS employees_temp.deptname%TYPE;
  5     SUBTYPE v_emprec_subtype   IS employees_temp%ROWTYPE;
  6  
  7     v_empid    v_empid_subtype;
  8     v_deptid   v_deptid_subtype;
  9     v_deptname v_deptname_subtype;
 10     v_emprec   v_emprec_subtype;
 11  BEGIN
 12    v_empid  := NULL;         -- NULL constraint not inherited
 13    v_deptid := 50;           -- CHECK constraint not inherited
 14  
 15    v_emprec.empid  := NULL;  -- NULL constraint not inherited
 16    v_emprec.deptid := 50;    -- CHECK constraint not inherited
 17  
 18    DBMS_OUTPUT.PUT_LINE
 19      ('v_deptname: ' || v_deptname);  -- Default value not inherited
 20  
 21    DBMS_OUTPUT.PUT_LINE
 22      ('v_emprec.deptname: ' || v_emprec.deptname);
 23        -- Default value not inherited
 24    v_empid := 10000002;  -- NUMBER(6) constraint inherited
 25  END;
 26  /
v_deptname:
v_emprec.deptname:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 24
 
SQL>

PL/SQL Data Type Conversion

Sometimes it is necessary to convert a value from one data type to another. For example, to use a DATE value in a report, you must convert it to a character string. PL/SQL supports both explicit and implicit data type conversion.

For best reliability and maintainability, use explicit conversion. Implicit conversion is context-sensitive and not always predictable, and its rules might change in later software releases. Implicit conversion can also be slower than explicit conversion.

Topics:

Explicit Conversion

To explicitly convert values from one data type to another, you use built-in functions, which are described in Oracle Database SQL Language Reference. 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.

Explicit conversion can prevent errors or unexpected results. For example:

  • Using the concatenation operator (||) to concatenate a string and an arithmetic expression can produce an error, which you can prevent by using the TO_CHAR function to convert the arithmetic expression to a string before concatenation.

  • Relying on language settings in the database for the format of a DATE value can produce unexpected results, which you can prevent by using the TO_CHAR function and specifying the format that you want.

Implicit Conversion

Sometimes PL/SQL can convert a value from one data type to another automatically. This is called implicit conversion, and the data types are called compatible. When two data types are compatible, you can use a value of one type where a value of the other type is expected. For example, you can pass a numeric literal to a subprogram that expects a string value, and the subprogram receives the string representation of the number.

In Example 3-16, the CHAR variables start_time and finish_time store string values representing the number of seconds past midnight. The difference between those values can be assigned to the NUMBER variable elapsed_time, because PL/SQL converts the CHAR values to NUMBER values automatically.

Example 3-16 Implicit Conversion

SQL> DECLARE
  2    start_time   CHAR(5);
  3    finish_time  CHAR(5);
  4    elapsed_time NUMBER(5);
  5  BEGIN
  6     -- Get system time as seconds past midnight:
  7  
  8     SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.DUAL;
  9  
 10     -- Processing done here
 11  
 12     -- Get system time again:
 13  
 14     SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.DUAL;
 15  
 16     -- Compute and report elapsed time in seconds:
 17  
 18     elapsed_time := finish_time - start_time;
 19     DBMS_OUTPUT.PUT_LINE ('Elapsed time: ' || TO_CHAR(elapsed_time));
 20  END;
 21  /
Elapsed time: 0
 
PL/SQL procedure successfully completed.
 
SQL>

If you select a value from a column of one data type, and assign that value to a variable of another data type, PL/SQL converts the value to the data type of the variable. This happens, for example, when you select a DATE column value into a VARCHAR2 variable.

If you assign the value of a variable of one database type to a column of another database type, PL/SQL converts the value of the variable to the data type of the column.

If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use explicit conversion.

Table 3-10 shows which implicit conversions PL/SQL can do. However:

  • Table 3-10 lists only data types that have different representations.

    Types that have the same representation, such as PLS_INTEGER and BINARY_INTEGER, CLOB and NCLOB, CHAR and NCHAR, and VARCHAR and NVARCHAR2, can be substituted for each other.

  • It is your responsibility to ensure that specific values are convertible.

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

  • Regarding date, time, and interval data types:

    • Conversion rules for the DATE data type also apply to the datetime data types. However, because of their different internal representations, these types cannot always be converted to each other. For details about implicit conversions between datetime datatypes, see Oracle Database SQL Language Reference.

    • To implicitly convert a DATE value to a CHAR or VARCHAR2 value, PL/SQL invoks 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, invoke TO_CHAR explicitly with a format mask.

    • When you insert a CHAR or VARCHAR2 value into a DATE column, PL/SQL implicitly converts the CHAR or VARCHAR2 value to a DATE value by invoking the function TO_DATE, which expects its parameter to be in the default date format. To insert dates in other formats, invoke TO_DATE explicitly with a format mask.

  • Regarding LOB data types:

    • Converting between CLOB and NCLOB values can be expensive. To make clear that you intend this conversion, use the explicit conversion functions TO_CLOB and TO_NCLOB.

    • Implicit conversion between CLOB values and CHAR and VARCHAR2 values, and between BLOB values and RAW values, lets you use LOB data types in most SQL and PL/SQL statements and functions. However, to read, write, and do piecewise operations on LOB values, you must use DBMS_LOB package subprograms, which are described in Oracle Database PL/SQL Packages and Types Reference.

  • Regarding RAW and LONG RAW data types:

    • LONG RAW is supported only for backward compatibility with existing applications. For more information, see LONG and LONG RAW Data Types.

    • 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. PL/SQL does this by returning 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.

    • Conversion is also necessary when you insert a CHAR or VARCHAR2 value into a RAW or LONG RAW column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte; otherwise, PL/SQL raises an exception.

    • When a LONG value appears in a SQL statement, PL/SQL binds the LONG value as a VARCHAR2 value. However, if the length of the bound VARCHAR2 value exceeds the maximum width of a VARCHAR2 column (4,000 bytes), Oracle converts the bind type to LONG automatically, and then issues an error message because you cannot pass LONG values to a SQL function.

Table 3-10 Possible Implicit PL/SQL Data Type Conversions

From: To:








 

BLOB

CHAR

CLOB

DATE

LONG

NUMBER

PLS_INTEGER

RAW

UROWID

VARCHAR2

BLOB

             

Yes

   

CHAR

   

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

CLOB

 

Yes

             

Yes

DATE

 

Yes

   

Yes

       

Yes

LONG

 

Yes

         

Yes

 

Yes

NUMBER

 

Yes

   

Yes

 

Yes

   

Yes

PLS_INTEGER

 

Yes

   

Yes

Yes

     

Yes

RAW

Yes

Yes

   

Yes

       

Yes

UROWID

 

Yes

             

Yes

VARCHAR2

 

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes