Anonymous Blocks: Sample Code

Description: Anonymous blocks are run by copying them to the SQL*Plus command prompt then hitting the key

--Simplest Anonymous Block
 
BEGIN
  <valid statement>;
END;
/
BEGIN
  NULL;
END;
/
 
 
--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
 
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;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.