Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL









PL/SQL Data Types - Oracle


As you know about the variables and constants stores value in specific 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 works 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 type of column.
  6. User Define data types - Define your own data type that are inherit from predefined base data type.

PL/SQL Data Types -Oracle


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:

Data types Description, Storage(Maximum)
NUMBER(p,s) NUMBER data type used to store numeric data.
It's contain 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 Data types Maximum Precision Description
INTEGER 38 digits This 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 define constraint to store a value.
Sub Data types 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 give 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 alphabetic/alphanumeric character. Following are some character data types in PL/SQL,

Data types Description Storage(Maximum)
CHAR CHAR data type used to store character data within predefined length. 32767 bytes
CHARACTER CHARACTER data type same as CHAR type, is this another name of CHAR type. 32767 bytes
VARCHAR2 VARCHAR2 data type used to store variable strings data within predefined length.
VARCHAR2 Subtypes : Following sub type defines same length value.
Sub Data types Description
STRING We can access this data type.
VARCHAR
32767 bytes
NCHAR NCHAR data type used to store national character data within predefined length. 32767 bytes
NVARCHAR2 NVARCHAR2 data type used to store Unicode string data within 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 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 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:

Data types 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 Data types

Date/time variable can holds value, we can say date/time data type. PL/SQL automatically converts character value in to default date format ('DD-MM-YY') value. Following are the Date/Time data types in PL/SQL:

Data types Description Range
DATE DATE data type stores valid date-time format with 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 stores 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. Range from 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 the 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 specifies when you insert values into the database column, value is stored with the time zone of the database.
The time-zone displacement is not stored in the column. When you retrieve value from Oracle database, returns it according to your UTC local time zone.

Following are the Interval data types in PL/SQL:

Syntax : INTERVAL YEAR [ (precision) ] TO MONTH
Note : precision specifies number of digits in years field range 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 month.
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;
Data types Description
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH data type is used to store and manipulate intervals of year and month.
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)

LOB types

LOB data types used to store large objects such as image, video, graphics, text or audio. Maximum size is up to 4 Gigabytes. Following are the LOB data types in PL/SQL:

Data types Description Storage(Maximum)
BFILE BFILE data type is used to store large binary objects into Operating System file. BFILE stores full file locator's path which are points to a stored binary object with in server. BFILE data type is read only, you can't modify them. Size: up to 4GB (232 - 1 bytes)
Directory name: 30 character
File name: 255 characters
BLOB BLOB data type is same as BFILE data type used to store 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 is used to store large blocks of character data into Database. Store single byte and multi byte character data. CLOB type is fully supported transactions, also that 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. NCLOB data type is used to store large blocks of NCHAR data into Database. Store single byte and multi byte character data. NCLOB type fully supported transactions, also that 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.

Data types Description
%Type This data type is used to store value unknown data type column in a table. Column is identified by %type data type.
Eg. emp.eno%type
emp name is table,
eno is a 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 table,
all column type is %rowtype.
%RowID RowID is data type. RowID is two type 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 are inherit from predefined base type. Sub types can increase reliability and provide compatibility with ANSI/ISO type. Several predefined subtypes are in STANDARD package.

Defining Subtypes

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

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

Following example, predefined data type inherit from CHARACTER and INTEGER data type to make a new sub types,

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.