Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Dynamic Binds Using Query Transformation

When using dynamic SQL it is sometimes necessary to use variable numbers of bind variables depending on how the SQL statement is built. Rather than having to decide how many bind variables to use and what order they should be presented, you can use a consistent set of bind variables and allow query transformation to throw away the ones you don't need.

Note. This is an alternative to using contexts to accomplish the same outcome.

Create Dynamic Function

First, we create a function to illustrate the issue when using dynamic binds.

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.

Use Query Transformation

Next, we write the same function but allow query transformation to get rid of predicates, and therefore bind variables, that we don't need.

Note. This is not runtime short-circuit evaluation of predicates, this is the optimizer rewriting the statement at parse time to remove unnecessary predicates. At least one reader misunderstood the concept of query transformation, so I thought it was worth stressing this point.

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 ';
  ELSE
    l_sql := l_sql || 'AND ((1=1) OR :job IS NULL) ';
  END IF;

  IF p_deptno IS NOT NULL THEN
    l_sql := l_sql || 'AND deptno = :deptno ';
  ELSE
    l_sql := l_sql || 'AND ((1=1) OR :deptno IS NULL) ';
  END IF;
  
  DBMS_OUTPUT.PUT_LINE(l_sql);
  
  EXECUTE IMMEDIATE l_sql INTO l_number USING p_job, p_deptno;
  
  RETURN l_number;
END emp_count;
/
SHOW ERRORS

If the parameter is not set, a line in the form of "AND ((1=1) OR :bind IS NULL)" is included in the statement. At parse time, the query optimizer sees that "1=1" is always true, therefore this whole OR expression must always equate to true, so it throws the predicate away. Notice that the "EXECUTE IMMEDIATE" does not need any complex logic to decide the bind variable order. All bind variables are always used.

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;
/

10053 Trace Output

For a little extra clarity, the following test uses the 10053 trace output to show the query transformation that takes place.

The following test turns on the 10053 trace, runs the EMP_COUNT procedure passing in no parameters, so they both default to NULL, then turns off the trace.

ALTER SESSION SET EVENTS '10053 trace name context forever';

SET SERVEROUTPUT ON

BEGIN
  DBMS_OUTPUT.PUT_LINE('COUNT: ' || emp_count);
END;
/

ALTER SESSION SET EVENTS '10053 trace name context off';

The trace file produced is very big, but there are two sections that show what is happening with this query transformation. The section titled "QUERY BLOCK TEXT" shows the query we sent to the server.

QUERY BLOCK TEXT
****************
SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 AND ((1=1) OR :job IS NULL) AND ((1=1) OR :deptno IS NULL)

So the procedure sent the SQL statement with the following dummy entries.

1=1
((1=1) OR :job IS NULL)
((1=1) OR :deptno IS NULL)

The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."EMP" "EMP"

All of the dummy entries were thrown away, leaving the clean final statement.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.