Named Blocks in PL/SQL – Complete Guide with Examples

Named Blocks in PL/SQL are stored database programs that encapsulate logic for reuse. This includes procedures and functions, both of which play a crucial role in building modular, high-performance Oracle applications.

What are Named Blocks in PL/SQL?

A named block in PL/SQL is a compiled, stored unit of code that has a unique name and can be called multiple times. Unlike anonymous blocks (used for one-off tasks), named blocks are permanent and reusable database objects.

Syntax of a Named PL/SQL Procedure

Here’s a basic example of a named procedure:

CREATE OR REPLACE PROCEDURE greet_user IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello User!');
END;
/

To execute the named block:

EXEC greet_user;

Key Benefits of Named Blocks

Stored in the database (unlike anonymous blocks)
Reusable across applications
Precompiled — errors/warnings are detected at compile time
Modular and maintainable
Can be called by triggers, other procedures, or from front-end apps

To execute the procedure:

Unlike anonymous blocks, named blocks are compiled and stored. Oracle checks syntax, object references, and grants during compilation.

Example with Error:

CREATE OR REPLACE PROCEDURE test_error IS
  v_time TIMESTAMP;
BEGIN
  SELECT systimestamp INTO v_time FROM duall; -- Typo
  DBMS_OUTPUT.PUT_LINE(v_time);
END;
/

This throws:

ORA-00942: table or view does not exist

Corrected Version:

CREATE OR REPLACE PROCEDURE test_error IS
  v_time TIMESTAMP;
BEGIN
  SELECT systimestamp INTO v_time FROM dual;
  DBMS_OUTPUT.PUT_LINE(v_time);
END;
/

Check errors:

SHOW ERRORS;

PLSQL_WARNINGS for Compile-Time Checks

Oracle lets you enable compile-time warnings using the PLSQL_WARNINGS parameter. This helps catch issues that may not throw errors but affect performance or maintainability.

Example Warning:

CREATE OR REPLACE PROCEDURE warn_test IS
  v_price VARCHAR2(10); -- Will store into a NUMBER column
BEGIN
  INSERT INTO books(price) VALUES (v_price);
END;
/

Warning: PLW-07202: bind type would result in conversion away from column type

Enabling Warnings:

ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
-- Or session-level
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';

To convert a warning into an error:

ALTER PROCEDURE warn_test COMPILE PLSQL_WARNINGS='ERROR:07202';

This forces developers to correct performance issues during development.

Comparison Table: Anonymous vs Named Blocks

Feature	Anonymous Block	Named Block (Procedure / Function)
Definition	A PL/SQL block without a name, typically used for one-time or ad hoc execution.	A PL/SQL block that is given a name and stored in the database for reuse.
Syntax Structure	Contains three sections: Declaration (optional), Execution (mandatory), Exception (optional).	Contains four sections: Header (mandatory), Declaration, Execution, Exception.
Header Section	Not present	Present – defines name, parameters (if any), and return type (for functions).
Storage in Database	Not stored – exists temporarily in memory.	Stored persistently in the database as schema objects.
Invocation Method	Executed manually or from a script.	Can be executed using `EXEC`, called from other programs, or used in triggers and packages.
Reusability	Not reusable – must be rewritten or copied for repeated use.	Reusable – stored and can be executed multiple times.
Compilation	Compiled only at runtime.	Compiled during creation, errors and warnings can be identified before execution.
Dependency Checking	Not checked until runtime.	Dependencies (e.g., tables, views) are checked during compilation.
Error Detection	Errors found only when block is executed.	Errors and warnings are shown during compilation (`SHOW ERRORS`).
Performance Warnings	Not supported.	Supports compile-time warnings using `PLSQL_WARNINGS` and `DBMS_WARNING`.
Visibility in Data Dictionary	Not recorded in the database.	Visible in `USER_OBJECTS`, `USER_SOURCE`, and related views.
Calling by Other Programs	Cannot be directly called by other procedures or triggers.	Can be called by other programs, triggers, jobs, and applications.
Return Values	Uses `OUT` parameters or DBMS_OUTPUT to display results.	Functions must return a value; procedures may or may not return values.
Ideal Use Case	One-time scripts, testing code, quick fixes, or debugging.	Reusable logic, production code, modular programs, and business logic encapsulation.
Example	`plsql<br>BEGIN<br> DBMS_OUTPUT.PUT_LINE('Hello');<br>END;<br>/`	`plsql<br>CREATE OR REPLACE PROCEDURE hello_proc IS<br>BEGIN<br> DBMS_OUTPUT.PUT_LINE('Hello');<br>END;<br>/`

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