Oracle Anonymous Blocks, Constants, Variables, and Type Declarations
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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;
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);
END;
/
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 18cR3
What's New In 19cR2

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