Skip Headers
Oracle® Database PL/SQL User's Guide and Reference
10g Release 2 (10.2)

Part Number B14261-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

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

3 PL/SQL Datatypes

Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. PL/SQL provides many predefined datatypes. For instance, you can choose from integer, floating point, character, BOOLEAN, date, collection, reference, and large object (LOB) types. PL/SQL also lets you define your own subtypes. This chapter covers the basic types used frequently in PL/SQL programs. Later chapters cover the more specialized types.

This chapter contains these topics:

Overview of Predefined PL/SQL Datatypes

Predefined PL/SQL datatypes are grouped into composite, LOB, reference, and scalar type categories.

PL/SQL Number Types

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

BINARY_INTEGER Datatype

The BINARY_INTEGER datatype is identical to PLS_INTEGER. BINARY_INTEGER subtypes can be considered as PLS_INTEGER subtypes. See "Change to the BINARY_INTEGER Datatype". To simplify the documentation, PLS_INTEGER is primarily used throughout the book. See "PLS_INTEGER Datatype".

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.

BINARY_FLOAT and BINARY_DOUBLE Datatypes

Single-precision and double-precision IEEE 754-format single-precision floating-point numbers. These types are used primarily for high-speed scientific computation. For usage information, see "Writing Computation-Intensive Programs in PL/SQL". For information about writing math libraries that accept different numeric types, see "Guidelines for Overloading with Numeric Types".

Literals of these types end with f (for BINARY_FLOAT) or d (for BINARY_DOUBLE). For example, 2.07f or 3.000094d.

Computations involving these types produce special values that you need to check for, rather than raising exceptions. To help deal with overflow, underflow, and other conditions that can occur with these numbers, you can use several special predefined constants: BINARY_FLOAT_NAN, BINARY_FLOAT_INFINITY, BINARY_FLOAT_MAX_NORMAL, BINARY_FLOAT_MIN_NORMAL, BINARY_FLOAT_MAX_SUBNORMAL, BINARY_FLOAT_MIN_SUBNORMAL, and corresponding names starting with BINARY_DOUBLE. The constants for NaN (not a number) and infinity are also defined by SQL; the others are PL/SQL-only.

NUMBER Datatype

The NUMBER datatype reliably 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. See Example 2-1.

Oracle recommends only using the value of a NUMBER literal or result of a NUMBER computation that falls within the specified range.

  • If the value of the literal or a NUMBER computation is smaller than the range, the value is rounded to zero.

  • If the value of the literal exceeds the upper limit, a compilation error is raised.

  • If the value of a NUMBER computation exceeds the upper limit, the result is undefined and leads to unreliable results and errors.

The syntax of a NUMBER datatype is:

NUMBER[(precision,scale)]

Precision is the total number of digits and scale is the number of digits to the right of the decimal point. You cannot use constants or variables to specify precision and scale; you must use integer literals.

To declare fixed-point numbers, for which you must specify scale, use the following form that includes both precision and scale:

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 without precision and scale:

NUMBER

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

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

The maximum precision that can be specified for a NUMBER value is 38 decimal digits. If you do not specify precision, it defaults to 39 or 40, 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.4562 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 (34562 becomes 34000). A scale of 0 rounds to the nearest whole number (3.4562 becomes 3). If you do not specify scale, it defaults to 0, as shown in the following example.

DECLARE 
  x NUMBER(3);
BEGIN
  x := 123.89;
  DBMS_OUTPUT.PUT_LINE('The value of x is ' || TO_CHAR(x));
END;
/

The output is: The value of x is 124

For more information on the NUMBER datatype, see Oracle Database SQL Reference.

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
INT
INTEGER
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 Datatype

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2147483648 to 2147483647, represented in 32 bits. PLS_INTEGER values require less storage than NUMBER values and NUMBER subtypes. Also, PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range. For calculations outside the range of PLS_INTEGER, you can use the INTEGER datatype.

Note:

  • The BINARY_INTEGER and PLS_INTEGER datatypes are identical. See "Change to the BINARY_INTEGER Datatype".

  • When a calculation with two PLS_INTEGER datatypes overflows the magnitude range of PLS_INTEGER, an overflow exception is raised even if the result is assigned to a NUMBER datatype.

PL/SQL Character and String Types

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

CHAR Datatype

You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:

CHAR[(maximum_size [CHAR  |  BYTE] )]

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

If you do not specify a maximum size, it defaults to 1. If you specify the maximum size in bytes rather than characters, a CHAR(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation CHAR(n CHAR) so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert CHAR values longer than 2000 bytes into a CHAR database column.

You can insert any CHAR(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a CHAR(n) variable. Note that the LONG datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

For information on semantic differences between the CHAR and VARCHAR2 base types, see "Differences between the CHAR and VARCHAR2 Datatypes".

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 and LONG RAW Datatypes

Note:

The LONG and LONG RAW datatypes are supported only for backward compatibility with existing applications. For new applications, use CLOB or NCLOB in place of LONG, and BLOB or BFILE in place of LONG RAW.

Oracle also recommends that you replace existing LONG and LONG RAW datatypes with LOB datatypes. LOB datatypes are subject to far fewer restrictions than LONG or LONG RAW datatypes. Further, LOB functionality is enhanced in every release, whereas LONG and LONG RAW functionality has been static for several releases. See "PL/SQL LOB Types".

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

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 size of a LONG RAW value is 32760 bytes.

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

Likewise, you can insert any LONG RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483648 bytes. However, you cannot retrieve a value longer than 32760 bytes from a LONG RAW column into a LONG RAW 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 Oracle Database SQL Reference.

In SQL statements, PL/SQL binds LONG values as VARCHAR2, not as LONG. However, if the length of the bound VARCHAR2 exceeds the maximum width of a VARCHAR2 column (4000 bytes), Oracle converts the bind type to LONG automatically, then issues an error message because you cannot pass LONG values to a SQL function.

RAW Datatype

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, Oracle Net 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 size up to 32767 bytes. The syntax follows:

RAW(maximum_size)

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

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 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG RAW column into a RAW variable. Note that the LONG RAW datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

ROWID and UROWID Datatype

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 datatype can store only physical rowids. However, the UROWID (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.

Note:

Use the ROWID datatype only for backward compatibility with old applications. For new applications, use the UROWID datatype.

When you select or fetch 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.

Physical Rowids

Physical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting a set of rows, operating on the whole set, and then updating a subset. For example, you can compare a UROWID 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".

A physical rowid can have either of two formats. The 10-byte extended rowid format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byte restricted rowid format is provided for backward compatibility.

Extended rowids use a base-64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query

SELECT rowid, last_name FROM employees WHERE employee_id = 120; 

might return the following row:


ROWID LAST_NAME
------------------ -------------------------
AAALktAAFAAAABSAAU Weiss

The format, OOOOOOFFFBBBBBBRRR, has four parts:

  • OOOOOO: The data object number (AAALkt in the preceding example) identifies the database segment. Schema objects in the same segment, such as a cluster of tables, have the same data object number.

  • FFF: The file number (AAF in the example) identifies the data file that contains the row. File numbers are unique within a database.

  • BBBBBB: The block number (AAAABS in the example) identifies the data block that contains the row. Because block numbers are relative to their data file, not their tablespace, two rows in the same tablespace but in different data files can have the same block number.

  • RRR: The row number (AAU in the example) identifies the row in the block.

Logical Rowids

Logical rowids provide the fastest access to particular rows. Oracle uses them to construct secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.

A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, Oracle uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale and slow down access to rows. To obtain fresh guesses, you can rebuild the secondary index.

You can use the ROWID pseudocolumn to select logical rowids (which are opaque values) from an index-organized table. Also, you can insert logical rowids into a column of type UROWID, which has a maximum size of 4000 bytes.

The ANALYZE statement helps you track the staleness of guesses. This is useful for applications that store rowids with guesses in a UROWID column, then use the rowids to fetch rows.

To manipulate rowids, you can use the supplied package DBMS_ROWID. For more information, see Oracle Database PL/SQL Packages and Types Reference.

VARCHAR2 Datatype

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 size up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_size [CHAR  |  BYTE])

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

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

If you specify the maximum size in bytes rather than characters, a VARCHAR2(n) variable might be too small to hold n multibyte characters. To avoid this possibility, use the notation VARCHAR2(n CHAR) so that the variable can hold n characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column.

You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable. Note that the LONG datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" more information.

When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

VARCHAR2 Subtypes

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

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

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. It is a good idea to use VARCHAR2 rather than VARCHAR.

PL/SQL National Character Types

The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. These languages require two or three bytes to represent each character. To deal with such languages, Oracle provides globalization support, 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 globalization support, number and date formats adapt automatically to the language conventions specified for a user session. Thus, users around the world can interact with Oracle in their native languages.

PL/SQL supports two character sets called the database character set, which is used for identifiers and source code, and the national character set, which is used for national language data. The datatypes NCHAR and NVARCHAR2 store character strings formed from the national character set.

When converting CHAR or VARCHAR2 data between databases with different character sets, make sure the data consists of well-formed strings. For more information CHAR or VARCHAR2 data, see Oracle Database Globalization Support Guide.

Comparing UTF8 and AL16UTF16 Encodings

The national character set represents data as Unicode, using either the UTF8 or AL16UTF16 encoding.

Each character in the AL16UTF16 encoding takes up 2 bytes. This makes it simple to calculate string lengths to avoid truncation errors when mixing different programming languages, but requires extra storage overhead to store strings made up mostly of ASCII characters.

Each character in the UTF8 encoding takes up 1, 2, or 3 bytes. This lets you fit more characters into a variable or table column, but only if most characters can be represented in a single byte. It introduces the possibility of truncation errors when transferring the data to a buffer measured in bytes.

Oracle recommends that you use the default AL16UTF16 encoding wherever practical, for maximum runtime reliability. If you need to determine how many bytes are required to hold a Unicode string, use the LENGTHB function rather than LENGTH.

NCHAR Datatype

You use the NCHAR datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The NCHAR datatype takes an optional parameter that lets you specify a maximum size in characters. The syntax follows:

NCHAR[(maximum_size)]

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

If you do not specify a maximum size, it defaults to 1. The value always represents the number of characters, unlike CHAR which can be specified in either characters or bytes.

v_string NCHAR(100); -- maximum size is 100 characters

You cannot insert NCHAR values longer than 2000 bytes into an NCHAR column.

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

You can interchange CHAR and NCHAR values in statements and expressions. It is always safe to turn a CHAR value into an NCHAR value, but turning an NCHAR value into 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 can result in characters that usually look like question marks (?).

NVARCHAR2 Datatype

You use the NVARCHAR2 datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding (UTF8) or a fixed-width encoding (AL16UTF16). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The NVARCHAR2 datatype takes a required parameter that specifies a maximum size in characters. The syntax follows:

NVARCHAR2(maximum_size)

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

The maximum size always represents the number of characters, unlike VARCHAR2 which can be specified in either characters or bytes.

v_string NVARCHAR2(200); -- maximum size is 200 characters

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.

You can interchange VARCHAR2 and NVARCHAR2 values in statements and expressions. It is always safe to turn a VARCHAR2 value into an NVARCHAR2 value, but turning an NVARCHAR2 value into 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 can result in characters that usually look like question marks (?).

PL/SQL 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. LOBs allow efficient, random, piece-wise access to the data. BLOB, CLOB, and NCLOB are from 8 to 128 terabytes in size. The size of a BFILE is system dependent, but cannot exceed four gigabytes (4GB - 1 bytes).

The LOB types differ from the LONG and LONG RAW types in several ways. For example, LOBs (except NCLOB) can be attributes of an object type, but LONGs cannot. The maximum size of a BLOB, CLOB, or NCLOB is 8 to 128 terabytes, but the maximum size of a LONG is two gigabytes. Also, LOBs support random access to data, but LONGs support only sequential access. Note that the LONG and LONG RAW datatypes are supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

LOB types store lob locators, which point to 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 LOBs through the locators. For example, when you select a BLOB column value, only a locator is returned. If you got it during a transaction, the LOB locator includes a transaction ID, so you cannot use it to update that LOB in another transaction. Likewise, you cannot save a LOB locator during one session, then use it in another session.

You can also convert CLOBs to CHAR and VARCHAR2 types and vice versa, or BLOBs to RAW and vice versa, which lets you use LOB types in most SQL and PL/SQL statements and functions. To read, write, and do piecewise operations on LOBs, you can use the supplied package DBMS_LOB.

For more information on LOBs, see Oracle Database Application Developer's Guide - Large Objects.

BFILE Datatype

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.

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 Datatype

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.

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 Datatype

You use the CLOB datatype to store large blocks of character data in the database, in-line 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 Datatype

You use the NCLOB datatype to store large blocks of NCHAR data in the database, in-line 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.

PL/SQL Boolean Types

PL/SQL has a type for representing Boolean values (true and false). Because SQL does not have an equivalent type, you can use BOOLEAN variables and parameters in PL/SQL contexts but not inside SQL statements or queries.

BOOLEAN Datatype

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

The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable.

You cannot insert the values TRUE and FALSE into a database column. You cannot select or fetch column values into a BOOLEAN variable. Functions called from a SQL query cannot take any BOOLEAN parameters. Neither can built-in SQL functions such as TO_CHAR; to represent BOOLEAN values in output, you must use IF-THEN or CASE constructs to translate BOOLEAN values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

PL/SQL Date, Time, and Interval Types

The datatypes in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has a date and time datatype holds values called datetimes. A variable that has an interval datatype holds 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 view V$TIMEZONE_NAMES Not applicable
TIMEZONE_ABBR Found in the 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 Reference.

DATE Datatype

You use the DATE datatype 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 instance, SYSDATE + 1 signifies the same time tomorrow.

TIMESTAMP Datatype

The datatype TIMESTAMP, which extends the datatype 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.

In Example 3-1, you declare a variable of type TIMESTAMP, then assign a literal value to it. In the example, the fractional part of the seconds field is 0.275.

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

DECLARE
   checkout TIMESTAMP(3);
BEGIN
   checkout := '22-JUN-2004 07:48:53.275';
   DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout));
