Lexical elements and basic data types

Certify and Increase Opportunity.
Be
Govt. Certified PL SQL Developer

PL/SQL LEXICAL ELEMENTS

A lexical element refers to a character or groupings of characters that may legally appear in a source file. Basic lexical elements are

• Tokens
• Source program character set
• Comments

PL/SQL DATATYPES

Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL/SQL 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.

The datatypes are:

• Predefined Datatypes
• User-Defined Subtypes
• Datatype Conversion

Predefined Datatypes

A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.

A lexical element refers to or groupings of characters that may legally appear in file. Basic lexical elements are

• Tokens
• Source program character set
• Comments

PL/SQL DATATYPES

Every constant, variable, and parameter has (or type), which specifies format, constraints, and valid range of values. PL/SQL provides of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL/SQL 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.

The datatypes are:

• Predefined Datatypes
• User-Defined Subtypes
• Datatype Conversion

Predefined Datatypes

A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.

 

A lexical element refers to or groupings of characters that may legally appear in file. Basic lexical elements are

• Tokens
• Source program character set
• Comments

PL/SQL DATATYPES

Every constant, variable, and parameter has (or type), which specifies format, constraints, and valid range of values. PL/SQL provides of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL/SQL 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.

The datatypes are:

• Predefined Datatypes
• User-Defined Subtypes
• Datatype Conversion

Predefined Datatypes

A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.

 

Number Types

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

BINARY_INTEGER

You use the BINARY_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. Like PLS_INTEGER values, BINARY_INTEGER values require less storage than NUMBER values. However, most BINARY_INTEGER operations are slower than PLS_INTEGER operations.

BINARY_INTEGER Subtypes

A base type is the datatype from which is derived. A subtype associates type with and so defines 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.

NUMBER

You use the NUMBER datatype to store fixed-point or floating-point numbers. Its magnitude range is 1E-130 .. 10E125. If the value of an expression falls outside this range, you get >numeric overflow or underflow error. You can specify precision, which is the total number of digits, and scale, which is the number of digits to the right of the decimal point. The syntax follows:

NUMBER[(precision,scale)]

To declare fixed-point numbers, for which you must specify scale, use the following form:

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:

NUMBER

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

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

You cannot use constants or variables to specify precision and scale; you must use integer literals. The maximum precision of >NUMBER value is 38 decimal digits. If you do not specify precision, it defaults to 38 or the maximum supported by your system, whichever is less.

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

NUMBER Subtypes

You can use the following NUMBER subtypes for compatibility with ANSI/ISO and IBM types or when you want descriptive name:

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INTEGER

INT

NUMERIC

REAL

SMALLINT

 

Use the subtypes DEC, DECIMAL, and NUMERIC to declare fixed-point numbers with precision of 38 decimal digits. Use the subtypes DOUBLE PRECISION and FLOAT to declare floating-point numbers with precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with precision of 63 binary digits, which is roughly equivalent to 18 decimal digits. Use the subtypes INTEGER, INT, and SMALLINT to declare integers with precision of 38 decimal digits.

PLS_INTEGER

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range. Although PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. When >PLS_INTEGER calculation overflows, an exception is raised. However, when >BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to >NUMBER variable. Because of this small semantic difference, you might want to continue using BINARY_INTEGER in old applications for compatibility. In new applications, always use PLS_INTEGER for better performance.

Character Types

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

CHAR

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 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 constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

If you do not specify size, it defaults to 1. If you specify the maximum size in bytes rather than characters, >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 -byte character set.

Although PL/SQL character variables can be relatively long, the maximum width of >CHAR database column is 2000 bytes. So, you cannot insert CHAR values longer than 2000 bytes into >CHAR database column.

You can insert any CHAR(n) value into >LONG database column because the maximum width of >LONG column is 2**31 bytes or two gigabytes. However, you cannot retrieve longer than 32767 bytes from >LONG column into >CHAR(n) variable.

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

Note: Semantic differences between the CHAR and VARCHAR2 base types are discussed in Appendix B.

CHAR Subtype

The CHAR subtype CHARACTER has the same range of values as its base type. That is, CHARACTER is just another name for CHAR. You can use this subtype for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than CHAR.

LONG and LONG RAW

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

Starting in Oracle9i, LOB variables can be used interchangeably with LONG and LONG RAW variables. Oracle recommends migrating any LONG data to the CLOB type, and any LONG RAW data to the BLOB type.

You can insert any LONG value into >LONG database column because the maximum width of >LONG column is 2**31 bytes. However, you cannot retrieve longer than 32760 bytes from >LONG column into >LONG variable.

