Every PL/SQL block you write defines and manipulates program data. Program data consists of data structures that exist only during the PL/SQL session (physically stored in the session's Program Global Area or PGA). Unlike database tables, program data is not persisted to disk. Program data encompasses:

• Variables or constants: Variables can change during execution, while constants remain fixed once declared.
• Scalar or composite: Scalar types hold single values like numbers or strings. Composite types contain multiple values through records, collections, or object instances.
• Containerized data: Containers may hold information retrieved from the database or data that never existed and will never exist in the database.

Before using program data in PL/SQL code, you must declare the data structure and assign it a name and datatype.

Naming Program Data

Variables and constants must be declared with a name before use. Naming rules:

• Names must begin with a letter
• Maximum length is 30 characters
• Names are case-insensitive (unless enclosed in double quotes, which is not recommended)

Key recommendations for naming:

• Ensure each name clearly reflects its purpose and usage
• Establish consistent naming conventions

Conventions often use prefixes and suffixes to indicate type and scope. For instance, local variables might use the "l_" prefix, while package-level globals use "g_". Record types carry a "_rt" suffix. A comprehensive set of naming conventions is available in downloadable resources.

PL/SQL Datatype Overview

When declaring variables or constants, you must assign a datatype. PL/SQL is a statically typed language, meaning type checking occurs at compile time rather than runtime (unlike dynamic languages such as Perl, JavaScript, or Ruby). Static typing enables compile-time error detection, improving reliability and execution speed. Dynamic typing offers easier metaclass and introspection implementation.

PL/SQL provides a comprehensive set of predefined scalar and composite datatypes through the STANDARD package. You can also create user-defined types (abstract datatypes). Some types like Boolean and NATURAL cannot be used to define database columns.

Character Data

PL/SQL supports fixed-length strings (CHAR and NCHAR) and variable-length strings (VARCHAR2 and NVARCHAR2). Character large objects are represented by CLOB and NCLOB. The LONG datatype exists only for backward compatibility and should not be used.

Numbers

The NUMBER datatype handles decimal fixed-point, floating-point values, and integers. Binary floating-point types include BINARY_FLOAT and BINARY_DOUBLE. These use binary precision rather than decimal, potentially causing rounding issues, so they are unsuitable for monetary calculations.

SIMPLE_FLOAT and SIMPLE_DOUBLE behave similarly to their binary counterparts but disallow NULL values and do not raise exceptions on overflow.

PLS_INTEGER is an integer type with hardware-implemented arithmetic. FOR loop counters use PLS_INTEGER. SIMPLE_INTEGER shares the same range as PLS_INTEGER but rejects NULL values and does not throw exceptions on overflow.

Dates, Timestamps, and Intervals

DATE stores both date and time with second precision. INTERVAL and TIMESTAMP handle time-related data with various granularities.

Booleans

BOOLEAN implements three-valued logic with values TRUE, FALSE, or NULL.

Native boolean database columns were not supported until Oracle 23c. Previously, you could simulate boolean behavior with constraints:

flag CHAR(1) CHECK (flag IN ('Y', 'N'))

Binary Data

RAW avoids character set conversions. BLOB stores binary data internally, while BFILE stores it externally. These types handle unstructured data.

ROWID

ROWID and UROWID represent row addresses in tables. ROWID provides the unique physical address of a row; UROWID indicates logical positions in index-organized tables (IOTs). ROWID is also a SQL pseudo-column usable in SQL statements.

REF CURSORs

REF CURSOR declares cursor variables for use with static or dynamic SQL, enabling more flexible programming. Two forms exist: strong REF CURSOR and weak REF CURSOR. Weak REF CURSORs represent one of the few dynamic type structures supported in PL/SQL.

-- Strong REF CURSOR
TYPE book_data_t IS REF CURSOR RETURN book%ROWTYPE;

-- Weak REF CURSOR
TYPE book_data_t IS REF CURSOR;
book_curs_var SYS_REFCURSOR;

Internet Datatypes

XMLType, URIType, and HttpURIType handle XML and URI-related data.

Any Datatypes

Any datatypes enable more generic code by dynamically encapsulating and accessing type descriptions, data instances, and data instance sets for any SQL type. These object types provide methods to determine datatypes stored in nested tables without accessing the table type's actual declaration.

Any datatypes include AnyType, AnyData, and AnyDataSet.

User-Defined Datatypes

Oracle allows creating custom complex types from built-in and user-defined types to model your system's data structure and behavior.

Declaring Program Data

With few exceptions, variables and constants must be declared before use. Declarations appear in the declaration section of PL/SQL blocks and can include variables, constants, types (such as collection or record types), and exceptions.

Declaring a Variable

Variable declaration allocates memory for the value and assigns a storage location name for retrieval and modification. The declaration also specifies the datatype for value validation.

name datatype [NOT NULL] [ := | DEFAULT default_assignment];

Examples of declaration errors:

-- Incorrect: NOT NULL variables and constants require initialization
d1 NUMBER NOT NULL;
d1 CONSTANT NUMBER;


-- Correct
d1 NUMBER NOT NULL DEFAULT 5;

-- Correct: equivalent to the previous declaration
d2 NUMBER NOT NULL := 5;

A recommended approach uses the assignment operator (:=) for constents and DEFAULT for variables to improve code readability:

max_count CONSTANT NUMBER := 100;
current_count NUMBER DEFAULT 0;

Declaring Constants

The CONSTANT keyword precedes the datatype. Once declared, constants cannot be modified.

Unnamed constants are literals, such as 2 or "Bobby McGee". Literals have no identifier but possess an implicit (undeclared) datatype.

The NOT NULL Clause

This example demonstrates the NOT NULL constraint:

DECLARE
    max_value NUMBER NOT NULL := 50;
BEGIN
    max_value := NULL;
END;
/

This block raises an exception when executed because NULL cannot be assigned to a NOT NULL variable.

Anchored Declarations

Explicitly specifying datatypes is called hardcoded typing. Anchored declarations set a variable's datatype based on an existing data structure: another PL/SQL variable, predefined TYPE or SUBTYPE, database table, or specific table column.

Anchoring comes in scalar and record varieties:

-- Hardcoded datatype
a VARCHAR2(100);

-- Scalar anchoring
b a%TYPE;
emp_id employees.employee_id%TYPE;

Anchor references resolve when the code is compiled. Changes to referenced elements invalidate the anchored code.

Anchored declarations demonstrate that PL/SQL extends beyond procedural programming—it is specifically designed as an extension to Oracle's SQL language.

Anchoring to Cursors and Tables

-- Record anchoring
emp_record employees%ROWTYPE;

-- Explicit record anchoring
CURSOR emp_cur IS
    SELECT employee_id, employee_name FROM employees
    WHERE employee_id = 100;
emp_data emp_cur%ROWTYPE;

-- Implicit record anchoring
FOR emp_data IN (
    SELECT employee_id, employee_name FROM employees
    WHERE employee_id = 100
)
LOOP
    -- process emp_data
END LOOP;

Benefits of Anchored Declarations

Anchoring provides:

• Synchronization with database columns (maintaining consistency)
• Normalization of local variable definitions (enabling single-point changes when business requirements evolve)

Anchoring to NOT NULL Datatypes

When anchoring to database columns, NOT NULL constraints are not automatically transferred to the variable. However, NOT NULL variables do propagate their constraint.

Programmer-Defined Subtypes

Subtypes, also called abstract datatypes, come in constrained and unconstrained forms:

-- Constrained subtype
SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;

-- Unconstrained subtype
SUBTYPE FLOAT IS NUMBER;

Unconstrained subtypes function as type aliases. Note that subtypes anchored to database columns do not transfer NOT NULL constraints to variables defined with them, nor do they propagate default values from the original declaration.

Datatype Conversion

Conversion between datatypes can be implicit or explicit.

Implicit Data Conversion

While PL/SQL performs implicit conversions, this approach introduces several concerns:

• Implicit conversion undermines static typing benefits like code clarity and safety
• Each implicit conversion represents a loss of control over program behavior
• Implicit conversions depend on execution context and may not perform expected transformations
• Explicit conversions improve code readability and eliminate potential misunderstandings

Conversion failures may raise exceptions, and not all type pairs support implicit conversion.

Explicit Datatype Conversion

Oracle provides comprehensive conversion functions for SQL and PL/SQL:

Function	Purpose
ASCIISTR	Converts strings from any character set to ASCII in the database character set
CAST	Covnerts between built-in or collection types; a powerful alternative to traditional functions
CHARTOROWID	Converts strings to ROWID format
CONVERT	Converts strings between character sets
FROM_TZ	Adds timezone information to TIMESTAMP values
HEXTORAW	Converts hexadecimal to RAW format
MULTISET	Maps database tables to collections
NUMTODSINTERVAL	Converts numbers to INTERVAL DAY TO SECOND literals
NUMTOYMINTERVAL	Converts numbers to INTERVAL YEAR TO MONTH literals
RAWTOHEX, RAWTONHEX	Converts RAW values to hexadecimal strings
REFTOHEX	Converts REF values to hexadecimal representation
ROWIDTOCHAR, ROWIDTONCHAR	Converts binary ROWID values to strings
TABLE	Maps collections to data base tables (inverse of MULTISET)
THE	Maps single-column values to virtual database tables
TO_BINARY_DOUBLE	Converts numbers or strings to BINARY_DOUBLE
TO_BINARY_FLOAT	Converts numbers or strings to BINARY_FLOAT
TO_BLOB	Converts RAW values to BLOB
TO_CHAR, TO_NCHAR (character)	Converts between database and national character sets
TO_CHAR, TO_NCHAR (date)	Converts dates to strings
TO_CHAR, TO_NCHAR (number)	Converts numbers to strings
TO_CLOB, TO_NCLOB	Converts VARCHAR2, NVARCHAR2, or NCLOB to CLOB/NCLOB
TO_DATE	Converts strings to dates
TO_DSINTERVAL	Converts character strings to INTERVAL DAY TO SECOND
TO_LOB	Converts LONG to LOB
TO_MULTI_BYTE	Converts single-byte characters to multi-byte equivalents
TO_NUMBER	Converts strings or numbers to NUMBER
TO_RAW	Converts BLOB to RAW
TO_SINGLE_BYTE	Converts multi-byte characters to single-byte equivalents
TO_TIMESTAMP	Converts strings to TIMESTAMP values
TO_TIMESTAMP_TZ	Converts strings to TIMESTAMP WITH TIME ZONE
TO_YMINTERVAL	Converts character strings to INTERVAL YEAR TO MONTH
TRANSLATE ... USING	Converts text using specified character sets
UNISTR	Converts strings to database Unicode format

CHARTOROWID Function

Input strings must follow the format OOOOOFFFBBBBBBRRR, where:

• OOOOO is the data object number
• FFF is the relative file number
• BBBBBB is the block number within the file
• RRR is the row number within the block

All four components must use Base-64 encoding. Non-conforming formats raise the VALUE_ERROR exception.

CAST Function

-- CAST in SQL
SELECT employee_id, CAST(hire_date AS VARCHAR2(30))
FROM employees;

-- CAST in PL/SQL
hd_display := CAST(SYSDATE AS VARCHAR2);

CONVERT Function

CONVERT transforms strings between character sets, enabling proper handling of different encoding systems.

HEXTORAW Function

HEXTORAW converts hexadecimal strings from CHAR or VARCHAR2 to RAW type for binary data handling.

RAWTOHEX Function

RAWTOHEX transforms RAW type values into VARCHAR2 hexadecimal strings and always returns variable-length results.

ROWIDTOCHAR Function

ROWIDTOCHAR converts binary ROWID values to VARCHAR2 strings using the format OOOOOFFFBBBBBBRRR. This function is essential for displaying and manipulating row identifiers in human-readable form.