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

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

PL/SQL Data Types define how Oracle stores, handles, and constrains each value in a block. They group into character, number, Boolean, date, and large object (LOB) families, each with its own storage format, size limits, and assignment rules.

  • 🔤 Character: CHAR is fixed length, VARCHAR2 is variable length, and NCHAR and NVARCHAR2 use the national character set.
  • 🔢 Number: NUMBER stores fixed or floating point values up to 38 digits of precision.
  • ✅ Boolean: Holds TRUE or FALSE and is used mainly in conditional logic.
  • 📅 Date: Stores date and time, defaulting to midnight when no time is given.
  • 🗂️ LOB: BLOB, CLOB, NCLOB, and BFILE store large unstructured data up to 128 TB.
  • 💡 Best Practice: Prefer VARCHAR2 over CHAR and LOB over LONG to save memory and stay future-proof.
  • 🔗 Scope: SQL types apply to table columns; PL/SQL types are used inside PL/SQL blocks.
Read More

Oracle PL/SQL Data Types

What are PL/SQL Data Types?

Data types in PL/SQL define how data will be stored, handled, and treated by Oracle during storage and processing. Data types are associated with a specific storage format and range constraints. In Oracle, each value or constant is assigned a data type.

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

Following is a diagram of the different Oracle PL/SQL data types:

Different Data Types in PL/SQL
Different Data Types in PL/SQL

PL/SQL CHARACTER Data Type

This data type stores alphanumeric characters in string format. The literal values should always be enclosed in single quotes when assigning them to a 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
Data Type Description Syntax
CHAR This data type stores a string value, and the size of the string is fixed at the time of declaring the variable.

  • Oracle blank-pads the variable if it does not occupy the entire declared size, so Oracle allocates the memory for the declared size even if the variable does not fill it.
  • The size restriction is 1-2000 bytes.
  • CHAR is more appropriate wherever a fixed size of data is handled.
 
grade CHAR;
manager CHAR (10):= 'guru99';

Syntax Explanation:

  • The first statement declares the variable ‘grade’ of CHAR data type with the maximum size of 1 byte (default value).
  • The second statement declares the variable ‘manager’ of CHAR data type with a maximum size of 10 and assigns the value ‘guru99’, which is 6 bytes. Oracle allocates 10 bytes rather than 6 in this case.
VARCHAR2 This data type stores a string, but the length is not fixed.

  • The size restriction is 1-4000 bytes for a table column and 1-32767 bytes for a variable.
  • The size is defined for each variable at declaration.
  • Oracle allocates memory only after the variable is defined, so it uses the actual length of the stored string rather than the declared size.
  • It is always good to use VARCHAR2 instead of CHAR to optimize memory usage.
 
manager VARCHAR2(10) := 'guru99';

Syntax Explanation:

  • The statement declares the variable ‘manager’ of VARCHAR2 data type with a maximum size of 10 and assigns ‘guru99’, which is 6 bytes. Oracle allocates memory of only 6 bytes here.
VARCHAR This is synonymous with the VARCHAR2 data type.

  • It is always good practice to use VARCHAR2 instead of VARCHAR to avoid behavioral changes.
 
manager VARCHAR(10) := 'guru99';

Syntax Explanation:

  • The statement declares the variable ‘manager’ of VARCHAR data type with a maximum size of 10 and assigns ‘guru99’, 6 bytes. Oracle allocates only 6 bytes, similar to VARCHAR2.
NCHAR This data type is the same as CHAR, but the character set is the national character set.

  • This character set can be defined for the session using NLS_PARAMETERS.
  • The character set can be either UTF16 or UTF8.
  • The size restriction is 1-2000 bytes.
 
native NCHAR(10);

Syntax Explanation:

  • The statement declares the variable ‘native’ of NCHAR data type with a maximum size of 10.
  • The length depends on the number of bytes per character defined in the character set.
NVARCHAR2 This data type is the same as VARCHAR2, but the character set is the national character set.

  • This character set can be defined for the session using NLS_PARAMETERS.
  • The character set can be either UTF16 or UTF8.
  • The size restriction is 1-4000 bytes.
 
Native_var NVARCHAR2(10):= 'guru99';

Syntax Explanation:

  • The statement declares the variable ‘Native_var’ of NVARCHAR2 data type with a maximum size of 10.
LONG and LONG RAW This data type stores large text or raw data up to a maximum size of 2 GB.

  • These are mainly used in the data dictionary.
  • LONG stores character set data, while LONG RAW stores data in binary format.
  • LONG RAW accepts media objects and images, whereas LONG works only on data that can be stored using a character set.
 