Likewise, you can insert any LONG RAW value into >LONG RAW database column because the maximum width of >LONG RAW column is 2**31 bytes. However, you cannot retrieve longer than 32760 bytes from >LONG RAW column into >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.

Note: 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 >VARCHAR2 column (4000 bytes), Oracle converts the bind type to LONG automatically, then issues an error message because you cannot pass LONG values to function.

RAW

You use the RAW datatype to store binary data or byte strings. For example, >RAW variable might store of graphics characters or 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 parameter that lets you specify size up to 32767 bytes. The syntax follows:

RAW(maximum_size)

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

The maximum width of >RAW database column is 2000 bytes. So, you cannot insert RAW values longer than 2000 bytes into >RAW column. You can insert any RAW value into >LONG RAW database column because the maximum width of >LONG RAW column is 2**31 bytes. However, you cannot retrieve longer than 32767 bytes from >LONG RAW column into >RAW variable.

ROWID and UROWID

Internally, every database table has >ROWID pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of . A physical rowid identifies in an ordinary table. A logical rowid identifies 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.

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

When you select or fetch into >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 >ROWID character string into . If the conversion fails because the character string does not represent rowid, PL/SQL raises the predefined exception SYS_INVALID_ROWID. This also applies to implicit conversions.

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

Physical Rowids

Physical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting of rows, operating on the whole set, and then updating . For example, you can compare >UROWID variable with the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from .

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 -64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query

SQL> SELECT rowid, ename FROM emp WHERE empno = 7788;

might return the following row:

ROWID              ENAME
------------------ ----------
AAAAqcAABAAADFNAAH SCOTT

The format, OOOOOOFFFBBBBBBRRR, has four parts:

• OOOOOO: The data object number (AAAAqc in the example above) identifies the database segment. Schema objects in the same segment, such as of tables, have the same data object number.
• FFF: The file number (AAB in the example) identifies the data file that contains the row. File numbers are unique within .
• BBBBBB: The block number (AAADFN in the example) identifies the data block that contains the row. Block numbers are relative to their data file, not their tablespace. So, two rows in the same tablespace but in different data files can have the same block number.
• RRR: The row number (AAH in the example) identifies the row in the block.

Logical Rowids

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

A logical rowid can include >guess, which identifies the block location of at the time the guess is made. Instead of doing 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 of type UROWID, which has 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 >UROWID column, then use the rowids to fetch rows.

Note: To manipulate rowids, you can use the supplied package DBMS_ROWID. For more information.

VARCHAR2

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes parameter that specifies size up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_size [CHAR | BYTE])

You cannot use 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 >VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For >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 >VARCHAR2(2000 BYTE) variable and to >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, >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 -byte character set.

Although PL/SQL character variables can be relatively long, the maximum width of >VARCHAR2 database column is 4000 bytes. So, you cannot insert VARCHAR2 values longer than 4000 bytes into >VARCHAR2 database column.

You can insert any VARCHAR2(n) value into >LONG database column because the maximum width of >LONG column is 2**31 bytes. However, you cannot retrieve longer than 32767 bytes from >LONG column into >VARCHAR2(n) variable.

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 /SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

VARCHAR2 Subtypes

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

STRING

VARCHAR

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

Note: Currently, VARCHAR is synonymous with VARCHAR2. However, in future releases of PL/SQL, to accommodate emerging SQL standards, VARCHAR might become datatype with different comparison semantics. So, it is idea to use VARCHAR2 rather than VARCHAR.

National Character Types

The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. These languages require two or three bytes to represent each character. To deal with such languages, Oracle provides globalization support, which lets you process single-byte and multibyte 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 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.

Note: When converting CHAR or VARCHAR2 data between databases with different character sets, make sure the data consists of well-formed strings. For more information

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 or table column, but only if most characters can be represented in byte. It introduces the possibility of truncation errors when transferring the data to measured in bytes.

Oracle Corporation 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 string, use the LENGTHB function rather than LENGTH.

NCHAR

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 -width encoding (UTF8) or -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 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 constant or variable to specify the maximum size; you must use an integer literal.

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

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

The maximum width of an NCHAR database column is 2000 bytes. So, 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 >CHAR value into an NCHAR value, but turning an NCHAR value into >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

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 -width encoding (UTF8) or -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 parameter that specifies 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 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.

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

The maximum width of >NVARCHAR2 database column is 4000 bytes. Therefore, you cannot insert NVARCHAR2 values longer than 4000 bytes into >NVARCHAR2 column.

