10.1 How do I…Create anonymous PL/SQL blocks?

Problem

I want to perform tasks not well-suited for SQL on the database, such as performing conditional logic and working with the procedural components available in PL/SQL. Because the PL/SQL block is the smallest logical unit of PL/SQL code, I want to start by creating a PL/SQL block to perform a simple action. How do I create anonymous PL/SQL blocks in Oracle?

Technique

PL/SQL is a block structured language. PL/SQL blocks are implemented using anonymous blocks, procedures, and functions. Unlike functions and procedures, no name is associated with an anonymous PL/SQL block and, hence, cannot be referenced from other PL/SQL blocks. Anonymous blocks are standalone; they neither take input parameters nor return values and can be nested within another PL/SQL block. The anonymous PL/SQL block is made up of three sections, of which two are optional (indicated by square brackets):

DECLARE

Declarative Section]

BEGIN

Executable Section

[EXCEPTION

Exception handling Section]

END;

The declarative section, started with the DECLARE statement, is where objects are declared. User-defined variables, datatypes, cursors, and PL/SQL subprograms local to the block are declared in this section. The objects within the declarative section are limited to the block and cannot be used outside it. The executable section starts with the BEGIN keyword and contains procedural and SQL statements that are executed when the block is run. Next, the exception section starts with the EXCEPTION keyword and contains exception handlers to handle errors that occur during processing. The error handling code in the exception section is executed only when an error occurs. A PL/SQL block is terminated with the END statement and a semicolon.

Steps

1. Run SQL*Plus and connect to the database as the WAITE user account. Run the CHP10_1.SQL file at the prompt. It has the simplest form of the anonymous block, one that does not have the optional declaration and exception sections. The PL/SQL block contained in the file calls the DBMS_OUTPUT.PUT_LINE to display a message in SQL*Plus. Figure 10.1 shows the code and the output of the anonymous PL/SQL block in SQL*Plus.

For the output of the DBMS_OUTPUT package to be displayed in SQL*Plus, the SERVEROUTPUT system variable must be set to ON. The anonymous block calls the PUT_LINE procedure in the DBMS_OUTPUT built-in package to display a text message in SQL*Plus. The PUT_LINE procedure is passed a string to be displayed within SQL*Plus and can accept DATE and NUMBER variables as well. To display data of other datatypes, a conversion function such as TO_CHAR must be used. Table 10.1 shows procedures in the DBMS_OUTPUT package.

Table 10.1 Procedures in the DBMS_OUTPUT package

Procedure Description
ENABLE Enables message output (or SERVEROUTPUT ON)
DISABLE Disables message output (or SERVEROUTPUT OFF)
PUT_LINE Puts the output on a new line
PUT Puts the output on the same line
NEW_LINE Signals the beginning of a new line
GET_LINE Retrieves a line from the buffer
GET_LINES Retrieves an array of lines from the buffer

Note that line 4 in the file has a / signaling SQL*Plus to run the anonymous block after it has loaded the block into the SQL*Plus buffer. The DBMS_OUTPUT.PUT_LINE procedure is important because it is often used to debug PL/SQL code.

To debug a PL/SQL block with compilation errors, issue a SHOW ERRORS command at the SQL*Plus prompt; you can also include the SHOW ERRORS command at the end of the block in the line following the /, as shown in the preceding example. This makes SQL*Plus display any errors generated by the last module compiled in the current session. A blank line is left as the line following the /, to avoid a benign SQL*Plus error (Input truncated to n characters).

2. Run CHP10_2.SQL, which has a PL/SQL block that declares and uses a numeric variable. Figure 10.2 shows the code and output.

Lines 1 and 2 contain the declarative section of the block, which is started with the DECLARE keyword and continues until the BEGIN keyword. When declaring variables in PL/SQL, the datatype is specified after the variable name. Lines 4 and 5 contain the code segment executed when the block is run. The statement on line 4 sets the value of the variable ORDER_NO to the number 3000. The colon-equals operator (:=) is used to set a variable to a value in PL/SQL.

Table 10.2 contains the operators used in PL/SQL, listed in the order of descending precedence; operators with higher precedence are evaluated first. Whenever the precedence of operators is in doubt or you need to override the default precedence, use the ( and ) expression delimiters around the expression that needs to be evaluated first.

Exceptions are handled in the exception section starting on line 6. The WHEN OTHERS exception handler in line 7 handles any exceptions not dealt with by other exception handlers and must be the last exception handler. The NULL statement in line 8 is a “do nothing” construct, and here it specifies that no action is taken for exceptions.

How-To’s 10.11 and 10.12 cover the process of handling exceptions in detail. Both the declarative section and the exception section are optional. If you don’t need any local variables for your block or you don’t want to handle exceptions that occur, these sections can be removed.

Table 10.2 PL/SQL operators

