PL/SQL Datatypes

Every variable, constant and parameter must have a valid data type, which specifies a storage format, constraints, and valid range of values. PL/SQL provides many predefined datatypes, you can choose from integer, character, floating point, BOOLEAN, date, collection, reference, and large object (LOB) types. Also, you can define your own subtypes. Every variable, constant and parameter must have a valid data type, which specifies a storage format, constraints, and valid range of values.

PL/SQL provides many predefined datatypes, you can choose from integer, character, floating point, BOOLEAN, date, collection, reference, and large object (LOB) types. Also, you can define your own subtypes.

Overview of Predefined PL/SQL Datatypes

1. Scalar type.

A scalar type holds a single value, such as a number or character string. The scalar types fall into four families, which store number, character, Boolean, and date/time data. The scalar families with their datatypes are:

  1. PL/SQL Number Types
  2. PL/SQL Character and String Types and PL/SQL National Character Types
  3. PL/SQL Boolean Types
  4. PL/SQL Date, Time, and Interval Types

2. LOB type

A LOB type holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE, BLOB, CLOB, and NCLOB.

3. Composite type

A composite type has internal components that can be manipulated individually, such as the elements of an array, record, or table. For example, Collections and Records.

4. Reference type

A reference type holds values, called pointers, that designate other program items. These types include REF CURSORS and REFs to object types.

This chapter covers SCALAR and LOB data types and the other two data types will be covered in other chapters.

PL/SQL Scalar Data types and subtypes.

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

1. PLS_INTEGER
You use the PLS_INTEGER datatype to store signed integers. Signed integer range is -2147483648 through 2147483647, represented in 32 bits.

  • PLS_INTEGER values require less storage than NUMBER values and NUMBER subtypes.
  • 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.

2. BINARY_INTEGER
The BINARY_INTEGER datatype is identical to PLS_INTEGER. BINARY_INTEGER subtypes can be considered as PLS_INTEGER subtypes. Signed integer in range is -2147483648 through 2147483647, represented in 32 bits.

BINARY_INTEGER Subtypes
A base type is a datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following BINARY_INTEGER subtypes:

  • NATURAL
  • NATURALN
  • POSITIVE
  • POSITIVEN
  • SIGNTYPE

The subtypes NATURAL and POSITIVE let you restrict an integer variable to non-negative or positive values, respectively. NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. SIGNTYPE lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.

3. BINARY_FLOAT and BINARY_DOUBLE
Single-precision and double-precision IEEE 754-format single-precision floating-point numbers. These types are used primarily for high-speed scientific computation.

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

4. NUMBER
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.

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.

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

  • DEC
  • DECIMAL
  • DOUBLE
  • PRECISION
  • FLOAT
  • INT
  • INTEGER
  • NUMERIC
  • REAL
  • SMALLINT

Use the subtypes DEC, DECIMAL, and NUMERIC to declare fixed-point numbers with a maximum precision of 38 decimal digits.

Use the subtypes DOUBLE PRECISION and FLOAT to declare floating-point numbers with a maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits.

Use the subtypes INTEGER, INT, and SMALLINT to declare integers with a maximum precision of 38 decimal digits.

2. PL/SQL Character and String Types

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

1. CHAR
You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. A fixed-length character string with maximum size of 32,767 bytes.

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.

2. VARCHAR2
VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. A variable-length character string with maximum size of 32,767 bytes.

VARCHAR2 Subtypes

The VARCHAR2 subtypes STRING and VARCHAR have the same range of values as their base type. For example, VARCHAR is just another name for VARCHAR2.
You can use the VARCHAR2 subtypes for compatibility with ANSI/ISO and IBM types.

NCHAR
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). A fixed-length national character string with maximum size of 32,767 bytes.

NVARCHAR2
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). A variable-length national character string with maximum size of 32,767 bytes.

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

LONG RAW
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 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.

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

ROWID and UROWID

Internally, every database table has a ROWID pseudo column, 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.

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

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

SELECT rowid, last_name FROM employees WHERE employee_id = 120;

might return the following row:

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

The format, OOOOOOFFFBBBBBBRRR, has four parts:

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

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

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

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

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

3. PL/SQL Boolean Datatypes

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

PL/SQL Date, Time, and Interval Types

The DATE datatype is used to store fixed-length date times, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31,9999 AD.

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. For example, 01-OCT-12.

Each DATE includes the century, year, month, day, hour, minute, and second. The following table 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

DATE Datatype

You use the DATE datatype to store fixed-length date times, 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).

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.

Assigning a Literal Value to a TIMESTAMP Variable

DECLARE
checkout TIMESTAMP(3);

BEGIN

checkout := '22-JUN-2004 07:48:53.275';

DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout));

END;

/

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.

Declare a variable of type TIMESTAMP WITH TIME ZONE, then assign a literal value to it:

Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable

DECLARE
logoff TIMESTAMP(3) WITH TIME ZONE;

BEGIN

logoff := '10-OCT-2004 09:42:37.114 AM +02:00';

DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff));

END;

/

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.

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.

DECLARE
lifetime INTERVAL YEAR(3) TO MONTH;

BEGIN

lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal

lifetime := '101-3'; -- implicit conversion from character type

lifetime := INTERVAL '101' YEAR; -- Can specify just the years

lifetime := INTERVAL '3' MONTH; -- Can specify just the months

END;

/

INTERVAL DAY TO SECOND Datatype

You use the datatype INTERVAL DAY TO SECOND to store and manipulate intervals of days, hours, minutes, and seconds. The syntax is:

INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)]

where leading_precision and fractional_seconds_precision specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The defaults are 2 and 6, respectively.

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;

/

PL/SQL LOB Types

The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data, such as text, graphic images, video clips, and sound waveforms. LOBs allow efficient, random, piece-wise access to the data. BLOB, CLOB, and NCLOB are from 8 to 128 terabytes in size. The size of a BFILE is system dependent, but cannot exceed four gigabytes (4GB – 1 bytes).

BFILE Datatype

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

BFILEs are read-only, so you cannot modify them. Your DBA makes sure that a given BFILE exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.
BFILEs do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open BFILEs is set by the Oracle initialization parameter SESSION_MAX_OPEN_FILES, which is system dependent.

BLOB Datatype

You use the BLOB datatype to store large binary objects in the database, in-line or out-of-line. Every BLOB variable stores a locator, which points to a large binary object.
BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.

CLOB Datatype

You use the CLOB datatype to store large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every CLOB variable stores a locator, which points to a large block of character data.
CLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions.

NCLOB Datatype

You use the NCLOB datatype to store large blocks of NCHAR data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every NCLOB variable stores a locator, which points to a large block of NCHAR data.
NCLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. NCLOB locators can span transactions (for reads only), but they cannot span sessions.

PL/SQL User-Defined Subtypes

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values.

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

You can define and use your own subtypes. The following program illustrates defining and using a user-defined subtype

DECLARE
SUBTYPE name IS char(20);

SUBTYPE message IS varchar2(100);

salutation name;

greetings message;

BEGIN salutation := 'Reader ';

greetings := 'Welcome to the World of PL/SQL';

dbms_output.put_line('Hello ' || salutation || greetings);

END;

When the above code is executed at the SQL prompt, it produces the following result

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

NULLs in PL/SQL

PL/SQL NULL values represent missing or unknown data and they are not an integer, a character, or any other specific data type. Note that NULL is not the same as an empty data string or the null character value ‘\0’. A null can be assigned but it cannot be equated with anything, including itself.

 

Advertisements