CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Anonymous Blocks
Version 11.1
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;
/
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 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(10,8) := 2;
 pi      CONSTANT NUMBER(8,7) DEFAULT 3.1415926;
BEGIN
  dbms_output.put_line(counter);
  dbms_output.put_line(pi);
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) := 2;
 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);
  dbms_output.put_line(pi);
END;
/
 
Other Related Topics
DDL Triggers
Functions
Instead-Of Triggers
Operators
Packages
Pipelined Table Functions
Procedures
System Event Triggers
Table Triggers
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [50 users online]    © 2010 psoug.org