3 PL/SQL Data Types

PLS_INTEGER and BINARY_INTEGER Data Types

The PLS_INTEGER and BINARY_INTEGER data types are identical. For simplicity, this document uses "PLS_INTEGER" to mean both PLS_INTEGER and BINARY_INTEGER.

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:

• PLS_INTEGER values require less storage.

• PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

For efficiency, use PLS_INTEGER values for all calculations that fall in its range. For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.

Table 3-4 lists the predefined subtypes of the PLS_INTEGER data type and describes the data they store.

SIMPLE_INTEGER Subtype of PLS_INTEGER

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics.

You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER provides significantly better performance than PLS_INTEGER when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

Topics:

• Overflow Semantics

• Overloading Rules

• Integer Literals

• Cast Operations

• Compiler Warnings

DECLARE
  n SIMPLE_INTEGER := 2147483645;
BEGIN
  FOR j IN 1..4 LOOP
    n := n + 1;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
  END LOOP;
  FOR j IN 1..4 LOOP
   n := n - 1;
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
  END LOOP;
END;
/

+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645

BINARY_FLOAT and BINARY_DOUBLE Data Types

The BINARY_FLOAT and BINARY_DOUBLE data types represent single-precision and double-precision IEEE 754-format floating-point numbers, respectively.

A BINARY_FLOAT literal ends with f or F (for example, 2.07f). A BINARY_DOUBLE literal ends with d or D (for example, 3.000094D).

BINARY_FLOAT and BINARY_DOUBLE computations do not raise exceptions; therefore, you must check the values that they produce for conditions such as overflow and underflow, using the predefined constants listed and described in Table 3-5. For example:

SELECT COUNT(*)
  FROM employees
    WHERE salary < BINARY_FLOAT_INFINITY;

^Footnote 1 Also predefined by SQL

In the IEEE-754 standard, subnormal ranges of values are intended to reduce problems caused by underflow to zero.

BINARY_FLOAT and BINARY_DOUBLE data types are primarily for high-speed scientific computation, as explained in "Computation-Intensive PL/SQL Programs".

SIMPLE_FLOAT and SIMPLE_DOUBLE are predefined subtypes of the BINARY_FLOAT and BINARY_DOUBLE data types, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.

You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness, SIMPLE_FLOAT and SIMPLE_DOUBLE provide significantly better performance than BINARY_FLOAT and BINARY_DOUBLE when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_FLOAT and SIMPLE_DOUBLE values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.

NUMBER Data Type

The NUMBER data type 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.

Oracle recommends using only NUMBER literals and results of NUMBER computations that are in the specified range. Otherwise:

• Any value that is too small is rounded to zero.

• A literal value that is too large causes a compilation error.

• A computation result that is too large is undefined, causing unreliable results and possibly run-time errors.

A NUMBER value has both precision (its total number of digits) and scale (the number of digits to the right of the decimal point).

The syntax for specifying a fixed-point NUMBER is:

NUMBER(precision, scale)

For example:

NUMBER(8,2)

For an integer, the scale is zero. The syntax for specifying an integer NUMBER is:

NUMBER(precision)

For example:

NUMBER(2)

In a floating-point number, the decimal point can float to any position. The syntax for specifying a floating-point NUMBER is:

NUMBER

Both precision and scale must be integer literals, not constants or variables.

For precision, the maximum value is 38. The default value is 39 or 40, or the maximum for your system, whichever is least.

For scale, the minimum and maximum values are -84 and 127, respectively. The default value is zero.

Scale determines where rounding occurs. For example, a value whose scale is 2 is rounded to the nearest hundredth (3.454 becomes 3.45 and 3.456 becomes 3.46). A negative scale causes rounding to the left of the decimal point. For example, a value whose scale is -3 is rounded to the nearest thousand (34462 becomes 34000 and 34562 becomes 35000). A value whose scale is 0 is rounded to the nearest integer (3.4562 becomes 3 and 3.56 becomes 4).

For more information about the NUMBER data type, see Oracle Database SQL Language Reference.

Table 3-6 lists the predefined subtypes of the NUMBER data type and describes the data they store.

CHAR and VARCHAR2 Data Types

The CHAR and VARCHAR2 data types store fixed-length and variable-length character strings, respectively. All string literals have data type CHAR.

