8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
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.
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...