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

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
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

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-2014 Daniel A. Morgan All Rights Reserved