END;
/

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

Example 3-2 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

DECLARE
   right_now TIMESTAMP; 
   yesterday TIMESTAMP; 
   sometime TIMESTAMP;
   scn1 INTEGER; 
   scn2 INTEGER; 
   scn3 INTEGER;
BEGIN
   right_now := SYSTIMESTAMP; -- Get the current SCN
   scn1 := TIMESTAMP_TO_SCN(right_now);
   DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1);
   yesterday := right_now - 1; -- Get the SCN from exactly 1 day ago
   scn2 := TIMESTAMP_TO_SCN(yesterday);
   DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2);
-- Find an arbitrary SCN somewhere between yesterday and today
-- In a real program we would have stored the SCN at some significant moment
   scn3 := (scn1 + scn2) / 2;
   sometime := SCN_TO_TIMESTAMP(scn3); -- What time was that SCN was in effect?
   DBMS_OUTPUT.PUT_LINE('SCN ' || scn3 || ' was in effect at ' ||
 TO_CHAR(sometime));
END;
/

TIMESTAMP WITH TIME ZONE Datatype

The datatype TIMESTAMP WITH TIME ZONE, which extends the datatype 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. 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.

In Example 3-3, you declare a variable of type TIMESTAMP WITH TIME ZONE, then assign a literal value to it:

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

DECLARE
   logoff TIMESTAMP(3) WITH TIME ZONE;
BEGIN
   logoff := '10-OCT-2004 09:42:37.114 AM +02:00';
   DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff));
END;
/

In this example, the time-zone displacement is +02:00.

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 V$TIMEZONE_NAMES data dictionary view.

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 Datatype

The datatype TIMESTAMP WITH LOCAL TIME ZONE, which extends the datatype 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 datatype 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.

In Example 3-4, you declare a variable of type TIMESTAMP WITH LOCAL TIME ZONE:

Example 3-4 Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE

DECLARE
   logoff TIMESTAMP(3) WITH LOCAL TIME ZONE;
BEGIN
--   logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; raises an error
   logoff := '10-OCT-2004 09:42:37.114 AM '; -- okay without displacement
   DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff));
END;
/

INTERVAL YEAR TO MONTH Datatype

You use the datatype 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.

In Example 3-5, you declare a variable of type INTERVAL YEAR TO MONTH, then assign a value of 101 years and 3 months to it:

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

DECLARE
   lifetime INTERVAL YEAR(3) TO MONTH;
BEGIN
   lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal
   lifetime := '101-3'; -- implicit conversion from character type
   lifetime := INTERVAL '101' YEAR; -- Can specify just the years
   lifetime := INTERVAL '3' MONTH; -- Can specify just the months
END;
/

INTERVAL DAY TO SECOND Datatype

You use the datatype 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.

In Example 3-6, you declare a variable of type INTERVAL DAY TO SECOND:

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

DECLARE
   lag_time INTERVAL DAY(3) TO SECOND(3);