How CHAR and VARCHAR2 data is represented internally depends on the database character set. For information about the database character set, see "Database Character Set".

The syntax for specifying a CHAR or VARCHAR2 data item is:

[ CHAR | VARCHAR2 ] [( maximum_size [ CHAR | BYTE ] )]

For example:

CHAR
VARCHAR2
CHAR(10 CHAR)
VARCHAR2(32 BYTE)

The maximum_size must be an integer literal in the range 1..32767, not a constant or variable. The default value is one.

The default size unit (CHAR or BYTE) is determined by the NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL subprogram is compiled, the setting of this parameter is recorded, so that the same setting is used when the subprogram is recompiled after being invalidated. For more information about NLS_LENGTH_SEMANTICS, see Oracle Database Reference.

The maximum size of a CHAR or VARCHAR2 data item is 32,767 bytes, whether you specify maximum_size in characters or bytes. The maximum number of characters in a CHAR or VARCHAR2 data item depends on how the character set is encoded. For a single-byte character set, the maximum size of a CHAR or VARCHAR2 data item is 32,767 characters. For an n-byte character set, the maximum size of a CHAR or VARCHAR2 data item is 32,767/n characters, rounded down to the nearest integer. For a multiple-byte character set, specify maximum_size in characters to ensure that a CHAR(n) or VARCHAR2(n) variable can store n multiple-byte characters.

If the character value that you assign to a character variable is longer than the maximum size of the variable, PL/SQL does not truncate the value or strip trailing blanks; it stops the assignment and raises the predefined exception VALUE_ERROR.

For example, given the declaration:

acronym CHAR(4);

this assignment raises VALUE_ERROR:

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

If the character value that you insert into a database column is longer than the defined width of the column, PL/SQL does not truncate the value or strip trailing blanks; it stops the insertion and raises an exception.

To strip trailing blanks from a character value before assigning it to a variable or inserting it into a database column, use the built-in function RTRIM. For example, given the preceding declaration, this assignment does not raise an exception:

acronym := RTRIM('SPCA ');  -- note trailing blank

For the syntax of RTRIM, see Oracle Database SQL Language Reference.

Differences Between CHAR and VARCHAR2 Data Types

CHAR and VARCHAR2 data types differ in:

• Predefined Subtypes of Character Data Types

• Memory Allocation for Character Variables

• Blank-Padding Shorter Character Values

• Comparing Character Values

• Maximum Sizes of Values Inserted into Character Database Columns

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

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

COLES is greater than COLEMAN

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

BELLO equals BELLO

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

DOW    does not equal DOW

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 || ' equals ' || last_name2);
  ELSE
    DBMS_OUTPUT.PUT_LINE
      (last_name2 || ' does not equal ' || last_name1 );
  END IF;
END;
/

STAUB      does not equal STAUB

RAW Data Type

The RAW data type stores binary or byte strings, such as sequences of graphics characters or digitized pictures. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data. Oracle Net does no character set conversions when you transmit raw data from one system to another.

The syntax for specifying a RAW data item is:

RAW (maximum_size)

For example:

RAW(256)

The maximum_size, in bytes, must be an integer literal in the range 1..32767, not a constant or variable. The default value is one.

The largest RAW value that you can insert into a RAW database column is 2,000 bytes.

You can insert any RAW value into a LONG RAW database column, because the maximum width of a LONG RAW column is 2,147,483,648 bytes (2 GB). However, you cannot retrieve a value longer than 32,767 bytes from a LONG RAW column into a RAW variable. (The LONG RAW data type is supported only for backward compatibility with existing applications. For more information, see "LONG and LONG RAW Data Types".)

NCHAR and NVARCHAR2 Data Types

The NCHAR and NVARCHAR2 data types store fixed-length and variable-length national character strings, respectively.

How NCHAR and NVARCHAR2 data is represented internally depends on the national character set. For information about the national character set, see "National Character Set".

Topics:

• NCHAR Data Type

• NVARCHAR2 Data Type

NCHAR [(maximum_size)]

NCHAR
NCHAR(100)

NVARCHAR2 (maximum_size)

NVARCHAR2(300)

LONG and LONG RAW Data Types

