PL/SQL Data Types

In a PL/SQL block, the declaration section is where you define the elements to be used throughout the block. When declaring an element, you must also specify the kind of data the element will hold as well as its proper name. PL/SQL supports four main groups of data types:

• Scalar: Stores a single value, such as a number, character string, or Boolean.
• Reference: Stores pointers that reference other program items or objects.
• Composite: Stores multiple values that can be accessed and manipulated individually.
• Large Objects (LOBs): Stores lob locators that point to large objects, such as text blocks or graphic images, which are stored separately from other database data. 

Scalar Types

Scalar data types hold a single value, such as numeric, character, or datetime. They are the most basic data types in PL/SQL and provide complete support for SQL's scalar data types to ensure seamless integration with Oracle database.

• Numeric Types: For storing numbers, such as NUMBER. 
• Character Types: For storing text data, such as CHAR or VARCHAR2.
• Datetime Types: For storing date and time information, such as DATE or TIMESTAMP.

In addition, PL/SQL also supports BOOLEAN and PLS_INTEGER data types for procedural data processing. 

• BOOLEAN: Stores logical truth values (true, false, null).
• PLS_INTEGER: Same as BINARY_INTEGER^[1]. Stores signed integers ranging from -2,147,483,648 to 2,147,483,647. Internally, PLS_INTEGER is using 32bits and takes less storage compared to NUMBER data type. 
• NATURAL: This subtype restricts the value range to non-negative integers (0 to 2,147,483,647).
• NATURALN: Similar to NATURAL but disallows null values. Must be initialized at declaration.
• POSITIVE: This subtype allows only positive integers (1 to 2,147,483,647).
• POSITIVEN: Similar to POSITIVE but disallows null values and mandates initialization at declaration.
• SIGNTYPE: This subtype is limited to -1 (representing negative), 0, and 1 (representing positive). Useful for flag-like variables indicating positive, negative, or neutral values.
• SIMPLE_INTEGER: This subtype has the same range as PLS_INTEGER (-2,147,483,648 to 2,147,483,6477) but disallows null values. Must be initialized at declaration.

Declaring and Initializing Scalar Variables

In a declaration section, you can define a scalar variable using the following syntax:

After providing an appropriate variable name^[2], specify the data type to declare a scalar variable. Once declared, it can be initialized and referenced in the executable section. For example:

In this example, a scalar variable named v_text is declared with VARCHAR2(15) type. Next, in the executable section, the string literal 'Hello, PL/SQL!' is assigned to initialize the variable. The variable is then passed into dbms_output.put_line to print out the stored value.

The NOT NULL keyword prevents a scalar variable from holding a null value. In this case, a value must be initialized at the time of declaration; otherwise, Oracle throws an error, stating "PLS-00218: a variable declared NOT NULL must have an initialization assignment." Once initialized you can freely reassign different values to the variable as needed. For example:

Instead of using the NOT NULL and assignment operator (:=), you can also use the DEFAULT keyword in the declaration section. Values following the DEFAULT keyword will be assigned to the variable, until a new value is reassigned to the variable. For example:

Scalar Variables vs. Constants

Similar to a scalar variable, a scalar constant also stores a single value. However, unlike variables, the value assigned to a constant cannot be modified and remains fixed throughout the execution of the code block. The syntax for declaring a scalar constant is similar to that of a scalar variable, with only difference being the addition of the keyword CONSTANT between the constant's name^[3] and its data type:

Once declared, however, constants do not allow further assignments; any attempt to assign a new value will result in an error^[4]. For example:

In the code block, c_text is declared as a constant with the data type of VARCHAR2(15). Then, in the subsequent executable section, we're trying to assign a new value 'Hello, PL/SQL!', to the constant. However, because assigning new values to the declared constant is not allowed, Oracle raised an error saying, "PLS-00363: expression 'C_TEXT' cannot be used as an assignment target."

