Oracle - How to execute an Anonymous Code Block

About

In Oracle, an anonymous code block can be created with the DECLARE/BEGIN/END block.

Steps

Start Oracle

The next steps will be using the howto connection.

You need to start the sql server docker image.

tabul service start oracle

1 service was started
Name     Type
------   ------
oracle   docker

Ping your connection to check that the database is up.

tabul connection ping oracle

The connection (oracle) has been pinged successfully

The Anonymous Block

This anonymous block is idempotent, it will create the role webuser if it does not exist

The anonymous code block is located at oracle/anonymous_block.sql in the howto connection

With the data print command, we can see the content.

tabul data cat oracle/anonymous_block.sql@howto

DECLARE
  role_count NUMBER;
BEGIN
  -- Check if role already exists
  SELECT COUNT(*)
  INTO role_count
  FROM dba_roles
  WHERE role = 'WEBUSER';

  IF role_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE ROLE webuser';
    DBMS_OUTPUT.PUT_LINE('Role webuser created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Role webuser already exists.');
  END IF;

END;
/

Execution

The execution is performed via a SQL Request

tabul data execute '(oracle/anonymous_block.sql@howto)@oracle'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

List of runtime executed
runtime_data_uri                            exit_code   count   latency   data_uri                                                                   error_message
-----------------------------------------   ---------   -----   -------   ------------------------------------------------------------------------   -------------
(oracle/anonymous_block.sql@howto)@oracle           0       1   0.48s     execute/20251110-204127-357-pipe-tabul-data-exec/anonymous_block.log@tmp

Validation

Anonymous block result returns only an execution status. To verify the execution result, you need:

to check visually with a SQL Select

select role
from dba_roles
where role = 'WEBUSER'

tabul data print '(oracle/anonymous_block_actual_roles.sql@howto)@oracle'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

(oracle/anonymous_block_actual_roles.sql@howto)@oracle
ROLE
-------
WEBUSER

or to perform a diff.

tabul data diff \
  oracle/anonymous_block_expected_roles.csv@howto \
  '(oracle/anonymous_block_actual_roles.sql@howto)@oracle'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

Diff Summary Report
The data resources are equals.
from                                              to                                                       equals   record_count   change_count
-----------------------------------------------   ------------------------------------------------------   ------   ------------   ------------
oracle/anonymous_block_expected_roles.csv@howto   (oracle/anonymous_block_actual_roles.sql@howto)@oracle   ✓                   1              0