Operator Description
** Exponentiation
NOT† Logical negation
+ Unary identity
- Unary negation
* Multiplication
/ Division
+ Addition
- Subtraction
|| String concatenation
= Equality
!= Not equals
<> Not equals
~= Not equals
^= Not equals
< Less than
> Greater than
<= Less than or equal
>= Greater than or equal
IS NULL Comparison for null
LIKE Comparison for match
|| String concatenation
= Equality
!= Not equals
<> Not equals
~= Not equals
^= Not equals
< Less than
> Greater than
<= Less than or equal
>= Greater than or equal
IS NULL Comparison for null
LIKE Comparison for match
|| String concatenation
= Equality
!= Not equals
<> Not equals
~= Not equals
^= Not equals
< Less than
> Greater than
<= Less than or equal
>= Greater than or equal
IS NULL Comparison for null
LIKE Comparison for match
BETWEEN Range of values
IN Set membership
AND Conjunction
OR Inclusion
:= Assignment
=> Association
.. Range operator
† Negation operator NOT can be used as IS NOT NULL, NOT LIKE, NOT BETWEEN, NOT IN.

SQL> START CHP10_3

SQL> SET ECHO ON

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 ORDER_DATE DATE; -- Date variable

3 ORDER_NO NUMBER := 24; -- Initialized numeric variable

4 PI CONSTANT NUMBER := 3.1416; -- Numeric constant

5 /* The declarative section of the outer block ends here. All the

6 variables declared above have scope limited to the outer block */

7 BEGIN

8 ORDER_NO := 25;

9 DECLARE -- Inner block begins here

10 CUST_NAME VARCHAR2(30); -- Character variable with a width of 20

11 BEGIN

12 ORDER_NO := 26; -- Outer block variable used in inner block

13 CUST_NAME := ‘JOE SHMOE’;

14 DBMS_OUTPUT.PUT_LINE(‘ORDER NO = ‘ || ORDER_NO);

15 DBMS_OUTPUT.PUT_LINE(‘CUST_NAME = ‘ || CUST_NAME);

16 DBMS_OUTPUT.PUT_LINE(‘PI = ‘ || PI);

17 EXCEPTION

18 WHEN OTHERS THEN

19 DBMS_OUTPUT.PUT_LINE(‘Inner block error.’);

20 END; -- Inner block ends here

21 EXCEPTION

22 WHEN OTHERS THEN

23 DBMS_OUTPUT.PUT_LINE(‘Outer block error.’);

24 END;

25 /

DATE Date Date values with a format as in the database
BOOLEAN Boolean TRUE or FALSE
NUMBER Numeric Integer and real values that can be defined with a precision and scale
BINARY_INTEGER Numeric Signed integers, less storage than NUMBER
POSITIVE Numeric Subtype of BINARY_INTEGER, positive integer values
POSITIVEN Numeric Subtype of BINARY_INTEGER, positive integer values, null values not allowed
NATURAL Numeric Subtype of BINARY_INTEGER, non-negative integer values
NATURALN Numeric Subtype of BINARY_INTEGER, non-negative integer values, null values not allowed
SIGNTYPE Numeric -1, 0, or 1 values only
DECIMAL Numeric Subtype of NUMBER, decimal
DEC Numeric Same as DECIMAL
DOUBLE PRECISION    Subtype of NUMBER, real numbers with high precision
INTEGER Numeric Subtype of NUMBER, integer values only
INT Numeric Same as INTEGER
NUMERIC Numeric Same as NUMBER
REAL Numeric Same as NUMBER
SMALLINT Numeric Subtype of NUMBER, small range of integer
FLOAT Numeric Subtype of NUMBER, requires a binary precision but no scale
PLS_INTEGER Numeric Signed integers, faster than NUMBER and BINARY_INTEGER
VARCHAR2 Character Variable-length character strings
CHAR Character Fixed-length character strings
CHARACTER Character Same as CHAR
LONG Character Variable-length byte strings
RAW Character Binary data or byte strings, no character set conversions
LONG RAW Character Binary data or byte strings
ROWID Character Consists of data object number, data file, data block, and row in the data block
NCHAR Character Fixed-length NLS character strings
NVARCHAR2 Character Variable-length NLS character strings

How It Works

Step 1 presents a simple PL/SQL block showing the basic structure of a block. In Step 2, the block contains a declarative section, an executable section, and an exception section. Both the declarative and exception sections are optional. Step 3 demonstrates how blocks can be nested in PL/SQL, and nesting is important when you need exceptions handled separately within each block. Both the outermost and innermost blocks can contain all three block sections. Step 3 also declares variables of a variety of scalar datatypes in a PL/SQL block.

Comments

The PL/SQL block is the fundamental structure in PL/SQL. Anonymous PL/SQL blocks provide control over scope and visibility of identifiers and exception handling. Anonymous blocks are not stored in the database, and typically they are constructed dynamically to be executed only once. You can nest anonymous blocks in the execution or exception section of a PL/SQL block but not in the declarative section. To declare a PL/SQL block in the declarative section, the block has to be a local subprogram—a function or procedure local to the block, which is addressed in How-To 10.4.