Anonymous blocks in different environments

Anonymous blocks are used in various environments where PL/SQL code is either executed directly or enclosed in some program in that environment (see Table 3-1). The enclosing object provides the context and possibly a means of naming the program.

Table 3-1. Anonymous blocks in different environments
Enclosing object	Environment(s)	Description
Client-side trigger	Oracle development tools	"Bare" executable statements in the Forms Builder or Reports Builder trigger(s), packaged as an anonymous block by the tool and sent to the PL/SQL engine. (See Chapter 18 for a discussion of triggers.)
Database trigger	Table-level data manipulation or database event	Trigger "body." While the trigger has a name, the PL/SQL code itself is unnamed (anonymous).
Script	SQL*Plus or equivalent	Ad hoc programs and batch-processing scripts that call procedures and/or functions. Also, the SQL*Plus EXECUTE command translates its argument into an anonymous block by enclosing it between BEGIN and END statements.
Compiled program	Embedded languages (Pro*C, etc.); OCI (Oracle Call Interface)	Anonymous blocks embedded in execute statements inside the database server.

Whenever you attach PL/SQL code to a trigger or field in a tool like Forms Builder, that code establishes an anonymous PL/SQL block. When you write this code, you can enter a fully specified PL/SQL block (declaration, execution, and exception sections), or you can enter only the execution section.

Named Blocks

While anonymous PL/SQL blocks can be found throughout most Oracle applications, the majority of code you write will be in named blocks. You've seen a few short examples of stored procedures in this book already (as in Figure 3-1), and you probably know that the big difference is in the header. A procedure header looks like this:

PROCEDURE [schema.]name [ ( parameter [, parameter ... ] ) ] [AUTHID {DEFINER | CURRENT_USER}]

Afunction header is similar, but includes the RETURN keyword:

FUNCTION [schema.]name [ ( parameter [, parameter ... ] ) ] RETURN return_datatype [AUTHID {DEFINER | CURRENT_USER}] [DETERMINISTIC] [PARALLEL ENABLE ...] [PIPELINED]

Because Oracle allows you to invoke some functions from within SQL statements, the function header includes more optional components than the procedure header, corresponding to the greater control that Oracle gives you over a function's runtime behavior.

For a more complete discussion of procedures and functions, see Chapter 16.