PL/SQL User's Guide and Reference

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 scalar and composite datatypes. A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type contains values, called pointers, that designate other program objects.

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, date/time, or Boolean data, respectively.

Figure 2 - 1. Predefined Datatypes

This section discusses the scalar types; the composite types are discussed; the reference type is discussed in "Using Cursor Variables" .

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. For more information, see "PLS_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 (0 .. 2147483647)

NATURALN (0 .. 2147483647)

POSITIVE (1 .. 2147483647)

POSITIVEN (1 .. 2147483647)

You can use these subtypes when you want to restrict a variable to non-negative integer values. The subtypes NATURALN and POSITIVEN are predefined as NOT NULL. For more information about the NOT NULL constraint, see "Using NOT NULL"

NUMBER

You use the NUMBER datatype to store fixed or floating-point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding occurs. The syntax follows:

NUMBER[(precision, scale)]

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; the magnitude range is 1.0E-129 .. 9.99E125. If you do not specify the precision, it defaults to the maximum value supported by your system.

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

Subtypes

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

DECIMAL

DOUBLE PRECISION

INTEGER

NUMERIC

REAL

SMALLINT

FLOAT is another subtype of NUMBER. However, you cannot specify a scale for FLOAT variables; you can only specify a binary precision. The maximum precision of a FLOAT value is 126 binary digits, which is roughly equivalent to 38 decimal digits.

You can use these subtypes for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than NUMBER.

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 are both integer types with 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.

CHAR

You use the CHAR datatype to store fixed-length (blank-padded if necessary) 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. If you do not specify the maximum length, it defaults to 1.

Remember, you specify the maximum length of a CHAR(n) variable in bytes, not characters. So, if a CHAR(n) variable stores multi-byte characters, its maximum length is less than n characters.

Although the maximum length of a CHAR(n) variable is 32767 bytes, the maximum width of a CHAR database column is 255 bytes. Therefore, you cannot insert values longer than 255 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 select a value longer than 32767 bytes from a LONG column into a CHAR(n) variable.

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 select 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 Oracle7 Server 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 character data, except that PL/SQL does not interpret raw data. Likewise, Oracle does no character set conversions (from 7-bit ASCII to EBCDIC Code Page 500, for example) 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.

Although the maximum length of a RAW variable is 32767 bytes, the maximum width of a RAW database column is 255 bytes. Therefore, you cannot insert values longer than 255 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 select 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 select a value longer than 32760 bytes from a LONG RAW column into a LONG RAW variable.

ROWID

Internally, every Oracle database table has a ROWID pseudocolumn, which stores binary values called rowids. Rowids uniquely identify rows and provide the fastest way to access particular rows. You use the ROWID datatype to store rowids in a readable format. The maximum length of a ROWID variable is 256 bytes.

When you select or fetch a rowid into a ROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string and returns it in the format

BBBBBBBB.RRRR.FFFF

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file.

These numbers are hexadecimal. For example, the rowid

0000000E.000A.0007

points to the 11th row in the 15th block in the 7th database file.

Typically, ROWID variables are compared to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor. For an example, see "Fetching Across Commits" .

VARCHAR2

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set.

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.

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.

Although the maximum length of a VARCHAR2(n) variable is 32767 bytes, the maximum width of a VARCHAR2 database column is 2000 bytes. Therefore, you cannot insert values longer than 2000 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 select a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.

Some important semantic differences between the CHAR and VARCHAR2 base types are described in Appendix C.

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. However, the VARCHAR datatype might change to accommodate emerging SQL standards. So, it is a good idea to use VARCHAR2 rather than VARCHAR.

BOOLEAN

You use the BOOLEAN datatype to store the values TRUE and FALSE and the non-value NULL. Recall that NULL stands for a missing, unknown, or inapplicable value.

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. Furthermore, you cannot select or fetch column values into a BOOLEAN variable.

DATE

You use the DATE datatype to store fixed-length date values. The DATE datatype takes no parameters. Valid dates for DATE variables include January 1, 4712 BC to December 31, 4712 AD.

When stored in a database column, 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.

MLSLABEL

With Trusted Oracle, you use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see Trusted Oracle7 Server Administrator's Guide.

You can use the MLSLABEL datatype to define a database column. Also, you can use the %TYPE and %ROWTYPE attributes to reference the column. However, with standard Oracle, such columns can store only nulls.

With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle converts it to a binary value automatically. The text string can be up to 255 bytes long. However, the internal length of an MLSLABEL value is between 2 and 5 bytes.

With Trusted Oracle, you can also select values from a MLSLABEL column into a character variable. Trusted Oracle converts the internal binary value to a VARCHAR2 value automatically.