What is Dynamic SQL?
Dynamic SQL is SQL that does not have to be hard-coded into your programs. Dynamic SQL refers to the Data Manipulation Language (DML) and Data Definition Language (DDL) statements that you store in character strings and concatenate to suit your needs. You can build Dynamic SQL statements on the fly, allowing the entire statement to be created at runtime. Since all or part of your SQL statement does not need to be known until runtime, you have much greater flexibility in adapting your SQL statements to particular runtime requirements. You can create more general-purpose procedures, and you can simplify your code by not having to write a cursor for every SQL statement. Furthermore, since you can parse any DDL or DML statement using Dynamic SQL, this helps you to compensate for the inability to parse DDL statements directly from within PL/SQL blocks.
Dynamic SQL is made possible by the DBMS_SQL package, which was released with PL/SQL 2.1 (RDBMS 7.1). DBMS_SQL provides the ability to parse the SQL statements that you create from character strings, execute the resulting SQL, and store the results in columns or variables. The DBMS_SQL package also includes related functions and procedures to aid you in this effort, such as a function to track the number of rows processed, a function to track the last ROWID returned, and procedures to allow you to define columns containing the results of your queries.
Why Use Dynamic SQL?
The usefulness of Dynamic SQL will become clearer as several examples are presented. A short list of the benefits derived from using Dynamic SQL may be helpful to a developer new to this topic.
Dynamic SQL is easy to learn and use.
Within several hours, the author was using Dynamic SQL on a current Forms development project. As you will see below, there are very few main procedures and functions in the DBMS_SQL package, and after a few short examples, you will gain the proficiency to use them.
Dynamic SQL allows the SQL to be unknown at compile time.
Dynamic SQL can allow to you handle a situation where any or all of the following are unknown at compile time:
References to database objects, objects such as tables, views, sequences, etc.
Text of the SQL statement (including clauses, join conditions, columns, etc.)
The number of variables that you will need to include in the SQL statement.
The datatypes of variables and/or columns.
Dynamic SQL provides better performance than cursors in some cases.
Dynamic SQL will allow you to use specific indexes in your SQL statements to optimize queries, whereas regular cursors with variables may not allow you to do so. With Dynamic SQL, you can specify which indexes to use through the use of hints, which can be concatenated to your SQL statements dynamically. When working with Forms 4.5, the author was unable to utilize cursors with variables, because the variables used with such cursors would sometimes negate the indexes used in the optimization of the queries. This problem also exists in Forms 5.0. Thus, these cursors performed full table scans, even when hints were used, dramatically decreasing performance. With Dynamic SQL, the author was able to correct this problem and greatly improve performance.
When weighing these benefits against the cost of learning to use Dynamic SQL, the author has found that the benefits far outweigh the cost.
Simple Examples of Dynamic SQL
Below is a simple procedure which you can execute in SQL*PLUS to create a table, using SQL stored in a character string. This example notes the SQL*PLUS resulting execution statements. You can use this procedure to create a table with any name and column names that you would like.
Example of Dynamic SQL (DDL)
CREATE OR REPLACE PROCEDURE DDLDYNAMIC
(TABLENAME VARCHAR2, COLUMN_NAMES VARCHAR2)
AS
my_cursor INTEGER;
BEGIN
my_cursor:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(my_cursor, 'CREATE TABLE ' ||TABLENAME ||
' ( ' || COLUMN_NAMES || ' ) ', DBMS_SQL.V7);
DBMS_SQL.CLOSE_CURSOR(my_cursor);
END;
/
To execute this procedure and create a table, you could issue the following command. The SQL*PLUS feedback is listed.
SQL> exec ddldynamic('CUSTOMER','CUSTID NUMBER, NAME VARCHAR2(45)');
PL/SQL procedure successfully completed.
SQL>
Note: you may run into a problem, where the CREATE TABLE privilege is granted to you via a role. In this case, if you use the DBMS_SQL package to create a table, you will receive the Oracle insufficient privileges error, ‘ORA-01031.’ To get around this problem, you may grant the relevant privilege to the user directly. They will then be able to create a table using this procedure.
SQL> CONNECT SYSTEM/MANAGER;
Connected.
SQL> GRANT CREATE TABLE TO SCOTT;
Grant succeeded.
Example of Dynamic SQL (DML)
This example illustrates how to run a simple SELECT statement with one variable passed in, using Dynamic SQL. This procedure accepts a numeric argument, used as the customer ID in a "greater than" clause, concatenates the customer ID with the SELECT statement, runs the statement, and returns the CUSTID and NAME rows.
CREATE OR REPLACE PROCEDURE DMLWITHVAR2 (CUSTID_LOW_VALUE NUMBER) AS
my_cursor INTEGER;
NUM_ROWS_PROCESSED INTEGER;
CUSTID NUMBER;
NAME VARCHAR2(45);
SQLSTR VARCHAR2(70);
BEGIN
SQLSTR := 'SELECT CUSTID, NAME FROM CUSTOMER WHERE CUSTID > '||CUSTID_LOW_VALUE;
my_cursor:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (my_cursor, SQLSTR, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 1, CUSTID);
DBMS_SQL.DEFINE_COLUMN (my_cursor, 2, NAME, 45);
NUM_ROWS_PROCESSED := DBMS_SQL.EXECUTE (my_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS (my_cursor) > 0 THEN
DBMS_SQL.COLUMN_VALUE (my_cursor, 1, CUSTID);
DBMS_SQL.COLUMN_VALUE (my_cursor, 2, NAME);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(CUSTID) || ' ' || NAME);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (my_cursor);
END;
/
In order to run this procedure within SQL*PLUS, you must first turn serveroutput on, in order to see the results.
SQL> set serveroutput on
Executing this statement with the CUSTID_LOW_VALUE argument of 100 returns the following query output. Note that the CUSTID column on the left begins with 101, which is greater than the value of 100 that you supplied to the procedure at runtime. This follows from your query condition of CUSTID > CUSTID_LOW_VALUE.
SQL> EXEC DMLWITHVAR2(100);
101 TKB SPORT SHOP
102 VOLLYRITE
103 JUST TENNIS
104 EVERY MOUNTAIN
105 K + T SPORTS
106 SHAPE UP
107 WOMENS SPORTS
108 NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER
PL/SQL procedure successfully completed.
Major DBMS_SQL Functions/Procedures
This section will briefly list the main functions and procedures within the DBMS_SQL package, to explain their usage with Dynamic SQL.
A. DBMS_SQL.PARSE
The PARSE command checks the syntax of your SQL statement and associates the statement with the cursor in your program, which is defined with an INTEGER variable. The PARSE command can parse any DDL or DML statement. Note that with DDL statements, the PARSE executes an implied commit. PARSE takes 3 parameters:
The name of the cursor handle (name of the INTEGER variable that you have declared)
The SQL statement to be parsed (which may be either a literal string of your SQL in single quotes, or the name of a character variable holding your SQL statement)
The language flag
The language flag, either V6, V7, or NATIVE, determines how the SQL statement will be processed, by either version 6, 7, or whichever version is native to your system. The PARSE statement below will use the cursor handle MY_CURSOR, parse the SQL statement in the variable my_sql and use version 7.
DBMS_SQL.PARSE(my_cursor, my_sql, dbms_sql.v7);
B. DBMS_SQL.DEFINE_COLUMN
The DEFINE_COLUMN statement defines the columns to contain the output of your Dynamic SELECT statement. When using a query, you must use DEFINE_COLUMN statements to specify the variables that will hold the selected values. The positioning of the DEFINE_COLUMN statements corresponds to the order in which the columns appear in the SELECT list. For example, the following code specifies two output variables to hold the columns returned by the cursor MY_CURSOR in the following query:
SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER;
DBMS_SQL.DEFINE_COLUMN(MY_CURSOR, 1, CUSTID);
DBMS_SQL.DEFINE_COLUMN(MY_CURSOR, 2, NAME, 45);
Note that when the receiving variable is an INTEGER, as in the case of CUSTID, no length needs to be coded. However, with VARCHAR2 variables, such as NAME, the length must be specified.
C. DBMS_SQL.EXECUTE
The Execute statement executes your SQL statement, and it takes the name of your cursor as an argument.
ignore := DBMS_SQL.EXECUTE(MY_CURSOR);
D. DBMS_SQL.FETCH_ROWS
The FETCH ROWS statement fetches the rows of data that are returned by your query. Each call of the FETCH_ROWS statement returns another row of data. This statement takes the name of your cursor as an argument.
DBMS_SQL.FETCH_ROWS(MY_CURSOR);
E. DBMS_SQL.COLUMN_VALUE
You use this statement to retrieve the values returned from your query, so that you can use them in your program. The first argument is the name of the cursor that you are using. The second argument is the numerical order of the column that you wish to retrieve, based on that column’s order in your select statement, beginning with 1. The third argument is the variable that you have defined to hold the returned value.
DBMS_SQL.COLUMN_VALUE(MY_CURSOR, 1, CUSTID);
DBMS_SQL.COLUMN_VALUE(MY_CURSOR, 2, NAME);
F. DBMS_SQL.OPEN_CURSOR AND DBMS_SQL.CLOSE_CURSOR
An open cursor is always required to process an SQL statement. The OPEN_CURSOR function returns a cursor ID number, which identifies a valid cursor maintained within Oracle. The procedure CLOSE_CURSOR closes the cursor specified as an argument. Use the INTEGER variable that you have defined as the cursor handle to hold the cursor ID returned by the OPEN_CURSOR function, and pass this variable to the CLOSE_CURSOR procedure as its argument.
MY_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.CLOSE_CURSOR(MY_CURSOR);
G. DBMS_SQL.BIND_VARIABLE
In addition to these DBMS_SQL functions and procedures, there is also the BIND_VARIABLE procedure. This procedure allows you to code a placeholder in your SQL statements, to later be filled with the particular value that you choose. The BIND_VARIABLE procedure produces essentially the same result as concatenating a variable into your SQL string. The author has found that the latter method worked fine, with less coding. However, the BIND_VARIABLE procedure might be useful in those instances where the coding for concatenating a variable becomes slightly complex, such as in the case where you are passing literal values into your SQL string. In the author’s experience, use of the BIND_VARIABLE procedure would increase code readability and simplicity in these instances. The BIND_VARIABLE code below would be used to supply the value of the VAL_VAR variable to the ‘x’ placeholder in the following SQL statement.
SELECT CUSTID, NAME FROM CUSTOMER WHERE CUSTID >:x
DBMS_SQL.BIND_VARIABLE(MY_CURSOR, ‘x’, VAL_VAR );
As mentioned above, the same result could be achieved by coding:
SQL_STR VARCHAR2(40);
SQL_STR:=’SELECT CUSTID, NAME FROM CUSTOMER WHERE CUSTID > ‘ || TO_CHAR(VAL_VAR);
Using Dynamic SQL in Forms with Bug Workarounds
Using Dynamic SQL in Forms is very similar to using it in SQL*PLUS or PL/SQL, although there are several differences. The differences arise mainly from the presence of two bugs within Oracle, bugs requiring some workaround code adjustments. These are noted below.
1. PLS-00201 PARSE BUG
The first bug, which produces the error, "PLS-00201: identifier ‘V7’ must be declared," arises when you attempt to run the following PARSE statement from within Forms:
DBMS_SQL.PARSE(MY_CURSOR, ‘SELECT CUSTID FROM CUSTOMER’, DBMS_SQL.V7);
The problem is that the third parameter, the language flag, must be replaced with its actual value, 2. Use 0, 2 and 1 for V6, V7 and NATIVE, respectively. The following worked fine in Forms 4.5.
DBMS_SQL.PARSE(MY_CURSOR, ‘SELECT CUSTID FROM CUSTOMER’, 2);
2. Error 307 COLUMN_VALUE BUG
The second bug concerns the DBMS_SQL.COLUMN_VALUE statement, and produces "Error 307…too many declarations of ‘COLUMN_VALUE’ match this call." This error results when compiling Forms 4.5 Triggers with DBMS_SQL.
The workaround for this problem involves creating a stored function to use in place of the DBMS_SQL.COLUMN_VALUE statement, as follows.
CREATE OR REPLACE
PACKAGE DBMS_SQL_FIX IS
FUNCTION COLUMN_VALUE_CHAR (CID INTEGER, POSITION INTEGER) RETURN VARCHAR2;
FUNCTION COLUMN_VALUE_NUM (CID INTEGER, POSITION INTEGER) RETURN NUMBER;
END;
CREATE OR REPLACE
PACKAGE BODY DBMS_SQL_FIX IS
FUNCTION COLUMN_VALUE_CHAR (CID INTEGER, POSITION INTEGER) RETURN VARCHAR2 IS
VALUE VARCHAR2(2000);
BEGIN
DBMS_SQL.COLUMN_VALUE(CID, POSITION, VALUE);
RETURN VALUE;
END;
FUNCTION COLUMN_VALUE_NUM (CID INTEGER, POSITION INTEGER) RETURN NUMBER IS
VALUE NUMBER;
BEGIN
DBMS_SQL.COLUMN_VALUE(CID, POSITION, VALUE);
RETURN VALUE;
END;
END;
/
Please note the size of 2000 for the variable VALUE and adjust according to the size of the values that you may be returning.
You would then replace the usual COLUMN_VALUE statements as follows. Note that the first example uses CUSTID, a NUMBER variable, and the second example uses NAME, a VARCHAR2 variable. The NUMBER variable requires the use of COLUMN_VALUE_NUM whereas the VARCHAR2 variable requires the use of COLUMN_VALUE_CHAR.
replace
DBMS_SQL.COLUMN_VALUE (my_cursor, 1, CUSTID);
with
CUSTID:= DBMS_SQL_FIX.COLUMN_VALUE_NUM (my_cursor, 1);
And replace
DBMS_SQL.COLUMN_VALUE (my_cursor, 2, NAME);
With
NAME := DBMS_SQL_FIX.COLUMN_VALUE_CHAR (my_cursor, 2);
Other Factors to Consider
An additional consideration when using Dynamic SQL is where to store your code. As with other types of PL/SQL code, it is often preferable to store the code in the database, so that changes can be made quickly and efficiently, without having to change and recompile any of the forms. The author helped create a function in the database, which returned the SQL statement to be run in a VARCHAR2 variable. Once the statement had been constructed, it could then be run in a library, which allowed the results to be copied into form fields using the COPY statement. This way, when the customer inevitably requested changes to the rules involved in the creation of the SQL statement, these changes were very quickly made in the database function.
