Oracle Anonymous Blocks, Constants, Variables, and Type Declarations
Version 21c
General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
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;
/
DECLARE
counter NUMBER(10,8) := 0.11235813;
pi NUMBER(8,7) := 3.1415926;
test NUMBER(10,8) NOT NULL := 10;
maxlen DBMS_ID := 'A maximum length
string for the database in use';
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);
dbms_output.put_line('MaxLen: ' || maxlen);
END;
/ Counter/Pi: 27.96052764
Pi: 1.0471975
Test: 10
MaxLen: A maximum length string for the database in use
PL/SQL procedure successfully completed.
Declaring Constants and Variables in nested blocks and nested block naming
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;
/
