USING DYNAMIC SQL IN
ORACLE FORMS
Thomas J. Corbley,
Dulcian, Inc.
Introduction
Dynamic SQL is a broad topic, encompassing
many issues and many possible applications. Dynamic SQL deals with the numerous
parameters listed in the DBMS_SQL package - the package that makes Dynamic SQL
possible. This paper does not attempt to present an exhaustive coverage of
Dynamic SQL. Rather, this paper is geared for a developer who is new to the
subject, covering the basics of why to use Dynamic SQL and how to do so. The
author will present:
The author also provides some background on
the main parameters needed to implement Dynamic SQL. For a more thorough
coverage of Dynamic SQL and the DBMS_SQL package, please see Oracle’s TechNet
web page at http://technet.oracle.com/. Choose ‘Documentation,’ ‘Find,’ and
search on ‘Dynamic SQL.’
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:
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.
1.
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.
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 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.
About the Author
Thomas Corbley is a developer for Dulcian
Inc., an Oracle consulting firm. He has presented at MAOP. This is his first
presentation at ECO. He can be contacted at tcorbley@dulcian.com or through Dulcian’s Website at www.dulcian.com.
©1999 Dulcian, Inc.