Using %TYPE Attributes

PL/SQL scalar variables are commonly used to interact with values stored in database tables, making it often essential for these variables to maintain compatibility with table columns. For the purpose, you can take advantage of the %TYPE attribute, which allows you to declare a variable with the same data type as another PL/SQL variable or a specific database table column. General syntax of the %TYPE attribute is as follows:

variable_name schema.table.column%TYPE;
variable_name another_variable%TYPE;

For example, in the code block shown below, four PL/SQL variables-v_first_name, v_last_name, v_email, and v_phone_number-are declared, each corresponding to a column in the hr.employees table.

It is worth noting that table columns' NOT NULL constraints are not enforced when assigning values to PL/SQL variables. For example, in the hr.employees table, the last_name column has a NOT NULL constraint. However, even if you declare a scalar variable referencing it, you can still assign a null value within the code block. This is because PL/SQL variables operate independently for their own data processing. Of course, however, you cannot insert the null value back to the column, as they have NOT NULL constraints on the database table, or you cannot assign a null value if the PL/SQL variable is declared with the NOT NULL keyword.

The usage of %TYPE attribute is not limited to referencing a table column; it can also be used to reference the data type of another PL/SQL variable. For example:

In this example, v_last_name is declared with the data type of v_first_name using the %TYPE attribute. This approach provides flexibility in code maintenance and adaptability, as any changes to the data type of v_first_name will also be reflected in v_last_name after compilation.

Composite Types

In PL/SQL, composite data types are used to store multiple values as a single unit. Each element within the composite can be accessed and manipulated individually. PL/SQL composite types include records, collections, and objects.

• Records: Data type that can hold multiple fields of different data types. Each field can be accessed using a dot notation. You define a record type and then declare variables of that type.
• Collections: Data structures that can hold multiple values, allowing you wo work with sets of data. There are three subtypes of collections in PL/SQL:
• Index-by Tables (Associative Arrays): Key-value pairs where the key can be a number or string.
• Nested Tables: An unordered set of elements that can be accessed using an index.
• VARRAYS (Variable-size Arrays): Ordered collections with a fixed maximum size.
• Objects: A PL/SQL object refers to user-defined data types that encapsulate both data and methods. You can define an object type and create instances of that type, which allows for more complex data manipulation and organization.

Records

A PL/SQL record is a combination of scalar values that we discussed earlier. This data type is particularly useful for storing a single row retrieved from a database table. The general syntax of declaring a record is:

For example, in the code block below, a record data type named dept_rect is declared. At line 13, an instance of dept_rect is created. In the subsequent executable section, the SELECT INTO statement retrieves a row where department_id equals 100 and stores it in vr_dept. Observe that this line captures all four column values from the table into vr_dept simultaneously.

In this example, the data type of each field of vr_dept is declared manually when defining the record data type. However, you can also use the %ROWTYPE attribute. The %ROWTYPE attribute allows you to reference the data types of all columns in an existing row, without listing all the columns. For example:

Each field in a record can be referenced using dot notation. Then the field values can be manipulated using PL/SQL's statements and control structures.

Collections

In computer programming, a collection generally refers to an ordered group of elements each of which has the same data type and can be addressed by a unique subscript. PL/SQL offers three different types of collections:

• Index-By Tables: Also known as associative arrays, allow you to access elements using arbitrary numbers and strings as index values. Index-by tables are particularly useful for fast lookups and dynamic data retrieval. If you're familiar with some other programming languages, you can think of an index-by table as hash tables.
• Varrays: Short for variable-size arrays, hold a fixed number of elements and use a sequence of numbers as subscripts. Varrays can be defined as collections of SQL-compatible data types, allowing them to be stored in database tables. 
• Nested Tables: Hold an arbitrary number of elements, using a sequence of numbers as subscripts. Similar to varrays, nested tables can be defined as collections of SQL-compatible data types and stored in database tables. However, they offer greater flexibility compared to varrays.

