Finding dynamic SQL placeholders in a string

Question:
I am using dynamic SQL to execute various PL/SQL blocks that are stored as strings in rows in a database table. I need to figure out how many placeholders for bind variables appear in those blocks, and their names. Does Oracle provide a utility that returns this information?
Answer:
Oracle does not provide any built-in functionality to return this information (check out the "What is Available from Oracle" sidebar by Bryn Llewellyn for more details on this). Instead, you must analyze the string yourself and extract the required information. To save you the trouble, however, I have written a utility to do just that (it handles most scenarios; restrictions are described below): the dyn_placeholder package (appended to end of this answer).
Before explaining the package, let's review the issues at hand. When I execute a dynamic SQL statement that relies on values of local variables in my program, I can pass those values as bind variables. To do so, I must insert placeholders in my statement so that Oracle can parse the statement independent of any specific values for those placeholders. A placeholder consists of the colon character followed by a number or identifier. There are all, therefore, valid placeholders:
:14
:my_variable
:"abc"
Then when the statement is executed, I provide the specific values to be bound to the placeholders. In native dynamic SQL, this is done with the USING clause of EXECUTE IMMEDIATE, as you see below in the upd_num_col procedure that updates the specified (dynamic) numeric column of the employees table for all rows with hire dates between the specified values:
CREATE OR REPLACE PROCEDURE upd_num_col (
col_in IN VARCHAR2,
start_in IN DATE,
end_in IN DATE,
val_in IN NUMBER
)
IS
BEGIN
EXECUTE IMMEDIATE
'UPDATE employees SET '
|| col_in
|| ' = :val WHERE hire_date BETWEEN :lodate AND :hidate'
USING val_in, start_in, end_in;
END upd_num_col;
/
With DBMS_SQL, you will call the BIND_VARIABLE subprogram, which I demonstrate below:
CREATE OR REPLACE PROCEDURE upd_num_col (
col_in IN VARCHAR2
, start_in IN DATE
, end_in IN DATE
, val_in IN NUMBER
)
IS
cur PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
BEGIN
DBMS_SQL.parse ( cur
, 'UPDATE employees SET '
|| col_in
|| ' = :val WHERE hire_date BETWEEN :lodate AND :hidate'
, DBMS_SQL.native
);
DBMS_SQL.bind_variable ( cur, 'val', val_in );
DBMS_SQL.bind_variable ( cur, 'lodate', start_in );
DBMS_SQL.bind_variable ( cur, 'hidate', end_in );
fdbk := DBMS_SQL.EXECUTE ( cur );
DBMS_SQL.CLOSE_CURSOR (cur);
END upd_num_col;
/
Note that as an alternative to binding, you could concatenate your values directly into your SQL statement. Here is a rewrite of upd_num_col (NDS version) using concatenation:
CREATE OR REPLACE PROCEDURE upd_num_col (
col_in IN VARCHAR2
, start_in IN DATE
, end_in IN DATE
, val_in IN NUMBER
)
IS
c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS';
BEGIN
EXECUTE IMMEDIATE 'UPDATE employess SET '
|| col_in
|| ' = '
|| val_in
|| ' WHERE hire_date BETWEEN TO_DATE ('''
|| TO_CHAR ( start_in, c_format )
|| ''', '''
|| c_format
|| ''') AND TO_DATE ('''
|| TO_CHAR ( end_in, c_format )
|| ''', '''
|| c_format
|| ''')';
END upd_num_col;
/
As I hope you can readily see, concatenation has several disadvantages:
• The resulting dynamic string is more complicated and harder to maintain than a dynamic SQL statement that uses bind variables and placeholders.
• You will need to perform conversions from whatever type of data you need to insert into the statement into a string. Sometimes this is straightforward, but in other cases it is difficult or impossible (you can't really concatenate a nested table into a statement!). With binding, you can work directly and intuitively with all known SQL datatypes.
• Each new value results in a physically distinct SQL statement, which means it must be re-parsed. If, on the other hand, you simply bind in different values to the same placeholder, the SQL statement does not change physically and so under some circumstances you can avoid re-parsing of the statement.
Now, it is true that with Oracle Database 10g, you can take advantage of the user-defined quote character to simplify matters a bit, as you can see below:
CREATE OR REPLACE PROCEDURE upd_num_col (
col_in IN VARCHAR2
, start_in IN DATE
, end_in IN DATE
, val_in IN NUMBER
)
IS
c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS';
BEGIN
EXECUTE IMMEDIATE 'UPDATE employess SET '
|| col_in
|| ' = '
|| val_in
|| q'[ WHERE hire_date BETWEEN TO_DATE (']'
|| TO_CHAR ( start_in, c_format )
|| q'[', ']'
|| c_format
|| q'[') AND TO_DATE (']'
|| TO_CHAR ( end_in, c_format )
|| q'[', ']'
|| c_format
|| q'[')]';
END upd_num_col;
/
Still, I would suggest that you bind whenever possible and as much as possible.
Note that you can only bind into your SQL statement those expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. You cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation. That is why the upd_num_col procedure that relies on binding still had to concatenate the name of the column being updated.
Another complication regarding bind variables of which you should be aware has to do with duplicate placeholders and native dynamic SQL. If you use the same placeholder name more than once in a dynamic SQL statement, you must provide a bind variable for each occurrence of that name. If, however, you are executing a dynamic PL/SQL block, you provide the bind variable only for the first occurrence of the name; that same value will be automatically used for the other placholders of the same name.
The following two implementations of upd_num_col demonstrate this difference.
1. Dynamic SQL with duplicate placeholders
PROCEDURE upd_num_col (
col_in IN VARCHAR2,
start_in IN DATE,
end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'UPDATE employees SET ' || col_in || ' = :val
WHERE hire_date BETWEEN :lodate AND :hidate
AND :val IS NOT NULL';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in, val_in;
END upd_num_col;
2. Dynamic PL/SQL block with duplicate placeholders
PROCEDURE upd_num_col (
col_in IN VARCHAR2,
start_in IN DATE,
end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'BEGIN
UPDATE employees SET ' || col_in || ' = :val
WHERE hire_date BETWEEN :lodate AND :hidate
AND :val IS NOT NULL;
END;';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in;
END upd_num_col;
Now, back to your question! You are being passed a dynamic SQL string that may have one or more placeholders. How many are there and what are their names? To figure this out, you need to locate any colons in the string that are not followed by "=" (that is, ":=" is part of an assignment).
I have put together a package to do this for you. The dyn_placeholder package offers a number of programs you can use to obtain information about placeholders in your dynamic string. Please take note of the following restrictions on this utility:
• The dyn_placeholder programs may incorrectly return as a placeholder a string within a comment, as in 'SELECT /* :not_a_ph */ dummy FROM dual'.
• It is unlikely that you could glean all the information you need to properly execute your dynamic PL/SQL block solely from this information returned by dyn_placeholder programs. You will almost certainly need to supplement it with metadata about the statement itself, such as the type of statement (DDL, DML, query, PL/SQL block, the datatypes of the bind variables, etc.). In fact, it is likely that dyn_placeholder will serve mostly as a QA validator of your metadata.
Here is a description of the files in the download for this utility:
dyn_placeholder.pks — specification of dyn_placeholder package
dyn_placeholder.pkb — body of dyn_placeholder package
qu_assert.pks/pkb — assertion package used by test script
ut_dyn_placeholder.pkg — unit test package for dyn_placeholder
dyn_placeholder.tst — test script that will install all necessary program units (specifically, all those listed above) and then run the tests
dyn_placeholder_demo.sql — demonstration of using the dyn_placholder.all_in_string function
I offer the test package so that you can run the test script and verify the behavior of the utility before using it (note: the unit test package was generated from a test definition from Qute, the Quick Unit Test Engine, a tool that will be available in March 2006 from www.unit-test.com).
The programs in dyn_placeholder are as follows:
1. Return the number of placeholders found in the string.
FUNCTION dyn_placeholder.count_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT NULL
)
RETURN PLS_INTEGER;
2. Return the nth placeholder in the string.
FUNCTION dyn_placeholder.nth_in_string (
string_in IN VARCHAR2
, nth_in IN PLS_INTEGER
, dyn_plsql_in IN BOOLEAN DEFAULT NULL
)
RETURN placeholder_rt;
3. Return all the placeholders in an associative array.
FUNCTION dyn_placeholder.all_in_string (
string_in IN VARCHAR2
, dyn_plsql_in IN BOOLEAN DEFAULT NULL
)
RETURN placeholder_aat;
4. Display all the placeholders (a quick means to verify the analysis of placeholders in your string).
PROCEDURE dyn_placeholder.show_placeholders (
list_in IN placeholder_aat
, dyn_plsql_in IN BOOLEAN DEFAULT NULL
);
The associate array type used to hold the parsed information is defined as follows:
TYPE dyn_placeholder.placeholder_rt IS RECORD (
NAME VARCHAR2 ( 100 ) –- name of the placholder
, POSITION PLS_INTEGER -- position of placeholder in the string
);

TYPE dyn_placeholder.placeholder_aat IS TABLE OF placeholder_rt
INDEX BY PLS_INTEGER;
Note: if you are running Oracle8i, you will need to change the INDEX BY datatype to BINARY_INTEGER.
Here is an example of using this package (see dyn_placeholder_demo.sql):
SQL> DECLARE
2 c_dyn_block CONSTANT VARCHAR2 ( 32767 )
3 := 'BEGIN DBMS_OUTPUT.PUT_LINE (:string1); DBMS_OUTPUT.PUT_LINE (:date1); END;';
4 --
5 l_placeholders dyn_placeholder.placeholder_aat;
6 cur PLS_INTEGER := DBMS_SQL.open_cursor;
7 fdbk PLS_INTEGER;
8 BEGIN
9 l_placeholders := dyn_placeholder.all_in_string ( c_dyn_block );
10 --
11 DBMS_SQL.parse ( cur, c_dyn_block, DBMS_SQL.native );
12
13 FOR indx IN 1 .. l_placeholders.COUNT
14 LOOP
15 IF l_placeholders ( indx ).NAME LIKE 'STRING%'
16 THEN
17 DBMS_SQL.bind_variable ( cur, l_placeholders ( indx ).NAME
18 , 'Steven' );
19 ELSIF l_placeholders ( indx ).NAME LIKE 'DATE%'
20 THEN
21 DBMS_SQL.bind_variable ( cur, l_placeholders ( indx ).NAME, SYSDATE );
22 END IF;
23 END LOOP;
24
25 fdbk := DBMS_SQL.EXECUTE ( cur );
26 DBMS_SQL.CLOSE_CURSOR (cur);
27 END;
28 /
Steven
11-JAN-06
I hope that you find this utility to be of use as you worth with very generic and complex dynamic SQL statements and dynamic PL/SQL blocks.