PL/SQL Data Types

In this lesson, you will learn PL/SQL Data Types. PL/SQL variables and constants must have a valid data type. Which specifies storage format. There are six built-in PL/SQL data types

  1. Scalar data types - Scalar data types haven't internal components.
  2. Composite data types - Composite data types have internal components to manipulate data easily.
  3. Reference data types - This data types work like a pointer to hold some value.
  4. LOB data types - Stores large objects such as images, graphics, video.
  5. Unknown Column types - Identify columns when not know the type of column.
  6. User Define data types - Define your own data type that inherited from predefined base data type.

PL/SQL Data Types

Scalar types

Scalar data type haven't internal components. It is like a linear data type. Scales data type divides into four different types character, numeric, boolean or date/time type.

Numeric Data types

Following are numeric data types in PL/SQL:

Datatype Description, Storage(Maximum)
NUMBER(p,s) NUMBER data type used to store numeric data.
It contains letters, numbers, and special characters.
Storage Range: Precision range(p): 1 to 38 and Scale range(s) : -84 to 127
NUMBER Subtypes: This sub type defines different types storage range.
Sub Datatype Maximum Precision Description
INTEGER 38 digits These data types are used to store fixed decimal points. You can use based on your requirements.
INT 38 digits
SMALLINT 38 digits
DEC 38 digits
DECIMAL 38 digits
NUMERIC 38 digits
REAL 63 binary digits
DOUBLE PRECISION 126 binary digits
FLOAT 126 binary digits
BINARY_INTEGER BINARY_INTEGER data type store signed integer's value.
Note: BINARY_INTEGER values require less storage space compare of NUMBER data type values.
Storage Range: from -2147483647 to 2147483647
BINARY_INTEGER Subtypes: This sub type defines constraint to store a value.
Sub Datatype Description
NATURAL NATURAL/POSITIVE data type prevent to store negative value, allow only positive values.
POSITIVE
NATURALN NATURALN/POSITIVEN data type prevent to assign a NULL value.
POSITIVEN
SIGNTYPE SIGNTYPE allow only -1, 0, and 1 values.
PLS_INTEGER PLS_INTEGER data type used to store signed integers data.
Note PLS_INTEGER data type value require less storage space compare of NUMBER data type value.
Storage Range: from -2147483647 to 2147483647
Performance: PLS_INTEGER data type gives you better performance on your data. PLS_INTEGER perform arithmetic operation fast than NUMBER/BINARY_INTEGER data type.

Character Data types

Character Data types used to store an alphabetic/alphanumeric character. Following are some character data types in PL/SQL,

Datatype Description Storage(Maximum)
CHAR CHAR data type used to store character data within a predefined length. 32767 bytes
CHARACTER CHARACTER data type same as CHAR data type. It is another name of CHAR data type. 32767 bytes
VARCHAR2 VARCHAR2 data type used to store variable strings data within a predefined length.
VARCHAR2 Subtypes: the Following subtype defines the same length value.
Sub Datatype Description
STRING We can access this data type.
VARCHAR
32767 bytes
NCHAR NCHAR data type used to store national character data within a predefined length. 32767 bytes
NVARCHAR2 NVARCHAR2 data type used to store Unicode string data within a predefined length. 32767 bytes
RAW The RAW data type used to store binary data such as images, graphics, etc. 32767 bytes
LONG LONG data type used to store variable string data within a predefined length, This data type used for backward compatibility. Please use LONG data to the CLOB type. 32760 bytes
LONG RAW LONG RAW data type same as LONG type used to store variable string data within a predefined length, This data type used for backward compatibility.
Use LONG RAW data type for storing BLOB type data.
32760 bytes
ROWID The ROWID data type represents the actual storage address of a row. And table index identities as a logical rowid. This data type used to storing backward compatibility. We strongly recommended to use UROWID data type.
UROWID[(size)] The UROWID data type identifies as universal rowid, same as ROWID data type. Use UROWID data type for developing newer applications.
Optional, You can also specify the size of UROWID column type.
4000 bytes

Boolean Data types

Boolean Data types stores logical values either TRUE or FALSE. Let's see Boolean data types in PL/SQL:

Datatype Description
Boolean Boolean data type stores logical values. Boolean data types doesn't take any parameters.
Boolean data type store, either TRUE or FALSE. Also, store NULL, Oracle treats NULL as an unassigned boolean variable.
You can not fetch boolean column value from another table.

Date/Time Datatypes

A variable that has date/time data type hold value call datetimes. Oracle SQL automatically converts character value into default date format ('DD-MON-YY') TO_DATE values. Following are Date/Time data types in Oracle SQL.

Datatype Description Range
DATE DATE data type to store valid date-time format with a fixed length. Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD. Jan 1, 4712 BC to
Dec 31, 9999 AD
TIMESTAMP TIMESTAMP data type to store valid date (year, month, day) with time (hour, minute, second).
Type TIMESTAMP Type
1 Syntax: TIMESTAMP [(fractional_seconds_precision)]
Example: TIMESTAMP '2014-04-13 18:10:52.124'
fractional_seconds_precision optionally specifies the number of digits in the fractional part of the second precision datetime field. Range 0 to 9. The default is 6.
2 Syntax: TIMESTAMP [(fractional_seconds_precision) ] WITH TIME ZONE
Example: TIMESTAMP '2014-04-13 18:10:52.124 +05:30'
WITH TIME ZONE specify the UTC time zone. Following two values represent same instant in UTC.
TIMESTAMP '1999-04-15 8:00:00 -8:00' (8.00 AM Pacific Standard Time) or
TIMESTAMP '1999-04-15 11:00:00 -5:00' (11:00 AM Eastern Standard Time) both are same.
3 Syntax: TIMESTAMP [(fractional_seconds_precision) ] WITH LOCAL TIME ZONE
Example: COL_NAME TIMESTAMP(3) WITH LOCAL TIME ZONE;
WITH LOCAL TIME ZONE specify when you insert value into database column, value is store to the database time zone.
The time-zone displacement is not stored in the column. When you retrieve value oracle database returns it in your UTC local time zone.

