Well known fact: most queries you execute often, have better performance when you use bind variables. On two separate occasions in the last month, a developer came to me with a question about Dynamic SQL.
The problem they both had was that when dynamically creating a (complex) SQL statement, they wanted to use bind variables. However, they didn't know how many variables they were actually going to use and they didn't know how many times they were going to use them throughout the statement.
Let's start out with something simple to clarify the problem:
CREATE OR REPLACE PROCEDURE dynamic_sql_example
( par_a IN varchar2
, par_b IN number
, par_c IN varchar2
)
IS
TYPE t_cur IS REF CURSOR;
c_cur t_cur;
type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
r_rec t_rec;
--
l_qt varchar2(1) := chr(39);
BEGIN
OPEN c_cur FOR 'SELECT '||l_qt||par_a||l_qt||
', '||par_b||
', '||l_qt||par_c||l_qt||
' FROM dual';
FETCH c_cur INTO r_rec;
-- do something here
CLOSE c_cur;
END;
/
All that this actually does is open a cursor for a simple SQL select statement. The problem with this statement is that it will generate a different execution plan for each distinct set of parameters. In order to prevent this, we quickly switch to bind variables:
CREATE OR REPLACE PROCEDURE dynamic_sql_example
( par_a IN varchar2
, par_b IN number
, par_c IN varchar2
)
IS
TYPE t_cur IS REF CURSOR;
c_cur t_cur;
type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
r_rec t_rec;
--
l_qt varchar2(1) := chr(39);
BEGIN
OPEN c_cur FOR 'SELECT :x par_a'||
', :y par_b'||
', :z par_c'||
' FROM dual'
USING par_a, par_b, par_c;
FETCH c_cur INTO r_rec;
CLOSE c_cur;
END;
/
This statement is superior to the previous one, as it will generate one execution plan because it uses bind variables. If you think that's just a small detail, try googling "bind variables" and see why you should use them...
So, now the developer wants to create a dynamic SQL statement using an as of yet unknown number and order of bind variables:
CREATE OR REPLACE PROCEDURE dynamic_sql_example
( par_a IN varchar2
, par_b IN number
, par_c IN varchar2
)
IS
TYPE t_cur IS REF CURSOR;
c_cur t_cur;
type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
r_rec t_rec;
--
l_qt varchar2(1) := chr(39);
--
l_stmt varchar2(1000);
--
BEGIN
IF par_b = 1 THEN
l_stmt := 'SELECT :x par_a'||
' , :y par_b'||
' , :z par_c'||
' FROM dual'||
' WHERE :y = 1'||
' AND :x = ''A''';
ELSE
l_stmt := 'SELECT :x par_a'||
' , :y par_b'||
' , :z par_c'||
' FROM dual'||
' WHERE :x = ''Z''';
END IF;
--
OPEN c_cur FOR l_stmt
USING par_a, par_b, par_c;
FETCH c_cur INTO r_rec;
CLOSE c_cur;
END;
/
Depending on the value of par_b, the statement will need either 5 bind variables (when the value = 1) or 4 bind variables (when the value != 1). Thus, we can not use the OPEN..FOR..USING construction without having to resort to another IF..THEN..ELSE construct. When the statement becomes more complex, this becomes too complex to handle. Executing the code as displayed above, will net you an "ORA-01008: not all variables bound" error.
The alternative is to take the bind variables out of the equation and store them in an unambigious and predetermined order:
CREATE OR REPLACE PROCEDURE dynamic_sql_example
( par_a IN varchar2
, par_b IN number
, par_c IN varchar2
)
IS
TYPE t_cur IS REF CURSOR;
c_cur t_cur;
type t_rec is record (par_a varchar2(20), par_b number, par_c varchar2(20));
r_rec t_rec;
--
l_qt varchar2(1) := chr(39);
--
l_with varchar2(1000);
l_stmt varchar2(1000);
--
BEGIN
l_with := 'WITH my_parameters '||
' AS (SELECT :x par_a'||
' , :y par_b'||
' , :z par_c'||
' FROM dual) ';
--
IF par_b = 1 THEN
l_stmt := 'SELECT par.par_a par_a'||
' , par.par_b par_b'||
' , par.par_c par_c'||
' FROM dual'||
' , my_parameters par'||
' WHERE par.par_b = 1'||
' AND par.par_a = ''A''';
ELSE
l_stmt := 'SELECT par.par_a par_a'||
' , par.par_b par_b'||
' , par.par_c par_c'||
' FROM dual'||
' , my_parameters par'||
' WHERE par.par_a = ''Z''';
END IF;
--
OPEN c_cur FOR l_with || l_stmt
USING par_a, par_b, par_c;
FETCH c_cur INTO r_rec;
CLOSE c_cur;
END;
/
Using a WITH..AS construct, we now have a predetermined set of bind variables in a predetermined order. By referencing them in the rest of the code as column values, we are completely independent of the number and order of the bind variables in that part.
Mission Accomplished :-)