BEGIN
   lag_time := '7 09:24:30';
   IF lag_time > INTERVAL '6' DAY THEN
     DBMS_OUTPUT.PUT_LINE ( 'Greater than 6 days');
   ELSE
     DBMS_OUTPUT.PUT_LINE ( 'Less than 6 days');
   END IF;
END;
/

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

You can also manipulate datetime values using various functions, such as EXTRACT. For a list of such functions, see Table 2-4, "Built-In Functions".

For further information and examples of datetime arithmetic, see Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Fundamentals.

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 procedure parameters of these types, you can declare variables and procedure parameters of the following subtypes, which use the maximum values for precision:


TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED

Overview of PL/SQL 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. A subtype does not introduce a new type; rather, it places an optional constraint on its base type.

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.

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[(constraint)] [NOT NULL];

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

Some examples follow:

DECLARE
   SUBTYPE BirthDate IS DATE NOT NULL;  -- based on DATE type
   SUBTYPE Counter IS NATURAL;          -- based on NATURAL subtype
   TYPE NameList IS TABLE OF VARCHAR2(10);
   SUBTYPE DutyRoster IS NameList;      -- based on TABLE type
   TYPE TimeRec IS RECORD (minutes INTEGER, hours INTEGER);
   SUBTYPE FinishTime IS TimeRec;       -- based on RECORD type
   SUBTYPE ID_Num IS employees.employee_id%TYPE; -- based on column type

You can use %TYPE or %ROWTYPE to specify the base type. When %TYPE provides the datatype 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-11. For more information, see "Using the %TYPE Attribute" and "Using the %ROWTYPE Attribute".

Using Subtypes

After you define a subtype, you can declare items of that type. In the following example, you declare a variable of type Counter. Notice how the subtype name indicates the intended use of the variable.

DECLARE 
   SUBTYPE Counter IS NATURAL;
   rows Counter;

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. InExample 3-7, you restrict the subtype pinteger to storing integers in the range -9 .. 9. If your program tries to store a number outside that range in a pinteger variable, PL/SQL raises an exception.

Example 3-7 Using Ranges With Subtypes

DECLARE
  v_sqlerrm VARCHAR2(64);
  SUBTYPE pinteger IS PLS_INTEGER RANGE -9 .. 9;
  y_axis pinteger;
  PROCEDURE p (x IN pinteger) IS
    BEGIN  DBMS_OUTPUT.PUT_LINE (x);  END p;
BEGIN
   y_axis := 9; -- valid, in range
   p(10); -- invalid for procedure p
   EXCEPTION 
     WHEN OTHERS THEN
       v_sqlerrm := SUBSTR(SQLERRM, 1, 64);
       DBMS_OUTPUT.PUT_LINE('Error: ' || v_sqlerrm);
END;
/

Type Compatibility With Subtypes

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:

Example 3-8 Type Compatibility With the NUMBER Datatype

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

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

DECLARE 
   SUBTYPE b1 IS BOOLEAN;
   SUBTYPE b2 IS BOOLEAN;
   finished  b1; -- Different subtypes,
   debugging b2; -- both based on BOOLEAN.
BEGIN
   finished :=FALSE;
   debugging := finished; -- They can be assigned to each other.
END;
/

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(15);
   SUBTYPE Text IS VARCHAR2(1500);
   verb     Word;       -- Different subtypes
   sentence Text(150);  -- of types from the same family
BEGIN
  verb := 'program';
  sentence := verb;    -- can be assigned, if not too long.
END;
/

Constraints and Default Values With Subtypes

The examples in this section illustrate the use of constraints and default values with subtypes. In Example 3-9, the procedure enforces the NOT NULL constraint, but does not enforce the size.

Example 3-9 Constraints Inherited by Subprograms

DECLARE 
  SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
  verb     v_word := 'run';
  noun     VARCHAR2(10) := NULL;
  PROCEDURE word_to_upper (w IN v_word) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE (UPPER(w));
  END word_to_upper;
BEGIN
   word_to_upper('run_over_ten_characters'); -- size constraint is not enforced
-- word_to_upper(noun); invalid, NOT NULL constraint is enforced
END;
/

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

Example 3-10 Default Value With Subtypes

DECLARE 
  SUBTYPE v_word IS VARCHAR2(10) NOT NULL; -- invalid to put default here
  verb  v_word  := 'verb';
  noun  v_word  := 'noun';
BEGIN
   DBMS_OUTPUT.PUT_LINE (UPPER(verb));
   DBMS_OUTPUT.PUT_LINE (UPPER(noun));
