Blocks are typically written as part of a procedure, function, subprogram, or trigger — named programs that are stored in the database for reuse. An anonymous block is a PL/SQL block that runs once and is not stored in the database. Use it for quick, one-time tasks such as testing logic or running a script without creating a permanent database object.
Block structure
An anonymous block follows the standard PL/SQL block structure:
[ DECLARE
<declarations> ]
BEGIN
<statements>
[ EXCEPTION
WHEN <exception_condition> THEN
<statements> ]
END;| Section | Required | Description |
|---|---|---|
DECLARE | No | Declare variables, cursors, and types used in the block |
BEGIN | Yes | The main logic of the block |
EXCEPTION | No | Error handlers for exceptions raised in the BEGIN section |
END | Yes | Closes the block |
How it works
After execution, the block is erased from the application buffer. It cannot be re-executed unless the code is re-entered. This is what makes it "anonymous" — even if the block has a label, it is not stored in the database and therefore has no persistent identity.
When to use anonymous blocks
Use an anonymous block when:
Testing logic — Validate a query, computation, or data transformation before building a stored program.
Running a one-off script — Execute a maintenance task or data fix that does not need to be repeatable.
Prototyping — Quickly iterate on PL/SQL logic without the overhead of creating and dropping stored programs.
For logic that must run repeatedly, convert the anonymous block into a procedure or function. See What's next.
Example
The following anonymous block queries a table and raises an informational message for each row. Replace <your_table> and <your_column> with actual values from your schema.
DECLARE
v_name VARCHAR2(100);
BEGIN
SELECT <your_column>
INTO v_name
FROM <your_table>
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('First entry: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows found.');
END;Convert to a stored program
To run the same logic multiple times without re-entering the code, wrap the block in a procedure or function. The conversion requires only minor modifications — primarily adding a name and the CREATE OR REPLACE statement.
Named programs (procedures, functions, and triggers) are stored in the database and can be invoked repeatedly by other procedures, functions, or application programs.
What's next
Procedures overview — Create a named, reusable procedure from your block logic.
Functions overview — Wrap logic that returns a value into a stored function.