In this article, we will explain the Data Types PL/SQL. If you missed the introduction part of PL/SQL, then click here.
Introduction to data types in PL/SQL
Each value in PL/SQL like constant, variable, and parameter has a data type. That determines the storage format, valid values, and allowed operations in PL/SQL. For instance, you can choose from the integer, floating-point, character, boolean, date, collection, reference, and large object (LOB) types. This article covers the basic types used frequently in PL/SQL programs.
PL/SQL has two types of data types: scalar and composite. The scalar types are nothing but a type that store single values such as number, Boolean, character, and DateTime, whereas the composite types stores multiple values, for example, record and collection.
The Scalar Type in data type PL/SQL:
PL/SQL divides the scalar data types into four parts:
The The PL/SQL composite datatype: types:
The PL/SQL Composite types have internal components that can be handle individually, such as the elements of an array, record, or table.
Oracle TimesTen In-Memory Database helps the following composite data types:
- An associative array (index-by table)
- Nested table
List of Datatypes in PL SQL
The below mentioned is a list of data types available in Oracle PL/SQL, including character, numeric, date/time, LOB, and rowid datatypes.
CHARACTER Data Types pl/sql:
This character data type is additionally categorized as follows:
- CHAR Data types (fixed string size)
- VARCHAR2 Data types (variable string size)
- VARCHAR Data types
- NCHAR (native defined string size)
- NVARCHAR2 (native varying string size)
- LONG and LONG RAW.
CHAR Data types:
This data type mainly stores the string value, and the size of the string is fixed at the time of declaring the variable. As like every datatype has size restriction so, the size restriction for CHAR data type between 1-2000 bytes. The PL/SQL CHAR data type is more appropriate to use where ever fixed the size of data will be handled.
grade CHAR; manager CHAR (10):= 'technical blog';
VARCHAR2 Data type (variable string size):
This data type stores the string, but the length of the string is not fixed. The size restriction for the VARCHAR2 is 1-4000 bytes for table column size and 1-32767 bytes for variables. Its size defines for each variable at the time of variable declaration.
name VARCHAR2(10):= 'Lucine';
VARCHAR Data type:
This is synonymous with the VARCHAR2 data type. VARCHAR can hold 1 to 255 characters. It is always good to use VARCHAR2 instead of VARCHAR to avoid behavioural changes.
name VARCHAR(10) := ‘Lucine';
NCHAR (native fixed string size):
Fixed-length of the national character string is the maximum size of 32,767 bytes.
NVARCHAR2 (native variable string size):
The Variable-length national character string with a maximum size of 32,767 bytes. The NVARCHAR2 is used to store Unicode string data within a predefined length.
Native var NVARCHAR2(10):='Lucine';
LONG and LONG RAW :
This data type mainly uses for large store text or raw data up to the maximum size of 2GB. The LONG RAW data type is used for storing BLOB type data. These are mainly used in the data dictionary.
The LONG data type uses to store character set data, while LONG RAW uses to store data in binary format.
Large_text LONG; Large_raw LONG RAW;
The numeric data types represent real numbers, integers, and floating-point numbers. All numeric data types are stored as NUMBER, IEEE floating-point storage types (BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.
The data types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE, these are SQL data types. Here a list is mentioned the different numeric datatypes.
|S.No||Data Type & Description|
|1||PLS_INTEGER: integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits|
|2||BINARY_INTEGER: this integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits|
|3||BINARY_FLOAT: This is single-precision IEEE 754-format floating-point number|
|4||BINARY_DOUBLE: It is defined as double-precision IEEE 754-format floating-point number|
|5||NUMBER(prec, scale): Numbers are either fixed or floating-point with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0|
|6||DEC(prec, scale): ANSI distinct to fixed-point type with maximum precision of 38 decimal digits|
|7||DECIMAL(prec, scale): IBM special fixed-point type with maximum precision of 38 decimal digits|
|8||NUMERIC(pre, secale): Numerics are floating type with maximum precision of 38 decimal digits|
|9||DOUBLE PRECISION: ANSI distinct floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)|
|10||FLOAT: ANSI and IBM refers to floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)|
|11||INT: ANSI defines the integer type with maximum precision of 38 decimal digits|
|12||INTEGER: ANSI and IBM specific integer type with maximum precision of 38 decimal digits|
|13||SMALLINTANSI and IBM specific integer type with maximum precision of 38 decimal digits|
|14||REAL: Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)|
BOOLEAN Data Type:
The BOOLEAN data type is stored with logical values that are used in logical operations. It represents either TRUE or FALSE and is mainly used in conditional statements. Values need not enclose within quotes while assigning for this data type.
Syntax Explanation: In the above, the variable ‘Var1’ is declared as the BOOLEAN data type. The output will be true or false based on the condition set.
DATE/TIME Data Type:
In PL/SQL, the DATE datatype is used to store fixed-length DateTime, including the time of day in seconds since midnight. The Valid dates format range from January 1, 4712 BC to December 31, 9999 AD. This data type is stored with the values in date format, as of date, month, and year.
The standard Oracle time format for input and output is ‘DD-MON-YY,’ and it is again set at NLS_PARAMETERS (NLS_DATE_FORMAT) at the session-level. For instance, 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, 02-OCT-20.
new year DATE:='01-JAN-2015'; current_date DATE:=SYSDATE;
LOB Data Type:
The LOB (Large Object) data types relate to large data items such as text, graphic images, video clips, and sound waveforms. Oracle prefers LOB instead of the LONG data type, as it is more flexible than the LONG data type. The following are the predefined PL/SQL LOB data types −
|BFILE||It is used to store substantial binary objects in operating system files outside the database.||System-dependent. It cannot exceed 4 gigabytes (GB).|
|BLOB||It is mainly used for storing large binary objects in the database.||8 to 128 terabytes (TB)|
|CLOB||CLOB uses for storing large blocks of character data in the database.||8 to 128 TB|
|NCLOB||It is applied to store large blocks of NCHAR data in the database.||8 to 128 TB|
Answer: A subtype is a data type which is a subset of another data type, which is its base type. A subtype data type further defines a base type by restricting the value or size of the base data type.
Note: PL/SQL scalar data types include SQL data types and their own data type such as Boolean.
Answer: The major difference is:-
CHAR uses for storing fixed-length character strings. It will allocate a lot of disk space if this type is used to store variable-length strings.
VARCHAR2 uses to store variable length character strings.
Answer: PL/SQL NULL values represent missing or unknown data and they are not an integer, a character, or any other specific data type
Here, we have discussed the data type PL/SQL in Oracle PL/SQL Tutorial. In the future post, we will discuss the variable in PL/SQL. I hope this article will full fill your queries regarding datatypes in PL SQL.
Thank You 🙂