You can interchange VARCHAR2 and NVARCHAR2 values in statements and expressions. It is always safe to turn >VARCHAR2 value into an NVARCHAR2 value, but turning an NVARCHAR2 value into >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 (?).

LOB Types

The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. And, they allow efficient, random, piece-wise access to the data.

The LOB types differ from the LONG and LONG RAW types in several ways. For example, LOBs (except NCLOB) can be attributes of an object type, but LONGs cannot. The maximum size of >LOB is four gigabytes, but the maximum size of >LONG is two gigabytes. Also, LOBs support random access to data, but LONGs support only sequential access.

LOB types store lob locators, which point to large objects stored in an external file, in-line (inside the row) or out-of-line (outside the row). Database columns of type BLOB, CLOB, NCLOB, or BFILE store the locators. BLOB, CLOB, and NCLOB data is stored in the database, in or outside the row. BFILE data is stored in operating system files outside the database.

PL/SQL operates on LOBs through the locators. For example, when you select >BLOB column value, only is returned. If you got it during , the LOB locator includes ID, so you cannot use it to update that LOB in another transaction. Likewise, you cannot save >LOB locator during one session, then use it in another session.

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

BFILE

You use the BFILE datatype to store large binary objects in operating system files outside the database. Every BFILE variable stores locator, which points to binary file on the server. The locator includes alias, which specifies path name (logical path names are not supported).

BFILEs are read-only, so you cannot modify them. The size of >BFILE is system dependent but cannot exceed four gigabytes (2**32 – 1 bytes). Your DBA makes sure that 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

You use the BLOB datatype to store large binary objects in the database, in-line or out-of-line. Every BLOB variable stores , which points to binary object. The size of >BLOB cannot exceed four gigabytes.

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

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 , which points to block of character data. The size of >CLOB cannot exceed four gigabytes.

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

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 , which points to block of NCHAR data. The size of an NCLOB cannot exceed four gigabytes.

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 Type

BOOLEAN