Following are the Interval data types in PL/SQL:

Datatype Description
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH data type is used to store and manipulate intervals of year and month. Syntax: INTERVAL YEAR [ (precision) ] TO MONTH
Note: precision specifies the number of digits in the year's field range from 0 to 4 and default is 2.
Example: following example declares variable data type INTERVAL YEAR TO MONTH and assign interval 45 years and 7 months.
DECLARE
   inter INTERVAL YEAR(2) TO MONTH;
BEGIN
   inter := INTERVAL '45-7' YEAR TO MONTH;
   inter := '45-7'; -- assign from character type (implicit conversion)
   inter := INTERVAL '7' MONTH; -- Specify Months   
   inter := INTERVAL '45' YEAR; -- Specify years
END;
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND data type is used to store and manipulate intervals of days, hours, minutes, and seconds.
Syntax: INTERVAL DAY [ (leading_precision ) ] TO SECOND[(fractional_seconds_precision)]
Note: leading_precision and fractional_seconds_precision specifies number of digits in days field range from 0 to 9. The defaults are 2 and 6.
Example: following example declare variable data type INTERVAL DAY TO SECOND.
DECLARE
   inter INTERVAL DAY(3) TO SECOND(3);
BEGIN
   IF inter > INTERVAL '6' DAY ...
   ...
END;

In PL/SQL datetime data type or interval data type fields values show the valid values for each field.

Field Name Valid Value Valid Interval Value
YEAR -4712 to 9999 Integer Value exclude 0
MONTH 01 to 12 0 to 11
DAY 01 to 31 Integer Value exclude 0
HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
SECOND 00 to 59.9(n) here n is precision of time fractional seconds 0 to 59.9(n)

Large Object Datatypes (LOB types)

LOB data types use to store large object such as image, video, graphics, text, audio. Maximum size up to 4 Gigabytes. Following are LOB data types in SQL.

Datatype Description Storage(Maximum)
BFILE BFILE data type to store a large binary object into Operating System file. This data type variable store full file locator's path, which points to a stored binary object within a server. BFILE data type read-only, you can't modify them. Size: up to 4GB (232 - 1 byte)
Directory name: 30 character
File name: 255 characters
BLOB BLOB data type same as BFILE data type to store an unstructured binary object into Operating System file. BLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE
CLOB CLOB data type to store large blocks of character data into Database. Store single byte and multi-byte character data. CLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE
NCLOB NCLOB data type to store large blocks of NCHAR data into Database. Store single byte and multi-byte character data. NCLOB type fully supported transactions are recoverable and replicated. Size: 8 TB to 128 TB
(4GB - 1) * DB_BLOCK_SIZE

Unknown Column types

PL/SQL this data type is used when column type is not know.

Datatype Description
%Type This data type is used to store value unknown data type column in a table. The column is identified by %type data type.
Eg. emp.eno%type
emp name is a table,
eno is an unknown data type column and
%Type is data type to hold the value.
%RowType This data type is used to store values unknown data type in all columns in a table. All columns are identified by %RowType datatype.
Eg. emp%rowtype
emp name is a table,
all column type is %rowtype.
%RowID RowID is data type. RowID is two types extended or restricted.
Extended return 0 and restricted return 1 otherwise return the row number.
Function of Row ID:
Function RowID Description
ROWID_Verify Verify if the rowid can be extended.
ROWID_Type 0 = rowid, 1 = extended.
ROWID_Block_Number Block number that contain the record return 1 extended.
ROWID_Object Object number of the object that contain record.
ROWID_Relative_FNumber Relative file number that contain record.
ROWID_Row_Number Row number of the Record.
ROWID_To_Absolute_FNumber Return the absolute file number.
ROWID_To_Extended Convert the extended format.
ROWID_To_Restricted Convert the restricted format.

User-Defined Subtypes

PL/SQL gives you the control to create your own sub data type that inherited from a predefined base type. Subtypes can increase reliability and provide compatibility with ANSI/ISO type. Several predefined subtypes are in a STANDARD package.

Defining Subtypes

You can define your own subtypes in the declarative part of PL/SQL block using the following syntax,

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

Following the example, predefined data type inherits from CHARACTER and INTEGER data type to make a new sub type,

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(10,4);  -- allows for numbers

Example

DECLARE
   SUBTYPE message IS varchar2(25);
   SUBTYPE age IS INTEGER(2,0);
   description message;
   ages age;
BEGIN
   description := 'Web Developer';
   ages := 22;
   dbms_output.put_line('I am ' || description || ' and I am ' || ages || ' years Old.');
END;
/

Result

I am Web Developer and I am 22 years Old.
PL/SQL procedure successfully completed.