END;
/

Example 3-11 shows how column constraints are inherited by subtypes.

Example 3-11 Using SUBTYPE With %TYPE and %ROWTYPE

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, 
 deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200),
 deptname VARCHAR2(30) DEFAULT 'Sales');
 
DECLARE
   SUBTYPE v_empid_subtype IS employees_temp.empid%TYPE;
   SUBTYPE v_deptid_subtype IS  employees_temp.deptid%TYPE;
   SUBTYPE v_deptname_subtype IS employees_temp.deptname%TYPE;
   SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;   
   v_empid    v_empid_subtype;
   v_deptid   v_deptid_subtype;
   v_deptname v_deptname_subtype;
   v_emprec   v_emprec_subtype;
BEGIN
   v_empid := NULL;  -- this works, null constraint is not inherited
-- v_empid := 10000002; -- invalid, number precision too large 
   v_deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);
   v_emprec.empid := NULL;  -- this works, null constraint is not inherited
-- v_emprec.empid := 10000002; -- invalid, number precision too large
   v_emprec.deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname); 
END;
/

Converting PL/SQL Datatypes

Sometimes it is necessary to convert a value from one datatype 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 (automatic) datatype conversion. To ensure your program does exactly what you expect, use explicit conversions wherever possible.

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 Oracle Database SQL Reference.

Using explicit conversions, particularly when passing parameters to subprograms, can avoid unexpected errors or wrong results. For example, the TO_CHAR function lets you specify the format for a DATE value, rather than relying on language settings in the database. Including an arithmetic expression among strings being concatenated with the || operator can produce an error unless you put parentheses or a call to TO_CHAR around the entire arithmetic expression.

Implicit Conversion

When it makes sense, PL/SQL can convert the datatype of a value implicitly. This lets you use literals, variables, and parameters of one type where another 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-12, 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. PL/SQL converts the CHAR values to NUMBER values automatically.

