Oracle Anonymous Blocks, Constants and Variables
Version 11.2
 
Note: Anonymous blocks are run by copying them to the SQL*Plus command prompt then hitting the <Enter>key

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;
/
 
Demos
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;
/
 
 
Morgan's Library Page Footer
This is site maintained by Dan Morgan. Last Updated: Contact Us Legal Notices & Terms of Use  Privacy Statement