You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL (which stands for , 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 >BOOLEAN variable. You cannot insert the values TRUE and FALSE into column. Also, you cannot select or fetch column values into >BOOLEAN variable.

Datetime and Interval Types

The datatypes in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has /time datatype holds values called datetimes; 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.

DATE

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.

Tip: 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.

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 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 -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. For example, the following statement returns the number of days since an employee was hired:

SELECT SYSDATE - hiredate INTO days_worked FROM emp
   WHERE empno = 7499;

In arithmetic expressions, PL/SQL interprets integer literals as days. For instance, SYSDATE + 1 is tomorrow.

TIMESTAMP

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 constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9.

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

In the following example, you declare of type TIMESTAMP, then assign value to it:

DECLARE
   checkout TIMESTAMP(3);
BEGIN
   checkout := '1999-06-22 07:48:53.275';
   ...
END;

In this example, the fractional part of the seconds field is 0.275.

TIMESTAMP WITH TIME ZONE

The datatype TIMESTAMP WITH TIME ZONE, which extends the datatype TIMESTAMP, includes >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 constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9.

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

In the following example, you declare of type TIMESTAMP WITH TIME ZONE, then assign value to it:

DECLARE
   logoff TIMESTAMP(3) WITH TIME ZONE;
BEGIN
   logoff := '1999-10-31 09:42:37.114 +02:00';
   ...
END;

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

You can also specify the time zone by using name. The specification can include form such as 'US/Pacific', an abbreviation such as 'PDT', or . 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 '1999-04-15 8:00:00 -8:00'
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
TIMESTAMP '1999-10-31 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:

'1999-08-29 08:00:00 -8:00'
'1999-08-29 11:00:00 -5:00'

TIMESTAMP WITH LOCAL TIME ZONE

The datatype TIMESTAMP WITH LOCAL TIME ZONE, which extends the datatype TIMESTAMP, includes >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 constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9.

This datatype differs from TIMESTAMP WITH TIME ZONE in that when you insert into 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 the following example, you declare of type TIMESTAMP WITH LOCAL TIME ZONE:

DECLARE
   logoff TIMESTAMP(3) WITH LOCAL TIME ZONE;
BEGIN
   ...
END;

You cannot assign literal values to of this type.

INTERVAL YEAR TO MONTH

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 constant or variable to specify the precision; you must use an integer literal in the range 0 .. 4. The default is 2.

In the following example, you declare of type INTERVAL YEAR TO MONTH, then assign 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

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 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 the following example, you declare of type INTERVAL DAY TO SECOND:

DECLARE
   lag_time INTERVAL DAY(3) TO SECOND(3);
BEGIN
   IF lag_time > INTERVAL '6' DAY THEN ...
   ...
END;

Datetime and Interval Arithmetic

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

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

You can also manipulate datetime value using various functions, such as EXTRACT.

Avoiding Truncation Problems Using Date and Time Subtypes

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

TIMESTAMP_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

YMINTERVAL_UNCONSTRAINED

DSINTERVAL_UNCONSTRAINED

User-Defined Subtypes

Each PL/SQL base type specifies of values and of operations applicable to items of that type. Subtypes specify the same set of operations as their base type but only of its values. Thus, does not introduce type; it merely places an optional constraint on its base type.

Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables. PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtypes CHARACTER and INTEGER as follows:

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

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

Defining Subtypes

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

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

where subtype_name is 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 size.

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 emp.empno%TYPE;    -- based on column type

You can use %TYPE or %ROWTYPE to specify the base type. When %TYPE provides the datatype of column, the subtype inherits the size constraint (if any) of the column. However, the subtype does not inherit other kinds of constraints such as NOT NULL.

Using Subtypes

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

DECLARE 
   SUBTYPE Counter IS NATURAL;
   rows Counter;

The following example shows that you can constrain -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. In the example below, you restrict the subtype Numeral to storing integers in the range -9 .. 9. If your program tries to store outside that range in >Numeral variable, PL/SQL raises an exception.

DECLARE 
   SUBTYPE Numeral IS NUMBER(1,0);
   x_axis Numeral;  -- magnitude range is -9 .. 9
   y_axis Numeral;
BEGIN
   x_axis := 10;  -- raises VALUE_ERROR
   ...
END;

Type Compatibility

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
   ...
   total := amount;
   ...
END;

Different subtypes are interchangeable if they have the same base type. For instance, given the following declarations, the value of finished can be assigned to debugging:

DECLARE 
   SUBTYPE Sentinel IS BOOLEAN;
   SUBTYPE Switch IS BOOLEAN;
   finished  Sentinel;
   debugging Switch;
BEGIN
   ...
   debugging := finished;
   ...
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;
   sentence Text(150);
BEGIN
   ...
   sentence := verb;
   ...
END;

Datatype Conversion

Sometimes it is necessary to convert from one datatype to another. For example, if you want to examine , you must convert it to string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.

Explicit Conversion

To convert values from one datatype to another, you use built-in functions. For example, to convert >CHAR value to >DATE or NUMBER value, you use the function TO_DATE or TO_NUMBER, respectively. Conversely, to convert >DATE or NUMBER value to >CHAR value, you use the function TO_CHAR.

Implicit Conversion

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

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;
   -- do something
   /* 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;
   INSERT INTO results VALUES (elapsed_time, ...);
END;

Before assigning column value to , 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 >DATE column value into >VARCHAR2 variable.

Likewise, before assigning the value of to 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 error. In such cases, you must use conversion function. Table 3-1 shows which implicit conversions PL/SQL can do.

Notes:

• 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.
• To convert between CLOB and NCLOB, you must 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.

Table 3-1 Implicit Conversions

	BIN_INT	BLOB	CHAR	CLOB	DATE	LONG	NUMBER	PLS_INT	RAW	UROWID	VARCHAR2
BIN_INT			X			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				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 >DATE value but cannot convert the CHAR value 'YESTERDAY' to >DATE value. Similarly, PL/SQL cannot convert >VARCHAR2 value containing alphabetic characters to >NUMBER value.

Implicit versus Explicit Conversion

Generally, to rely on implicit datatype conversions is programming practice because they can hamper performance and might change from one software release to the next. Also, implicit conversions are context sensitive and therefore not always predictable. Instead, use datatype conversion functions. That way, your applications will be more reliable and easier to maintain.

DATE Values

When you select >DATE column value into >CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to value. So, PL/SQL calls the function TO_CHAR, which returns string in the default date format. To get other information such as the time or Julian date, you must call TO_CHAR with mask.

A conversion is also necessary when you insert >CHAR or VARCHAR2 value into >DATE column. So, PL/SQL calls the function TO_DATE, which expects the default date format. To insert dates in other formats, you must call TO_DATE with mask.

RAW and LONG RAW Values

When you select >RAW or LONG RAW column value into >CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to value. In this case, PL/SQL returns each binary byte of RAW or LONG RAW data as of characters. Each character represents the hexadecimal equivalent of (half ). 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 >CHAR or VARCHAR2 value into >RAW or LONG RAW column. Each pair of characters in the variable must represent the hexadecimal equivalent of byte. If either character does not represent the hexadecimal equivalent of , PL/SQL raises an exception.

-Oracle