Data Types PL/SQL -PL/SQL Tutorial

In this article, we will explain the Data Types PL/SQL. If you missed the introduction part of PL/SQL, then click here.

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.

PL/SQL divides the scalar data types into four parts:

1. Number
2. Boolean
3. Character
4. Datetime

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
• Varray
• Record

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.

Syntax:

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.

Syntax:

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.

Syntax:

name VARCHAR(10) := ‘Lucine';

NCHAR (native fixed string size):

Fixed-length of the national character string is the maximum size of 32,767 bytes.

Syntax:

native NCHAR(10);

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.

Syntax:

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.

Syntax:

Large_text LONG;
Large_raw LONG RAW;

Numeric Datatypes:

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.

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.

Var1 BOOLEAN;

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.

Syntax:

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 −

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 🙂