PL/SQL Anonymous Blocks – Syntax, Examples & Uses

What is an Anonymous Block in PL/SQL?

In PL/SQL (Procedural Language for SQL), an anonymous block is a basic unit of code that is not named, not stored in the database, and is typically used for quick tasks or testing purposes. As the name implies, it does not have an identifier and is usually written ad hoc in environments like Oracle SQL*Plus or SQL Developer.

Key Characteristics of Anonymous Blocks:

  • No Name: It doesn’t have an identifier or label.
  • Temporary: It’s not saved in the database and disappears after execution.
  • Single-Session Use: Cannot be reused unless copied and re-executed.
  • Ideal for Testing: Best used for one-time operations, debugging, or simple scripts.
  • Structure: Starts with DECLARE or BEGIN and ends with END;

Syntax of a PL/SQL Anonymous Block

SET SERVEROUTPUT ON;

DECLARE
   -- Variable declarations (optional)
BEGIN
   -- Executable statements
EXCEPTION
   -- Exception handling (optional)
END;
/

  • DECLARE: Optional section where variables or constants are defined.

  • BEGIN: Starts the main code section.

  • EXCEPTION: Optional section to handle errors.

  • /: Executes the block in SQL*Plus or tools like Oracle SQL Developer.

Example: Calculate Timestamp for One Hour Ago:

SET SERVEROUTPUT ON;

DECLARE
   v_Date TIMESTAMP;
BEGIN
   SELECT systimestamp - INTERVAL '1' HOUR
   INTO v_Date
   FROM dual;

   DBMS_OUTPUT.PUT_LINE('One hour ago: ' || v_Date);
END;
/
Output: 
One hour ago: 12-JUN-25 09.45.22.300000 AM +05:30

When to Use Anonymous Blocks

Use anonymous blocks in situations like:
  • Testing PL/SQL code snippets
  • Creating small scripts for admin tasks
  • Performing quick calculations
  • Initializing variables during testing
  • One-time data manipulations

Anonymous Block vs Named Block in PL/SQL – Detailed Comparison

Aspect Anonymous Block Named Block (Procedure/Function)
Definition Block of PL/SQL code with no name, executed once and not stored in the database. Block of code with a name, stored permanently in the database.
Name No Yes
Storage Not stored in the database Stored as a database object
Reusability Cannot be reused unless saved manually Can be reused by calling its name
Usage Scope Only within the current session Globally accessible within schema
Compilation Compiled at runtime each time Compiled once and stored
Call from Another Block Not callable from other blocks Can be called from other PL/SQL blocks
Parameters Support Does not accept parameters Supports IN, OUT, IN OUT parameters
Best Use Case Testing, scripting, one-time operations Reusable business logic and APIs
Execution Executed directly in session Executed using the procedure/function name
Error Handling Optional EXCEPTION block Optional EXCEPTION block

Study Material by AmantPoint
Only available at amantpoint.com – Learn Oracle the Smart Way

Previous
Next
Facebooktwitterredditpinterestlinkedinmailby feather
Scroll to Top