Index-By Tables

To define a PL/SQL type for index-by tables, use the syntax:

TYPE type_name IS TABLE OF type_of_value INDEX BY type_of_key;

Where:

• type_name: Type specifier used later to declare an instance of collection.
• type_of_value: The data type of the values stored in the table (e.g., NUMBER, VARCHAR2).
• type_of_key: The data type of the index (subscript). Keys can be of type PLS_INTEGER or scalar strings, such as VARCHAR2. 

Once declared, you can create an instance of the index-by table type and store key-value pairs within that instance. The keys must be of the data type as the index-by table type definition and must be unique; each key must be uniquely identified, whereas its associative values can be duplicated. For example:

At code lines 2-3, an index-by table type called av_employees is defined. Subsequently, an instance of av_employees is created and named as vav_emp. By the definition of av_employees, vav_emp can store a pair of unique PLS_INTEGER and VARCHAR2(50) values. 

The size of an index-by table is not fixed; it can grow or shrink dynamically. In the example with vav_emp, we did not specify its exact size when declaring it. Instead, during the execution of the statements, the size of vav_emp increased.

Index-By Table Methods

Index-by tables come with several built-in methods that facilitate the management of key-value pairs. These methods can be utilized, using dot notation with an instance of the index-by table.

• COUNT: Returns the number of elements.
• num_elements := my_table.COUNT;
• EXISTS: Checks if a specified key exists.
• IF my_table.EXISTS(key) THEN ... END IF;
• DELETE: Removes elements from the collection based on the specified key. If no key is provided, removes all elements. 
• Deletes all elements: my_table.DELETE;
• Deletes an element with the key 'A': my_table.DELETE('A');
• Deletes elements with the keys 'A' and 'B': my_table.DELETE('A', 'B');
• FIRST: Returns the first key.
• first_key := my_table.FIRST;
• LAST: Returns the last key.
• last_key := my_table.LAST;
• PRIOR: Returns the previous of the specified key.
• prior_key := my_table.PRIOR(key);
• NEXT: Returns the next of the specified key.
• next_key := my_table.NEXT(key);

Varrays

To declare a varray data type, use the following syntax:

TYPE type_name IS VARRAY(num_elements) OF type_of_elements;

Where:

• type_name: Name you assign to the varray type, used later to declare an instance of the varray.
• num_elements: Maximum number of elements the varray can hold. This should be a positive integer.
• element_data_type: Data type of elements stored in the varray (e.g., NUMBER, VARCHAR2, etc.).

For example, in the code block shown below, a varray type is defined as va_type with a maximum size of 5 and element type of VARCHAR2(20). Any instances constructed by va_type can thus store up to 5 VARCHAR2(20) strings.

Unlike index-by tables, the indices of the varrays are automatically assigned as consecutive integers starting from 1 up to the specified maximum size. This makes varrays particularly suitable for storing sequential values that need to be accessed in a specific order.

Varray Methods

Oracle offers some built-in methods for varrays to help manage and manipulate the elements efficiently. Here are some commonly used varray methods:

• EXTEND: Increases the size of the varray by a specified number of elements. 
• Append one null element at the end: my_varray.EXTEND;
• Append n null elements at the end: my_varray.EXTEND(n);
• Append ith element n times: my_varray.EXTEND(n, i);
• TRIM: Removes the last element from the varray. 
• my_varray.TRIM;
• DELETE: Removes all elements from the varray. Unlike index-by tables, indices of varrays must be consecutive numbers. Thus, you cannot delete specific elements by index.
• my_varray.DELETE;
• FIRST: Returns the first index of the varray.
• first_index := my_varray.FIRST;
• LAST: Returns the last index of the varray.
• last_index := my_varray.LAST;
• LIMIT: Returns the maximum number of elements of the varray.
• max_num_elements := my_varray.LIMIT;

Nested Tables

Syntax for declaring a nested table type is as follows:

TYPE type_name IS TABLE OF type_of_elements;

Where:

• type_name: The name you assign to the nested table type, which will be used to declare instances of the nested table.
• type_of_elements: The data type of the elements stored in the nested table (e.g., NUMBER, VARCHAR2, etc.).

For example:

Notice that nested table function similarly to varrays. However, unlike varrays, a nested table does not have a defined maximum size and can dynamically grow as needed during execution. 

Nested Table Methods

Nested table instances come with several built-in methods to facilitate the management and manipulation of their elements. Here are key methods associated with nested tables:

• EXTEND: Increases the size of the nested table by a specified number of elements. 
• Append one null element at the end: my_nested_table.EXTEND;
• Append n null elements at the end: my_nested_table.EXTEND(n);
• Append ith element n times: my_nested_table.EXTEND(n, i);
• TRIM: Removes the last element from the nested table. 
• my_nested_table.TRIM;
• DELETE: Removes all elements from the nested table. Unlike index-by tables, indices of nested tables must be consecutive numbers. Thus, you cannot delete specific elements by index.
• my_nested_table.DELETE;
• FIRST: Returns the first index of the nested table.
• first_index := my_nested_table.FIRST;
• LAST: Returns the last index of the nested table.
• last_index := my_nested_table.LAST;

LOB Types

In PL/SQL, LOB (Large OBject) types are designed to store and manipulate large amounts of unstructured data, such as text, images, or any other binary data. PL/SQL supports four primary LOB types: 

• CLOB (Character Large Object): Used to store large amounts of character data. CLOBs can hold up to 4GB of character data in a single column. This is ideal for storing large text documents, such as XML files, HTML document, or any text exceeding the limits of VARCHAR2.
• NCLOB (National Character Large Object): Similar to CLOB but designed to store large amounts of national character set data (Unicode). NCLOBs also have a maximum size of 4GB. It is useful for storing large texts in various languages or character sets, where Unicode support is necessary.
• BLOB (Binary Large Object): Used to store large amounts of binary data, such as images, audio files, or any other binary format. BLOBs can hold up to 4GB of binary data. BLOB is ideal for applications require storage of multimedia files or any data that is not character-based.
• BFILE: A special type of LOB that allows you to store a pointer to an external binary file located outside the database. BFILEs do not store the data within the database; instead, they reference it on the file system. BFILEs are useful for accessing large files stored on the server file system without the need to lad them into the database.

Reference Types

In PL/SQL, reference types are used to refer to objects or cursor variables. Here's a breakdown of the PL/SQL's reference types.

• REF CURSOR: This is a reference type that points to a cursor variable, which can hold the result set of a SQL query. It's essentially a placeholder for a query result.
• REF Objects: This is a reference type that points to an object instance. Objects in PL/SQL are structured data types that encapsulate data and behavior.

Note that while PL/SQL does use reference types, they don't directly equate to pointers in other languages. PL/SQL's reference types are more like handles or references to objects or cursor variables; they don't directly point to the memory address of the data, as pointers do in languages like C or C++.

---

^[1] In versions 8i and 9i, PLS_INTEGER demonstrated noticeably faster performance compared to BINARY_INTEGER. From Oracle 10g onward, PLS_INTEGER and BINARY_INTEGER are functionally identical and can be used interchangeably. ^↩ 

^[2] The total length of the variable name, including the prefix, must not exceed 30 characters. The first character must be a letter. After that it can include numbers, underscore (_), and/or dollar signs ($). Conventionally, scalar variable names start with the prefix v_, followed by a descriptive name in snake_case. ^↩ 

^[3] The same naming rule applies. Additionally, a scalar constant follows the naming convention of using the prefix c_, followed by a descriptive name in snake_case. ^↩ 

^[4] For the same reason, constants must be initialized at the time of declaration. You cannot initialize it in the executable section. ^↩