PL/SQL Data Types

Did you know that effectively storing and managing data in Oracle databases relies heavily on choosing the appropriate PL/SQL data types? With various data types in PL/SQL, understanding their usage, syntax, and limitations can significantly enhance your database application’s performance and efficiency. By mastering these data types, you can create and maintain powerful and robust Oracle database applications, ensuring your application remains scalable, flexible, and efficient. 

In this blog post, we will delve into the various data types available in PL and SQL and provide a clear understanding of their usage, syntax, and limitations. 

What are PL/SQL Data Types?

These data types are fundamental elements used to define how data is stored, managed, and processed within Oracle databases. They are closely associated with specific storage formats and range constraints. In PLSQL, data types are usually grouped into four types, including scalar data types, composite data types, LOB, and reference data types. We will look into each of these in more detail below.

Scalar Data Types

Scalar data types are the ones that store a single value, like a character, date, boolean, or number. They are further classified into the following types:

Character Data Types

These data types hold alphanumeric data representing a single or group of characters. They are sub-categorized into the following:

a.  CHAR Data Type

It stores strings with a set length. Oracle automatically fills any remaining space up to the defined maximum of 2000 bytes when storing shorter strings. This makes it ideal for scenarios where fixed-sized data will be handled, such as gender information (e.g., ‘M’ or ‘F’) and unique codes. 

b.  VARCHAR2 Data Type 

It is used to store string values of varying lengths. Memory allocation for these only depends on the actual size rather than a pre-defined maximum, making it more efficient in usage. The capacity ranges from 1 – 4000 bytes when stored as table columns and 1 – 32767 if set as variables within the code. This data type is ideal for saving variable-length information, such as names, addresses, or descriptions that may contain characters beyond those found in Latin scripts.

c.  VARCHAR Data Type 

VARCHAR and VARCHAR2 are virtually the same. However, Oracle recommends the usage of the latter to prevent potential changes in behavior with future releases. Both types can store strings of varying length; capacity-wise, they share a range from 1 – 4000 bytes if stored as table columns and from 1 – 32767 when set as variables within code.

d.  NCHAR Data Type  

The NCHAR data type is for saving fixed-length, language-specific information. This can include characters from non-Latin-based scripts. It utilizes a national character set, which is determined using the session’s NLS_PARAMETERS settings. This can either be UTF16 or UTF8 encoding of size limits ranging between 1 to 2000 bytes in length. Using this makes it ideal when dealing with unique script symbols due to its reliability as well as accuracy in representation.

e. NVARCHAR2 Data Type 

NVARCHAR2 is similar to VARCHAR2 but uses the national character set, which can be UTF16 or UTF8. It has a capacity of up to 4000 bytes when stored as table columns and 32767 max if used as variables in code. This data type makes it suitable for saving varying-length information such as names and addresses with special characters from non-Latin scripts.

Given below is a tabular description of the scalar data types.

Data Type	Description	Syntax Example
CHAR	Fixed string size, blank-padded if not fully occupied.	grade CHAR; manager CHAR(10) := ‘guru99’;
VARCHAR2	Variable string size allocates memory for actual string length.	manager VARCHAR2(10) := ‘guru99’;
VARCHAR	Synonymous with VARCHAR2.	manager VARCHAR(10) := ‘guru99’;
NCHAR	Native fixed string size uses national character set.	native NCHAR(10);
NVARCHAR2	Native variable string size, uses national character set.	Native_var NVARCHAR2(10) := ‘guru99’;
LONG	Stores large text data up to 2GB.	Large_text LONG;
LONG RAW	Stores large binary data up to 2GB.	Large_raw LONG RAW;

NUMBER Data Types

These are super-type for all the numeric types in PL/SQL. They can store both integers and floating point numbers with up to 38 digits of precision. This internal system data type is slower than other numerical formats due to its construction as an internal Oracle component. However, it remains highly portable compared to different languages, providing accuracy at the cost of performance. Predefined subtypes that fall within this category include Integer (INT), Numeric (DECIMAL), Float (DOUBLE PRECISION) & Real – each having its specific constraints on number format and scale values.