Large_text LONG;
Large_raw LONG RAW;

Syntax Explanation:

  • The statement declares ‘Large_text’ of LONG data type and ‘Large_raw’ of LONG RAW data type.

Note: Using the LONG data type is not recommended by Oracle. The LOB data type should be preferred instead.

RELATED ARTICLES

PL/SQL NUMBER Data Type

This data type stores fixed or floating point numbers up to 38 digits of precision. It is used to work with fields that contain only number data. The variable can be declared either with precision and decimal digit details or without them. Values need not be enclosed in quotes when assigned.

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

Syntax Explanation:

  • The first declaration declares the variable ‘A’ of number data type with total precision 8 and 2 decimal digits.
  • The second declares ‘B’ of number data type with total precision 8 and no decimal digits.
  • The third is the most generic, declaring ‘C’ of number data type with no restriction on precision or decimals. It can take up to 38 digits.

PL/SQL BOOLEAN Data Type

This data type stores logical values. The Oracle Boolean data type represents either TRUE or FALSE and is used mainly in conditional statements. Values need not be enclosed in quotes when assigned.

Var1 BOOLEAN;

Syntax Explanation:

  • The variable ‘Var1’ is declared as BOOLEAN data type. The output will be either true or false based on the condition set.

PL/SQL DATE Data Type

This data type stores values in date format, as date, month, and year. Whenever a variable is defined with DATE data type, it can hold time information; by default the time is set to 12:00:00 if not specified. Values need to be enclosed in quotes when assigned.

The standard Oracle time format for input and output is ‘DD-MON-YY’, which is set at NLS_PARAMETERS (NLS_DATE_FORMAT) at the session level.

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

Syntax Explanation:

  • The variable ‘newyear’ is declared as DATE data type and assigned the value of 1 January 2015.
  • The second declaration declares ‘current_date’ as DATE data type and assigns the current system date.
  • Both variables hold time information.

PL/SQL LOB Data Type

This data type is mainly used to store and manipulate large blocks of unstructured data such as images and multimedia files. Oracle prefers LOB over the LONG data type because it is more flexible. The main advantages of LOB over LONG are:

  • A table is limited to one LONG column, whereas there is no restriction on the number of LOB columns.
  • Data interface tools accept LOB during replication but omit LONG columns, which must be replicated manually.
  • A LONG column stores up to 2 GB, whereas a LOB can store up to 128 TB.
  • Oracle keeps improving the LOB data type with each release, whereas LONG is largely unchanged.

So it is always good to use the LOB data type instead of LONG. The different LOB data types below can store up to 128 terabytes:

  1. BLOB
  2. CLOB and NCLOB
  3. BFILE
Data Type Description Syntax
BLOB This data type stores LOB data in binary file format up to a maximum of 128 TB. It does not store data based on character set details, so it can store unstructured data such as multimedia objects and images. 
Binary_data BLOB;

Syntax Explanation:

  • The variable ‘Binary_data’ is declared as a BLOB.
CLOB and NCLOB CLOB stores LOB data in the character set, whereas NCLOB stores data in the native character set. Because these use character set based storage, they cannot store data like multimedia or images. The maximum size is 128 TB. 
Charac_data CLOB;

Syntax Explanation:

  • The variable ‘Charac_data’ is declared as CLOB data type.
BFILE
  • BFILE stores unstructured binary data outside the database as an operating-system file.
  • The size of a BFILE is limited by the operating system, and the files are read-only and cannot be modified.

FAQs

CHAR is fixed length and blank-pads to the declared size, wasting space on short values. VARCHAR2 is variable length and stores only the actual characters, so it is preferred for most string columns.

LOB allows many columns per table, stores up to 128 TB, and keeps improving each release. LONG is limited to one column, caps at 2 GB, and is retained mainly for backward compatibility.

Not in older releases. BOOLEAN was a PL/SQL-only type, so table columns used a NUMBER or CHAR flag instead. It is used directly inside blocks for conditional logic.

Yes. By profiling sample values for length, range, and format, AI can suggest VARCHAR2, NUMBER, DATE, or a LOB, and flag columns that are over-sized. Confirm the choice against future data.

Yes. DATE stores century, year, month, day, hour, minute, and second. If no time is given, it defaults to midnight. For fractional seconds or time zones, use TIMESTAMP instead.

Summarize this post with: