Figure 20-1. Execution of a do-nothing anonymous block

Let's step through the operations shown in this figure.

1. Starting on the left side of Figure 20-1, the user composes the block one line at a time and then gives SQL*Plus the "go-ahead" command (a slash). As the figure shows, SQL*Plus sends the entire code block, exclusive of the slash, to the server. This transmission occurs over whatever connection the session has established (for example, Oracle Net or interprocess communication).

2. Next, the PL/SQL compiler attempts to compile this anonymous block, generating internal data structures such as DIANA to help it analyze the code and generate bytecode.^[1] A first phase is to check the syntax to ensure that the program adheres to the grammar of the language. In this simple case, there are no identifiers to figure out, only language keywords. If compilation succeeds, Oracle puts the block's bytecode into a shared memory area; if it fails, the compiler will return error messages to the SQL*Plus session.

^[1] Actually, if some session previously needed Oracle to compile the block, there is a good chance that the compile phase won't need to be repeated. This is because the server caches in memory and tries to share the output of relatively expensive operations like compilation.

3. Finally, the PL/SQL runtime engine interprets the bytecode and ultimately returns a success or failure code to the SQL*Plus session.

Let's add an embedded SQL query statement into the anonymous block and see how that changes the picture. Figure 20-2 introduces some of the SQL-related elements of the Oracle server.

Figure 20-2. Execution of an anonymous block that contains sql

This example fetches a column value from a well-known built-in table named DUAL.^[2] The simple change introduces a surprisingly large number of new concepts.

^[2] According to Steve Adams' web site, the name DUAL is from its dual singularity: one row, one column.

After checking that the PL/SQL portions of the code adhere to the language's syntax, the PL/SQL compiler hands off the SQL statement to the SQL parser (parsing is the first of several compiler phases). The SQL parser first checks the SQL statement's syntax, and then it performs name resolution . This phase determines what each particular (non-keyword) identifier represents. In this example, the PL/SQL compiler has sent the SQL statement over to the SQL compiler with the identifiers DUAL, dummy, and str. The SQL parser will figure out that DUAL is a table and dummy is a column in that table, but it will call back to PL/SQL to resolve str. The SQL parser also checks permissions to make sure that the originating session has the authority to perform the requested SQL operation.

In Oracle9i,PL/SQL and SQL share a common SQL parser. In earlier versions, PL/SQL had its own SQL parser, which occasionally led to some discrepancies between the SQL at the command prompt versus the SQL that executed from within a PL/SQL program. For example, in Oracle 8.1.7, the SQL compiler in PL/SQL didn't know about the new NVL2 function. The statement: SELECT NVL2(NULL, 1, 2) FROM DUAL; worked fine from the command line, but inside PL/SQL it resulted in the error message PLS-00201: identifier `NVL2' must be declared.

It's important to remember that the executable (bytecode) form of the PL/SQL contains not just a predigested binary form of the program's logic, but also a textual form of any embedded static SQL statements.Typically, PL/SQL modifies the SQL slightly by removing INTO clauses, substituting bind variables for local program variables, and ensuring that the first keyword of the SQL statement—SELECT, UPDATE, or whatever—is uppercase. So, for example, if myvar is a local program variable, PL/SQL will change this:

select dummy into str from dual where x = myvar

into something like this:

SELECT dummy from dual where x = :b1

Getting back to the example, if the anonymous block passes all the compiler checks, Oracle "graduates" it from the compilation phase to the runtime (execution) phase. In this phase, the SQL compiler performs all of its steps rather than simply parsing. It does repeat the syntax, name resolution, semantic, and permission checks, but goes on to compute what it thinks will be an efficient execution plan—in our example, one that's not too complicated. The SQL compiler hands the plan off to the SQL execution engine, which fetches the row and passes the results back to PL/SQL.

What if an anonymous block invokes astored program? Figure 20-3 illustrates this case. To keep the picture as simple as possible, we'll go back to a no-SQL example.