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.