Day 17

Generating Dynamic
SQL

by Timothy Atwood

The DBMS_SQL package is a powerful package, and the most difficult to understand. This package allows you to execute non-query DDL and DML statements, SQL queries, and anonymous blocks of PL/SQL from within
PL/SQL.

Why are you not allowed to execute these items from within PL/SQL? The problem occurs when executing code in a SQL environment. A typical SQL query always opens a cursor and then parses the SQL for any errors. Any variables are then bound to the session. In regular PL/SQL, before the code is executed, it is parsed for errors. For example, if you were trying to create a table without using dynamic SQL, the PL/SQL code would check the code and then attempt to validate that all tables are present and all values are valid before executing the PL/SQL block. Because the table does not exist yet, the PL/SQL code will raise

an error and never execute because the table could not be bound. PL/SQL was designed to bind all variables first, in order to allow for faster execution. DBMS_SQL circumvents this limitation by allowing you to literally create and execute SQL code dynamically. If you use this powerful feature, your users can execute different queries from the same table based upon variables instead of fixed SQL.

Today's lesson discusses the following topics:

• Steps for using DBMS_SQL
• Using non-query DDL and DML statements
• Executing queries with DBMS_SQL
• Executing PL/SQL with DBMS_SQL

Steps for Using DBMS_SQL

The following steps give you an overview of what is required for coding non-query DDL and DML statements, executing SQL queries, and executing anonymous blocks:

1. Open the cursor with OPEN_CURSOR.
2. Parse the SQL statement with PARSE.
3. Bind any variables with BIND_VARIABLE.
4. If using SELECT statements, you must define the output columns by using DEFINE_COLUMN.
5. Execute the SQL statement with EXECUTE.
6. Use FETCH_ROWS to fetch rows from the cursor. You can also use EXECUTE_AND_FETCH to achieve the same goal in only one step.
7. Use VARIABLE_VALUE to retrieve values, which can change upon the execution of dynamic SQL.
8. If fetching rows, then you must use COLUMN_VALUE to retrieve data from the cursor and store it into a local variable.
9. When all processing is finished, you must close the cursor using CLOSE_CURSOR.

Three Types of Statements Processed with
DBMS_SQL

As stated earlier, the three types of statements that can be processed by the DBMS_SQL package are non-query DDL and DML statements, SQL queries, and anonymous blocks of PL/SQL. Based upon the steps required for processing these types of statements, this section examines each type of statement in much greater detail.

Using the DBMS_SQL Package with Non-Query DDL
and DML Statements

DDL (Data Definition Language) and DML (Data Manipulation Language) allow you to use CREATE, DROP, INSERT, UPDATE, DELETE, and so forth in your PL/SQL code. The following steps are required:

1. Open the cursor.
2. Parse the statement.
3. Bind input variables (if required).
4. Execute the statement.
5. Close the cursor.

Opening a Cursor for Non-Query DDL and DML Statements
Whenever you execute SQL or PL/SQL, a cursor is opened transparently. When creating dynamic SQL, you must specify every step, including opening the cursor by using OPEN_CURSOR. The syntax for the OPEN_CURSOR function is as follows:

FUNCTION OPEN_CURSOR RETURN INTEGER;

The OPEN_CURSOR function returns an integer, which is the cursor ID. The cursor ID will be retained for use until the cursor is closed. As you can see, leaving a cursor open without closing the cursor using CLOSE_CURSOR wastes valuable resources. You can now perform as many statements as desired by referencing the cursor ID returned.

Parsing Statements for Non-Query DDL and DML Statements
After the cursor is opened, the statements are parsed to check for syntax errors. Normally, a procedure would show all errors before it could be run successfully. You now run the risk of syntax errors during production runtime, so you must check for all possible errors and trap user mistakes.

The Syntax for the PARSE Procedure

PROCEDURE PARSE(cursor_id IN INTEGER,
                          statement_to_parse IN VARCHAR2,
                          version IN VARCHAR2);

cursor_id is the cursor assigned by the OPEN_CURSOR statement. statement_to_parse is the SQL statement you want to parse. The last parameter, version, is the version type.

The following versions are allowed:

• NATIVE—Native handling for the server
• V6—Handling for Oracle version 6.x
• V7—Handling for Oracle version 7.x
• V8—Handling for Oracle version 8.x

The version can be expressed as DBMS_SQL.NATIVE, DBMS_SQL.V8, and so on.

Binding Variables for Non-Query DDL and DML Statements
Binding enables you to bind a specific variable to a placeholder, which is identified by a colon. Because you can bind all types of PL/SQL variables, the overloaded BIND_VARIABLE procedure can handle many datatypes.

The Syntax for Binding Variables
The syntax for the NUMBER datatype is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN NUMBER);

The two syntaxes for type VARCHAR2 are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2,
                             out _value_size IN INTERGER);