Oracle PL/SQL Data Types: Boolean, Number, Date [Example]

What is PL/SQL Datatypes?

A data type is associated with the specific storage format and range constraints. In Oracle, each value or constant is assigned with a data type.

Basically, it defines how the data is stored, handled and treated by Oracle during the data storage and processing.

The main difference between PL/SQL and SQL data types is, SQL data type are limited to table column while the PL/SQL data types are used in the PL/SQL blocks. More on this later in the tutorial.

Following is the diagram of different Data Types in PL/SQL

In this tutorial, you will learn-

• CHARACTER Data Type
• NUMBER Data Type
• BOOLEAN Data Type
• DATE Data Type
• LOB Data Type

CHARACTER Data Type:

This data type basically stores alphanumeric characters in string format.

The literal values should always be enclosed in single quotes while assigning them to CHARACTER data type.

This character data type is further classified as follows:

• CHAR Data type (fixed string size)
• VARCHAR2 Data type (variable string size)
• VARCHAR Data type
• NCHAR (native fixed string size)
• NVARCHAR2 (native variable string size)
• LONG and LONG RAW

grade CHAR;
manager CHAR (10):= 'gtupapers';

manager VARCHAR2(10) := ‘gtupapers';

manager VARCHAR(10) := ‘gtupapers';

native NCHAR(10);

Native var NVARCHAR2(10):='gtupapers';

Large_text LONG;
Large_raw LONG RAW;

NUMBER Data Type:

This data type stores fixed or floating point numbers up to 38 digits of precision. This data type is used to work with fields which will contain only number data. The variable can be declared either with precision and decimal digit details or without this information. Values need not enclose within quotes while assigning for this data type.

A NUMBER(8,2);
B NUMBER(8);
C NUMBER;

Syntax Explanation:

• In the above, the first declaration declares the variable 'A' is of number data type with total precision 8 and decimal digits 2.
• The second declaration declares the variable 'B' is of number data type with total precision 8 and no decimal digits.
• The third declaration is the most generic, declares variable 'C' is of number data type with no restriction in precision or decimal places. It can take up to a maximum of 38 digits.

BOOLEAN Data Type:

This data type stores the logical values. It represents either TRUE or FALSE and mainly used in conditional statements. Values need not enclose within quotes while assigning for this data type.

Var1 BOOLEAN;

Syntax Explanation:

• In the above, variable 'Var1' is declared as BOOLEAN data type. The output of the code will be either true or false based on the condition set.

DATE Data Type:

This data type stores the values in date format, as date, month, and year. Whenever a variable is defined with DATE data type along with the date it can hold time information and by default time information is set to 12:00:00 if not specified. Values need to enclose within quotes while assigning for this data type.

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.

newyear DATE:='01-JAN-2015';
current_date DATE:=SYSDATE;

Syntax Explanation:

• In the above, variable 'newyear' is declared as DATE data type and assigned the value of Jan 1^st, 2015 date.
• The second declaration declares the variable current_date as DATE data type and assigned the value with current system date.
• Both these variable holds the time information.

LOB Data Type:

This data type is mainly used to store and manipulate large blocks of unstructured data's like images, multimedia files, etc. Oracle prefers LOB instead of the a LONG data type as it is more flexible than the LONG data type. The below are the few main advantage of LOB over LONG data type.

• The number of column in a table with LONG data type is limited to 1, whereas a table has no restriction on a number of columns with LOB data type.
• The data interface tool accepts LOB data type of the table during data replication, but it omits LONG column of the table. These LONG columns need to be replicated manually.
• The size of the LONG column is 2GB, whereas LOB can store up to 128 TB.
• Oracle is constantly improving the LOB data type in each of their releases according to the modern requirement, whereas LONG data type is constant and not getting many updates.

So, it is always good to use LOB data type instead of the LONG data type. Following are the different LOB data types. They can store up to the size of 128 terabytes.

1. BLOB
2. CLOB and NCLOB
3. BFILE

Binary_data BLOB;

Charac_data CLOB;

Summary

We have covered the different simple data types that are available in PL/SQL along with their syntax. We will learn about complex data types in further topics.