PL/SQL User's Guide and Reference

Using DDL and Dynamic SQL

This section explains why PL/SQL does not support SQL data definition language (DDL) or dynamic SQL, then shows how to solve the problem.

Efficiency versus Flexibility

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.

Designed primarily for high-speed transaction processing, PL/SQL increases efficiency by bundling SQL statements and avoiding runtime compilation. Unlike SQL, which is compiled and executed statement- by-statement at run time (late binding), PL/SQL is processed into machine-readable p-code at compile time (early binding). At run time, the PL/SQL engine simply executes the p-code.

Some Limitations

However, this design imposes some limitations. For example, the p-code includes references to database objects such as tables and stored procedures. The PL/SQL compiler can resolve such references only if the database objects are known at compile time. In the following example, the compiler cannot process the procedure because the table is undefined until the procedure is executed at run time:

CREATE PROCEDURE create_table AS 
BEGIN 
   CREATE TABLE dept (deptno NUMBER(2), ...);  -- illegal
   ...
END;

In the next example, the compiler cannot bind the table reference in the DROP TABLE statement because the table name is unknown until the procedure is executed:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS 
BEGIN 
   DROP TABLE table_name;  -- illegal
   ...
END;

Overcoming the Limitations

However, the package DBMS_SQL, which is supplied with Oracle7, allows PL/SQL to execute SQL data definition and data manipulation statements dynamically at run time. For example, when called, the following stored procedure drops a specified database table:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
   cid INTEGER;
BEGIN
   /* Open new cursor and return cursor ID. */
   cid := DBMS_SQL.OPEN_CURSOR;
   /* Parse and immediately execute dynamic SQL statement built by
      concatenating table name to DROP TABLE command. */
   DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);
   /* Close cursor. */
   DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
   /* If an exception is raised, close cursor before exiting. */
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(cid);
      RAISE;  -- reraise the exception
END drop_table;

For more information about package DBMS_SQL, see Oracle7 Server Application Developer's Guide.