Oracle Anonymous Blocks, Constants, Variables, and Type Declarations
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Note: Anonymous blocks are run by copying them to the SQL*Plus command prompt then hitting the <Enter> key
 
Anonymous Blocks
Simplest Anonymous Block BEGIN
  <valid statement>;
END <block_name>;
/
BEGIN
  NULL;
END;
/

BEGIN
  NULL;
END test_block;
/
Anonymous Block With Error Exception Handler BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;

END;
/
Nested Anonymous Blocks With Exception Handlers BEGIN
  <valid statement>;
  BEGIN
    <valid statement>;
  EXCEPTION
    <exception handler>;
  END;
EXCEPTION
  <exception handler>;
END;
/
BEGIN
  NULL;
  BEGIN
    NULL;
  EXCEPTION
    WHEN OTHERS THEN
     NULL;
  END;

  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
Nested Anonymous Blocks With Variable Declaration And Exception Handler That Does Real Work DECLARE
 <variable name> <data type><(length precision)>;
BEGIN
  <valid statement>;
  BEGIN
    <valid statement>;
  EXCEPTION
    <exception handler>;
  END;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 x NUMBER(4);
BEGIN
  x := 1000;

  BEGIN
    x := x + 100;
  EXCEPTION
    WHEN OTHERS THEN
      x := x + 2;
  END;

  x := x + 10;
  dbms_output.put_line(x);
EXCEPTION
  WHEN OTHERS THEN
    x := x + 3;

END;
/
 
Constants & Variables
Constants DECLARE
 <constant name> CONSTANT <data type> := <value>;
 <constant name> CONSTANT <data type> DEFAULT <value>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 counter CONSTANT NUMBER(3) := 2;
 pi      CONSTANT NUMBER(8,7) DEFAULT 3.1415926;
 today   CONSTANT VARCHAR2(30) := ' today is ' || SYSDATE;
BEGIN
  dbms_output.put_line('Counter: ' || counter);
  dbms_output.put_line('Pi:      ' || pi);
  dbms_output.put_line('Today:  ' || today);
END;
/
Variables DECLARE
 <variable name> <data type>;
 <variable name> CONSTANT <data type> := <value>;
 <variable name> CONSTANT <data type> NOT NULL := <value>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 counter NUMBER(10,8) := 0.11235813;
 pi      NUMBER(8,7) := 3.1415926;
 test    NUMBER(10,8) NOT NULL := 10;
 maxlen  DBMS_ID := 'A maximum length string for the database in use';
BEGIN
  counter := pi/counter;
  pi := pi/3;
  dbms_output.put_line('Counter/Pi: ' || counter);
  dbms_output.put_line('Pi:         ' || pi);
  dbms_output.put_line('Test:       ' || test);
  dbms_output.put_line('MaxLen:     ' || maxlen);
END;
/
Counter/Pi: 27.96052764
Pi:         1.0471975
Test:       10
MaxLen:     A maximum length string for the database in use

PL/SQL procedure successfully completed.
Declaring Constants and Variables in nested blocks and nested block naming DECLARE
 <variable name> <data type>;
 <variable name> CONSTANT <data type> := <value>;
 <variable name> CONSTANT <data type> NOT NULL := <value>;
BEGIN
  <valid statement>;
  DECLARE
   <variable name> <data type>;
   <variable name> CONSTANT <data type> := <value>;
   <variable name> CONSTANT <data type> NOT NULL := <value>;
  BEGIN
    <valid_statement>;
  EXCEPTION
    <exception handler>
  END;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i VARCHAR2(5) := 'ABC';

  BEGIN
   
dbms_output.put_line(nb.i);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;

  dbms_output.put_line(i);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;
/

-- potential scoping error
DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i VARCHAR2 := 'ABC';
  BEGIN
 
  dbms_output.put_line(i);
 
  dbms_output.put_line(nb.i);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;
/

-- scoping error
DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i PLS_INTEGER := 2;
  BEGIN
    NULL;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;

  dbms_output.put_line(i);
  dbms_output.put_line(nb.i);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;

/
Declaring Subtypes DECLARE
 SUBTYPE <variable name> IS <data_type>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 SUBTYPE ssn_t IS VARCHAR2(11);
 vSSN    ssn_t;
BEGIN
  vSSN := '555-11-2367';
  dbms_output.put_line(vSSN);
END;
/
 
Demo
Constants and Literals set timing on

DECLARE
 x CONSTANT NUMBER := 1;
 y NUMBER;
BEGIN
  FOR i IN 1..1000000 LOOP
    SELECT COUNT(*)
    INTO y
    FROM servers
    WHERE rownum = x;
  END LOOP;
END;
/

DECLARE
 x CONSTANT NUMBER := 1;
 y NUMBER;
BEGIN
  FOR i IN 1..1000000 LOOP
    SELECT COUNT(*)
    INTO y
    FROM servers
    WHERE rownum = 1;
  END LOOP;
END;
/

Related Topics
DDL Triggers
Functions
Instead-Of Triggers
Operators
Packages
Pipelined Table Functions
Procedures
System Event Triggers
Table Triggers
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx