Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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.

Note. 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...

Back to the Top.