8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Dynamic Binds Using Contexts
When using dynamic SQL it is sometimes necessary to use variable numbers of bind variables depending on how the SQL statement is built. Typically this would be done using a repeat of your decision code to decide which variables to bind. Alternatively, contexts can be used to simplify this process.
Possibly a better alternative is to use query transformation to accomplish the same outcome.
Create The Context
First we create the context to hold our parameters.
CREATE OR REPLACE CONTEXT parameter USING context_api;
This requires the CREATE ANY CONTEXT privilege.
Create context_package
Next we create a package to manage the context. In this case I've simplified it to only handle name-value pairs with no reference to the user or session.
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
Test The Context
We can do a quick test of the context using.
EXEC context_api.set_parameter('variable1','value1');
SELECT SYS_CONTEXT('parameter','variable1')
FROM dual;
Create emp_count
Next we create an function to illustrate the dynamic binds without using the context.
CREATE OR REPLACE FUNCTION emp_count (
p_job IN emp.job%TYPE DEFAULT NULL,
p_deptno IN emp.deptno%TYPE DEFAULT NULL)
RETURN NUMBER AS
l_sql VARCHAR2(32767);
l_number NUMBER;
BEGIN
l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';
IF p_job IS NOT NULL THEN
l_sql := l_sql || 'AND job = :job ';
END IF;
IF p_deptno IS NOT NULL THEN
l_sql := l_sql || 'AND deptno = :deptno ';
END IF;
DBMS_OUTPUT.PUT_LINE(l_sql);
CASE
WHEN p_job IS NOT NULL AND p_deptno IS NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING p_job;
WHEN p_job IS NULL AND p_deptno IS NOT NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING p_deptno;
WHEN p_job IS NOT NULL AND p_deptno IS NOT NULL THEN
EXECUTE IMMEDIATE l_sql INTO l_number USING p_job, p_deptno;
ELSE
EXECUTE IMMEDIATE l_sql INTO l_number;
END CASE;
RETURN l_number;
END emp_count;
/
SHOW ERRORS
As you can see, every possible combination of parameters must be accounted for when performing the EXECUTE IMMEDIATE. This is a simple example so it still looks quite neat, but once the number of parameters increases this can easily become difficult to manage.
Using the context this can be written as follows.
CREATE OR REPLACE FUNCTION emp_count (
p_job IN emp.job%TYPE DEFAULT NULL,
p_deptno IN emp.deptno%TYPE DEFAULT NULL)
RETURN NUMBER AS
l_sql VARCHAR2(32767);
l_number NUMBER;
BEGIN
l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';
IF p_job IS NOT NULL THEN
context_api.set_parameter('job', p_job);
l_sql := l_sql || 'AND job = SYS_CONTEXT(''parameter'',''job'') ';
END IF;
IF p_deptno IS NOT NULL THEN
context_api.set_parameter('deptno', p_deptno);
l_sql := l_sql || 'AND deptno = SYS_CONTEXT(''parameter'',''deptno'') ';
END IF;
DBMS_OUTPUT.PUT_LINE(l_sql);
EXECUTE IMMEDIATE l_sql INTO l_number;
RETURN l_number;
END emp_count;
/
SHOW ERRORS
Test It
Finally we can test the dynamic bind and display the SQL statement used.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_count);
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_count(p_job => 'SALESMAN'));
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_count(p_deptno => 10));
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_count(p_job => 'CLERK', p_deptno => 10));
END;
/
For more information see:
Hope this helps. Regards Tim...