Read an Excerpt
From Chapter 4: Oracle8 PL/SQL Programming
...Structured Query Language (SQL) defines how data in Oracle is manipulated. The procedural constructs we examined in Chapters 2 and 3 become significantly more useful when combined with the processing power of SQL, since they allow PL/SQL programs to manipulate data in Oracle. This chapter discusses the SQL statements that are permitted in PL/SQL and the transaction control statements that guarantee consistency of the data. Chapter 5 discusses the built-in SQL functions.
SQL Statements
SQL statements can be divided into six categories, as listed here. Table 4-1 gives some example statements. The Server SQL Reference manual describes all of the SQL statements in detail.
- Data manipulation language (DML) statements change the data in tables, or query data in a database table, but do not change the structure of a table or other object.
- Data definition language (DDL) statements create, drop, or alter the structure of a schema object. Commands that change permissions on schema objects are also DDL.
- Transaction control statements guarantee the consistency of the data by organizing SQL statements into logical transactions, which either succeed or fail as a unit.
- Session control statements change the settings for a single database connection, for example, to enable SQL tracing.
- System control statements change the settings for the entire database, for example, to enable or disable archiving.
- Embedded SQL commands are used in Oracle precompiler programs.
Theonly SQL: statements allowed in a PL/SQL program are DML and transaction control statements. Specifically, DDL statements are illegal. EXPLAIN PLAN, although classified as DML, is also illegal. In order to explain why this is the case, we need to look at the way PL/SQL is designed.
In general, a programming language can bind variables in two ways--early or late. Binding a variable is the process of identifying the storage location associated with an identifier in the program. In PL/SQL, binding also involves checking the database for permission to access the schema object referenced. A language that uses early binding does the bind during the compile phase, while a language that uses late binding postpones the bind until run time. Early binding means that the compile phase will take longer (since the work of binding has to be done), but execution will be faster, since the bind has already been completed. Late binding shortens the compile time but lengthens the execution time.
PL/SQL was intentionally designed to use early binding. This decision was made so that execution of a block would be as fast as possible, because all of the database objects have been verified by the compiler. This makes sense, since PL/SQL blocks can be stored in the database via procedures, functions, packages, and triggers. These objects are stored in compiled form, so that when needed they can be loaded from the database into memory and run. For more information on stored objects, see Chapters 7, 8, and 9. As a result of this design decision, DDL statements are prohibited. Since a DDL statement will modify a database object, the permissions must be validated again. Validating the permissions would require that the identifiers be bound, and this has already been done during the compile.
To further illustrate this, consider the following hypothetical PL/SQL block:
BEGIN
CREATE TABLE temp_table (
num_value NUMBER,
char_value CHAR(10));
INSERT INTO temp_table (num_value, char_value)
VALUES (10, 'Hello');
END;
In order to compile this, the temp_table identifier needs to be bound. This process will check to see whether this table exists. However, the table won't exist until the block is run. But since the block can't even compile, there is no way that it can run.
DML and transaction control statements are the only SQL statements that don't have the potential to modify schema objects or permissions on schema objects, thus they are the only legal SQL statements in PL/SQL.
Using DDL
There is, however, an alternative. The built-in package DBMS_SQL is available with PL/SQL 2.1 and higher. This package allows you to create a SQL statement dynamically at run time, and then parse and execute it. Since the statement doesn't actually get created until run time, the PL/SQL compiler doesn't have to bind the identifiers in the statement, which allows the block to compile. Chapter 15 describes the DBMS_SQL package in detail. We could use the DBMS_SQL package to execute the CREATE TABLE statement in the preceding block, for example. However, the INSERT statement would fail to compile since the table wouldn't exist until the block is run. The solution to this problem is to use DBMS_SQL to execute the INSERT statement as well...