Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
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:
Predefined PL/SQL datatypes are grouped into composite, LOB
, reference, and scalar type categories.
A composite type has internal components that can be manipulated individually, such as the elements of an array, record, or table. See Chapter 5, "Using PL/SQL Collections and Records".
A LOB
type holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE
, BLOB
, CLOB
, and NCLOB
. See "PL/SQL LOB Types".
A reference type holds values, called pointers, that designate other program items. These types include REF
CURSORS
and REF
s to object types. See "Using Cursor Variables (REF CURSORs)".
A scalar type has no internal components. It holds a single value, such as a number or character string. The scalar types fall into four families, which store number, character, Boolean, and date/time data. The scalar families with their datatypes are:
BINARY_DOUBLE
, BINARY_FLOAT
, BINARY_INTEGER
, DEC
, DECIMAL
, DOUBLE
PRECISION
, FLOAT
, INT
, INTEGER
, NATURAL
, NATURALN
, NUMBER
, NUMERIC
, PLS_INTEGER
, POSITIVE
, POSITIVEN
, REAL
, SIGNTYPE
, SMALLINT
PL/SQL Character and String Types and PL/SQL National Character Types
CHAR
, CHARACTER
, LONG
, LONG
RAW
, NCHAR
, NVARCHAR2
, RAW
, ROWID
, STRING
, UROWID
, VARCHAR
, VARCHAR2
Note that the LONG
and LONG
RAW
datatypes are supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.
BOOLEAN
PL/SQL Date, Time, and Interval Types
DATE
, TIMESTAMP
, TIMESTAMP
WITH
TIMEZONE
, TIMESTAMP
WITH
LOCAL
TIMEZONE
, INTERVAL
YEAR
TO
MONTH
, INTERVAL
DAY
TO
SECOND
Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.
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".
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.
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.
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.
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.
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.
Character types let you store alphanumeric data, represent words and text, and manipulate character strings.
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".
Note:
TheLONG
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.
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.
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 theROWID
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 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 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.
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.
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
.
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.
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
.
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 (?).
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 (?).
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, LOB
s (except NCLOB
) can be attributes of an object type, but LONG
s 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, LOB
s support random access to data, but LONG
s 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 LOB
s 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 CLOB
s to CHAR
and VARCHAR2
types and vice versa, or BLOB
s 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 LOB
s, you can use the supplied package DBMS_LOB
.
For more information on LOBs, see Oracle Database Application Developer's Guide - Large Objects.
You use the BFILE
datatype to store large binary objects in operating system files outside the database. Every BFILE
variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name. Logical path names are not supported.
BFILE
s are read-only, 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.
BFILE
s do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILE
s is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES
, which is system dependent.
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.
BLOB
s 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.
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.
CLOB
s 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.
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.
NCLOB
s 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 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.
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.
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.
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.
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 TIMESTAMP
s.
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; /
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'
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; /
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; /
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
:
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.
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
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 numbersThe 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.
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".
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; /
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; /
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; /
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.
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.
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.
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.
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.
When you select a RAW
or LONG
RAW
column value into a CHAR
or VARCHAR2
variable, PL/SQL must convert the internal binary value to a character value. In this case, PL/SQL returns each binary byte of RAW
or LONG
RAW
data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, PL/SQL returns the binary byte 11111111 as the pair of characters 'FF'
. The function RAWTOHEX
does the same conversion.
A conversion is also necessary when you insert a CHAR
or VARCHAR
2 value into a RAW
or LONG
RAW
column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte. 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.
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.
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.
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
.
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; /
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.