You can also control the precision and scale constraints on this data type. For example, a Numeric (29,3) is equivalent to a Number (29, 3). The float DOUBLE PRECISION option allows capturing up to 126 digits before any rounding takes place; where Real has a maximum capacity of 63 non-rounded numbers.

BOOLEAN Data Types

These are super-type for all the numeric types in PL/SQL. They can store both integers and floating point numbers with up to 38 digits of precision. This internal system data type is slower than other numerical formats due to its construction as an internal Oracle component. However, it remains highly portable compared to different languages, providing accuracy at the cost of performance. Predefined subtypes that fall within this category include Integer (INT), Numeric (DECIMAL), Float (DOUBLE PRECISION) & Real – each having its specific constraints on number format and scale values.

You can also control the precision and scale constraints on this data type. For example, a Numeric (29,3) is equivalent to a Number (29, 3). The float DOUBLE PRECISION option allows capturing up to 126 digits before any rounding takes place; where Real has a maximum capacity of 63 non-rounded numbers.

 BOOLEAN Data Types

This PL/SQL data type is used for storing logical values such as TRUE or FALSE. It primarily finds usage in conditional statements. Unlike other data types, no quotation marks are required when assigning a value to this format. Instead, you can simply declare with the syntax  ‘VAR1 BOOLEAN’ where VAR1 would be replaced by your chosen variable name. This will give the output as either true or false depending on how its conditionally set up and utilized within your code. 

DATE Data Types

The PL/SQL DATE data type is used to store information in date format, encompassing day, month, and year. Values must be enclosed by quotation marks when assigning them to this data type. Otherwise, they will not register correctly. Meanwhile, if no time element is specified, the default value of 12:00:00PM will take effect instead. You can learn more about SQL through this comprehensive SQL course.

In terms of a standard Oracle timestamp format for inputting or outputting dates, it’s best recommended that you follow ‘DD-MON-YY’ as per set NLS_PARAMETERS session level rules – e.g.,  “NEWYEAR DATE := ’01-JAN-2015′;” for 1st Jan 2015.

Composite Data Types

These data types are used to store values with internal components. It enables them to be utilized within subprograms and accessed individually through either a syntax such as ‘VARIABLE_NAME(INDEX)’ for individual elements of a collection, or by inputting the component field name when dealing with records – e.g., ‘VARIABLE_NAME.FIELD_NAME’. 

There are two types of composite data type available in PL/SQL: collections & records –  each having its unique capabilities. Both have three identified formats that use associative arrays, nested tables, and VARRAY, respectively. 

LOB Data Types

It stores and manipulates large blocks of unstructured data like images, multimedia files, etc. Oracle recommends using a LOB over the LONG data type for greater flexibility and scalability. The advantages that come with choosing this data type include:

• An unlimited number of columns in comparison to one for LONGs
• Less manual work required when replicating during interface tooling
• Capacity up to 128 TB as opposed to 2 GB for Longs 
• Frequent updates available from Oracle based on modern requirements

A few different LOB data types are: 

• BLOB (binary storage 32TB)
• CLob & NClob- Character Based Storage(128Tb)
• BFile – external binary file stored externally upto OS limitation (read-only)

Reference Types

This is one of the predefined data types in PL/SQL. A reference type can hold values, commonly referred to as pointers. They designate other program objects like REF CURSORS and REFs. With this data type, it’s easy to associate multiple memory locations with a single variable for more efficient use of system resources.

Conclusion

Knowing the various data types in PL/SQL is essential for effectively storing and manipulating Oracle database information. Becoming well versed with PL/SQL data types will lead to stronger outputs when dealing with databases powered by Oracle’s language – PL/SQL.