Example 3-12 Implicit Conversion

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;
   -- processing done here...
   /* 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;
   DBMS_OUTPUT.PUT_LINE( 'Elapsed time: ' || TO_CHAR(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 3-1 shows which implicit conversions PL/SQL can do.

Notes:

  • The labels PLS_INT and BIN_INT represent the types PLS_INTEGER and BINARY_INTEGER in the table. You cannot use them as abbreviations in code.

  • The PLS_INTEGER and BINARY_INTEGER datatypes are identical so no conversion takes place.

  • The table lists only types that have different representations. Types that have the same representation, such as CLOB and NCLOB, CHAR and NCHAR, and VARCHAR and NVARCHAR2, can be substituted for each other.

  • You can implicitly convert between CLOB and NCLOB, but be careful because this can be an expensive operation. To make clear that this conversion is intended, you can use the conversion functions TO_CLOB and TO_NCLOB.

  • TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH can all be converted using the same rules as the DATE type. However, because of their different internal representations, these types cannot always be converted to each other. See Oracle Database SQL Reference for details on implicit conversions between different date and time types.

Table 3-1 Implicit Conversions


BIN_INT BLOB CHAR CLOB DATE LONG NUMBER PLS_INT RAW UROWID VARCHAR2
BIN_INT     X     X X       X
BLOB                 X    
CHAR X     X X X X X X X X
CLOB     X               X
DATE     X     X         X
LONG     X           X   X
NUMBER X   X     X   X     X
PLS_INT     X     X X       X
RAW   X X     X         X
UROWID     X               X
VARCHAR2 X   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.

Choosing Between Implicit and Explicit Conversion

Relying on implicit datatype conversions is a poor programming practice because they can be slower and the conversion rules might change in later software releases. Implicit conversions are context-sensitive and not always predictable. For best reliability and maintainability, use datatype conversion functions.

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. 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, call TO_CHAR with a format mask.

A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a DATE column. PL/SQL calls the function TO_DATE, which expects the default date format. To insert dates in other formats, 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 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. Note that the LONG RAW datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

Differences between the CHAR and VARCHAR2 Datatypes

This section explains the semantic differences between the CHAR and VARCHAR2 base types. These subtle but important differences come into play when you assign, compare, insert, update, select, or fetch character values.

Assigning Character Values

When you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Information about trailing blanks in the original value is lost. In the following example, the value assigned to last_name includes six trailing blanks, not just one:

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

If the character value is longer than the declared length of the CHAR variable, PL/SQL aborts the assignment and raises the predefined exception VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks. For example, given the declaration

acronym CHAR(4);

the following assignment raises VALUE_ERROR:

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

When you assign a character value to a VARCHAR2 variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, so no information is lost. If the character value is longer than the declared length of the VARCHAR2 variable, PL/SQL aborts the assignment and raises VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks.

Comparing Character Values

You can use the relational operators to compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. One character value is greater than another if it follows it in the collating sequence. For example, given the following declarations in Example 3-13, the IF condition is TRUE.

Example 3-13 Comparing Character Values

DECLARE
  last_name1 VARCHAR2(10) := 'COLES';
  last_name2 VARCHAR2(10) := 'COLEMAN';
BEGIN
  IF last_name1 > last_name2 THEN
    DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is greater than ' || last_name2 );
  ELSE
    DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is greater than ' || last_name1 );
  END IF;
END;
/

The SQL standard requires that two character values being compared have equal lengths. If both values in a comparison have datatype CHAR, blank-padding semantics are used. Before comparing character values of unequal length, PL/SQL blank-pads the shorter value to the length of the longer value. For example, given the following declarations, the IF condition is TRUE.

DECLARE
  last_name1 CHAR(5) := 'BELLO';
  last_name2 CHAR(10) := 'BELLO   ';  -- note trailing blanks
BEGIN
  IF last_name1 = last_name2 THEN
    DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is equal to ' || last_name2 );
  ELSE
    DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is not equal to ' || last_name1 );
  END IF;
END;
/

If either value in a comparison has datatype VARCHAR2, non-blank-padding semantics are used: when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths. For example, given the following declarations, the IF condition is FALSE.

DECLARE
  last_name1 VARCHAR2(10) := 'DOW';
  last_name2 VARCHAR2(10) := 'DOW   ';  -- note trailing blanks
BEGIN
  IF last_name1 = last_name2 THEN
    DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is equal to ' || last_name2 );
  ELSE
    DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is not equal to ' || last_name1 );
  END IF;
END;
/

If a VARCHAR2 value is compared to a CHAR value, non-blank-padding semantics are used. But, remember, when you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Given the following declarations, the IF condition is FALSE because the value of last_name2 includes five trailing blanks.

DECLARE
  last_name1 VARCHAR2(10) := 'STAUB';
  last_name2 CHAR(10)     := 'STAUB';  -- PL/SQL blank-pads value
BEGIN
  IF last_name1 = last_name2 THEN
    DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is equal to ' || last_name2 );
  ELSE
    DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is not equal to ' || last_name1 );
  END IF;
END;
/

All string literals have datatype CHAR. If both values in a comparison are literals, blank-padding semantics are used. If one value is a literal, blank-padding semantics are used only if the other value has datatype CHAR.

Inserting Character Values

When you insert the value of a PL/SQL character variable into an Oracle database column, whether the value is blank-padded or not depends on the column type, not on the variable type.

When you insert a character value into a CHAR database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.

When you insert a character value into a VARCHAR2 database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.

The rules discussed in this section also apply when updating.

When inserting character values, to ensure that no trailing blanks are stored, use the function RTRIM, which trims trailing blanks, as shown in Example 3-14.

Example 3-14 Using the Function RTRIM

DECLARE
   v_empid NUMBER(6);
   v_last_name VARCHAR2(25);
   v_first_name VARCHAR2(20);
BEGIN
   v_empid := 300;
   v_last_name := 'Lee   ';  -- note trailing blanks
   v_first_name := 'Brenda';
   DBMS_OUTPUT.PUT_LINE ( 'Employee Id: ' || v_empid || ' Name: ' 
                         || RTRIM(v_last_name) || ', ' || v_first_name );
END;
/

Selecting Character Values

When you select a value from an Oracle database column into a PL/SQL character variable, whether the value is blank-padded or not depends on the variable type, not on the column type.

When you select a column value into a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. As a result, information about trailing blanks is lost. If the character value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR.

When you select a column value into a VARCHAR2 variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are stored intact, so no information is lost.

For example, when you select a blank-padded CHAR column value into a VARCHAR2 variable, the trailing blanks are not stripped. If the character value is longer than the declared length of the VARCHAR2 variable, PL/SQL aborts the assignment and raises VALUE_ERROR.

The rules discussed in this section also apply when fetching.