Understanding the PL/SQL Block – Structure, Types & Examples

A PL/SQL block is the core building unit of Oracle’s Procedural Language extension to SQL. Whether you’re creating a trigger, function, procedure, or a one-time script, every PL/SQL program is structured as a block.

Oracle applications are composed of PL/SQL blocks, which encapsulate logic like data manipulation, conditionals, and exception handling into modular, executable units.

What is a PL/SQL Block?

A PL/SQL block is the fundamental unit of code in Oracle’s PL/SQL language. It groups one or more logical operations (like data manipulation, control logic, error handling, etc.) into a single program structure that Oracle can execute as a whole.

All PL/SQL code—whether part of an anonymous block, stored procedure, function, trigger, or package—starts with a block. Even the most complex Oracle application is built from these simple building blocks.

Structure of a PL/SQL Block

Each PL/SQL block has three optional and one mandatory section:

DECLARE 
   -- Declarations (variables, constants, cursors)
BEGIN 
   -- Executable statements (SQL and PL/SQL)
EXCEPTION 
   -- Error handling logic (optional)
END;
/

Sections Explained:

DECLARE: Used to declare variables, constants, cursors. Optional.
BEGIN: The main logic where SQL statements are executed. Mandatory.
EXCEPTION: Used to handle runtime errors gracefully. Optional.
END: Marks the end of the block.

Example: Basic PL/SQL Block

SET SERVEROUTPUT ON;

DECLARE
   v_name VARCHAR2(50) := 'Oracle Learner';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name);
END;
/

Types of PL/SQL Blocks

Block Type	Description	Stored in DB?	Callable?
Anonymous Block	Ad-hoc code for one-time execution. Not named or stored.	No	No
Named Block	Stored procedures, functions, triggers, or packages with reusable logic.	Yes	Yes

Key Features of PL/SQL Blocks

Modular and structured logic
Supports DML (INSERT, UPDATE, DELETE)
Conditional statements (IF, LOOP, CASE)
Transaction control (COMMIT, ROLLBACK)
Error handling via EXCEPTION block
Can run DDL using dynamic SQL

Using DDL & Dynamic SQL

PL/SQL cannot directly run DDL (like CREATE, ALTER, DROP) using static SQL syntax. Instead, it uses:

Native Dynamic SQL (NDS) via EXECUTE IMMEDIATE
Or the DBMS_SQL package

Example: Executing DDL with PL/SQL

BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE demo (id NUMBER)';
END;
/

When to Use a PL/SQL Block

Running script-based tasks (via anonymous block)
Creating stored logic with procedures and functions
Error handling and rollback control
Triggering automated database operations
Performing batch processing
Executing DDL within PL/SQL using dynamic SQL

Learn More

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.