The LONG data type stores variable-length character strings. The LONG data type is like the VARCHAR2 data type, except that the maximum size of a LONG value is 32,760 bytes (as opposed to 32,767 bytes).

The LONG RAW data type stores binary 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 32,760 bytes.

Because the maximum width of a LONG or LONG RAW database column is 2,147,483,648 bytes (2 GB), you can insert any LONG value into a LONG column and any LONG RAW value into a LONG RAW column. However, you cannot retrieve a value longer than 32,760 bytes from a LONG column into a LONG variable, or from a LONG RAW column into a LONG RAW variable.

LONG database columns can store text, arrays of characters, and even short documents.

ROWID and UROWID Data Types

Internally, every database table has a ROWID pseudocolumn, which returns 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 data type can store only physical rowids, while the UROWID (universal rowid) data type can store physical, logical, or foreign (not database) rowids.

Physical rowids are useful for working around the inability to fetch from a FOR UPDATE cursor after a COMMIT or ROLLBACK statement, as in Example 6-47.

When you retrieve 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.

DATE Data Type

You use the DATE data type 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 use the addition and subtraction operators to increment or decrement date values. For example:

same_time_tomorrow := SYSDATE + 1;
hire_date := '10-MAY-95';
hire_date := hire_date + 1;  -- makes hire_date '11-MAY-95'
hire_date := hire_date - 5;  -- makes hire_date '06-MAY-95'

TIMESTAMP Data Type

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

Example 3-4 declares a variable of type TIMESTAMP and assigns a literal value to it. 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;
/

22-JUN-04 07.48.53.275 AM

In Example 3-5, 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;
  scn1 := TIMESTAMP_TO_SCN(right_now);
  DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1);
 
  yesterday := right_now - 1;
  scn2 := TIMESTAMP_TO_SCN(yesterday);
  DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2);
 
  -- Find arbitrary SCN between yesterday and today
 
  scn3 := (scn1 + scn2) / 2;
  sometime := SCN_TO_TIMESTAMP(scn3);
  DBMS_OUTPUT.PUT_LINE
    ('SCN ' || scn3 || ' was in effect at ' || TO_CHAR(sometime));
END;
/

Current SCN is 10216101
SCN from yesterday is 10105627
SCN 10160864 was in effect at 17-MAR-09 12.16.02.000000 AM

TIMESTAMP WITH TIME ZONE Data Type

The TIMESTAMP WITH TIME ZONE data type, which extends the data type 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 (GMT). 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.

Example 3-6 declares a variable of type TIMESTAMP WITH TIME ZONE and assign a literal value to it. The time-zone displacement is +02:00.

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

10-OCT-04 09.42.37.114 AM +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, these literals all represent the same 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'

Of the preceding forms, 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 static data dictionary view V$TIMEZONE_NAMES.

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, these two values are considered identical because, in UTC, 8:00 AM Pacific Standard Time equals 11:00 AM Eastern Standard Time:

'29-AUG-2004 08:00:00 -8:00'
'29-AUG-2004 11:00:00 -5:00'

TIMESTAMP WITH LOCAL TIME ZONE Data Type

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

Both Example 3-7 and Example 3-8 declare a variable of type TIMESTAMP WITH LOCAL TIME ZONE and assign it a value. The value in Example 3-7 is an appropriate local time, but the value in Example 3-8 includes a time zone displacement, which causes an error.

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

10-OCT-04 09.42.37.114 AM

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

DECLARE
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 4

INTERVAL YEAR TO MONTH Data Type

Use the INTERVAL YEAR TO MONTH data type 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.

Example 3-9 declares a variable of type INTERVAL YEAR TO MONTH and assigns a value of 101 years and 3 months to it, in three different ways.

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;  -- Specify only years
  lifetime := INTERVAL '3' MONTH;   -- Specify only months
END;
/

INTERVAL DAY TO SECOND Data Type

You use the INTERVAL DAY TO SECOND data type 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.

Example 3-10 declares a variable of type INTERVAL DAY TO SECOND and assigns a value to it.

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

Greater than 6 days

Datetime and Interval Arithmetic

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

Avoiding Truncation Problems with Date and Time Subtypes

The default precisions for some 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 subprogram parameters of these types, you can declare variables and subprogram parameters of these subtypes, which use the maximum values for precision:

TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED