|
|
Dynamic
Binds Using Context
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
We’ve already seen that using bind variables
is an important part of making sure that an application runs
efficiently. Using bind variables with dynamic SQL can prove
difficult at times as the number and ordering of the bind variables
may vary depending on how the SQL statement was built up. To
illustrate this we will need a test table, which can be created using
the create_dynamic_binds_tab.sql script.
create_dymanic_binds_tab.sql
CREATE TABLE dynamic_binds (
id NUMBER(10),
code_1 VARCHAR2(5),
code_2 VARCHAR2(5)
);
INSERT INTO
dynamic_binds (id, code_1, code_2) VALUES (1, ‘A’, ‘Z’);
INSERT INTO dynamic_binds (id, code_1, code_2) VALUES (2, ‘A’, ‘Y’);
INSERT INTO dynamic_binds (id, code_1, code_2) VALUES (3, ‘B’, ‘Z’);
INSERT INTO dynamic_binds (id, code_1, code_2) VALUES (4, ‘B’, ‘Y’);
INSERT INTO dynamic_binds (id, code_1, code_2) VALUES (5, ‘B’, ‘Z’);
COMMIT;
The get_row_count.sql script creates a
function that returns the number of rows in the dynamic_binds table
that match the specified criteria.
get_row_count.sql
CREATE OR
REPLACE FUNCTION get_row_count (
p_code_1 IN dynamic_binds.code_1%TYPE DEFAULT NULL,
p_code_2 IN dynamic_binds.code_2%TYPE DEFAULT NULL)
RETURN NUMBER AS
l_sql
VARCHAR2(32767);
l_number NUMBER;
BEGIN
l_sql
:= 'SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 ';
IF
p_code_1 IS NOT NULL THEN
l_sql := l_sql || 'AND code_1 = :b_code_1 ';
END IF;
IF
p_code_2 IS NOT NULL THEN
l_sql := l_sql || 'AND code_2 = :b_code_2 ';
END IF;
DBMS_OUTPUT.put_line('-----------------------------------');
DBMS_OUTPUT.put_line(l_sql);
CASE
WHEN p_code_1 IS NOT NULL AND p_code_2 IS NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING
p_code_1;
WHEN p_code_1 IS NULL AND p_code_2 IS NOT NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING
p_code_2;
WHEN p_code_1 IS NOT NULL AND p_code_2 IS NOT NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING
p_code_1, p_code_2;
ELSE
EXECUTE IMMEDIATE l_sql INTO l_number;
END CASE;
RETURN l_number;
END get_row_count;
/
SHOW ERRORS
Notice how the function contains one set of
logic for building up the SQL statement and another for deciding which
bind variables should be used. The get_row_count_test.sql script
can be used to test the function.
get_row_count_test.sql
SET
SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('get_row_count=' || get_row_count);
DBMS_OUTPUT.put_line('get_row_count(p_code_1 => ''A'')=' ||
get_row_count(p_code_1 => 'A'));
DBMS_OUTPUT.put_line('get_row_count(p_code_2 => ''Z'')=' ||
get_row_count(p_code_2 => 'Z'));
DBMS_OUTPUT.put_line('get_row_count(p_code_1 => ''B'', p_code_2 =>
''Y'')='
|| get_row_count(p_code_1 => 'B', p_code_2 => 'Y'));
END;
/
The results of this script display both the SQL statement
generated by the function and the resulting row count.
SQL> @get_row_count_test.sql
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1
get_row_count=5
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_1
= :b_code_1
get_row_count(p_code_1 => 'A')=2
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_2
= :b_code_2
get_row_count(p_code_2 => 'Z')=3
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_1
= :b_code_1 AND code_2 =
:b_code_2
get_row_count(p_code_1 => 'B', p_code_2 => 'Y')=1
PL/SQL procedure successfully completed.
From this we can see that each combination of
parameters generates a different SQL statement, hence the need for the
logic to decide how to bind the variables.
This is only a simple example, so you can see
how complex dynamic SQL statements can become almost impossible to
manage. For lots of programmers this complexity forces them give
up on using bind variables and revert to concatenating values directly
into the SQL statement. This is where the use of contexts can
come to our rescue. In chapter four we will describe how
contexts can be used to store global data, but they can also help us
with dynamic SQL bind variables.
First we must create a context and an
associated package to allow us to set the parameter values. The
creation of the context requires the CREATE ANY CONTEXT privilege,
which must be granted before running the create_context.sql script.
create_context.sql
CREATE OR
REPLACE CONTEXT parameter USING context_api;
CREATE OR
REPLACE PACKAGE context_api AS
PROCEDURE set_parameter(p_name IN VARCHAR2,
p_value IN VARCHAR2);
END context_api;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY context_api IS
--
----------------------------------------------------------------------------
PROCEDURE set_parameter (p_name IN VARCHAR2,
p_value IN VARCHAR2) IS
--
----------------------------------------------------------------------------
BEGIN
DBMS_SESSION.set_context('parameter', p_name, p_value);
END set_parameter;
--
----------------------------------------------------------------------------
END
context_api;
/
SHOW ERRORS
Once the context and the associated package
have been created we can perform a simple test to make sure the
context is working correctly.
SQL> EXEC
context_api.set_parameter('variable1','value1');
PL/SQL
procedure successfully completed.
SQL> SELECT
SYS_CONTEXT('parameter','variable1')
2 FROM dual;
SYS_CONTEXT('PARAMETER','VARIA
-----------------------------------------------------------------
value1
1 row
selected.
The call to the context_api.set_parameter
procedure created a context variable called “variable1” and assigned
it the value of “value1”. The query used the sys_context
function to retrieve the value, proving the context is working
correctly.
With the context in place we can recreate the
get_row_count function using the get_row_count_2.sql script, which
shows how the function is recoded to use the context.
get_row_count_2.sql
CREATE OR
REPLACE FUNCTION get_row_count (
p_code_1 IN dynamic_binds.code_1%TYPE DEFAULT NULL,
p_code_2 IN dynamic_binds.code_2%TYPE DEFAULT NULL)
RETURN NUMBER AS
l_sql
VARCHAR2(32767);
l_number NUMBER;
BEGIN
l_sql
:= 'SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 ';
IF
p_code_1 IS NOT NULL THEN
context_api.set_parameter('code_1', p_code_1);
l_sql := l_sql || 'AND code_1 = SYS_CONTEXT(''parameter'',''code_1'')
';
END IF;
IF
p_code_2 IS NOT NULL THEN
context_api.set_parameter('code_2', p_code_2);
l_sql := l_sql || 'AND code_2 = SYS_CONTEXT(''parameter'',''code_2'')
';
END IF;
DBMS_OUTPUT.put_line('-----------------------------------');
DBMS_OUTPUT.put_line(l_sql);
EXECUTE IMMEDIATE l_sql INTO l_number;
RETURN l_number;
END get_row_count;
/
|