3 PL/SQL Datatypes

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

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.

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

LONG and LONG RAW Datatypes

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.

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.

SELECT rowid, last_name FROM employees WHERE employee_id = 120; 

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.

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 (?).

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.

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.

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.

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.

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:

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.

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:

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:

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:

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:

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:

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;
/

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:

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.

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.

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.

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;
/