EXECUTE IMMEDIATE BULK COLLECT DYNAMIC USING

Question

I have a requirement where i have to add where clause in dynamic sql based on certain conditions..

SELECT x 
  FROM DUMMY_TBL 
 WHERE COL1 = :1

OR based on a condition it can be as

SELECT x 
  FROM DUMMY_TBL 
 WHERE COL1 = :1
   AND COL2 = :2

OR based on another condition it can be as

SELECT x 
  FROM DUMMY_TBL 
 WHERE COL1 = :1
   AND COL2 = :2
   AND COL3 = :3

My question is related to Using variable in "EXECUTE IMMEDIATE".. how can i pass dynamic list of variables in USING ?

  EXECUTE IMMEDIATE query BULK COLLECT INTO code_list USING var1;
  EXECUTE IMMEDIATE query BULK COLLECT INTO code_list USING var1, var2;
  EXECUTE IMMEDIATE query BULK COLLECT INTO code_list USING var1, var2, var3;

can we do something like :

var_list := 'var1, var2, var3'; --> Prepare this in code based in condition and pass this in execute command

EXECUTE IMMEDIATE query BULK COLLECT INTO code_list USING var_list ;

Wernfried Domscheit · Accepted Answer · 2025-07-09 06:45:23Z

You cannot do it with EXECUTE IMMEDIATE directly, but you can use the DBMS_SQL package to achieve it. Would be something like this:

DECLARE 
    curid INTEGER;
    ret INTEGER;
    refCur SYS_REFCURSOR;
    sqlstr VARCHAR2(10000);
    code_list ...
BEGIN

    sqlStr := 'SELECT x FROM DUMMY_TBL WHERE COL1 = :cond1 AND COL2 = :cond2';

    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, sqlStr, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_VARIABLE (curid, ':cond1', var1);
    DBMS_SQL.BIND_VARIABLE (curid, ':cond2', var2);
    ... add more according to your conditions
    ret := DBMS_SQL.EXECUTE(curid);
    
    refCur := DBMS_SQL.TO_REFCURSOR(curid);
    FETCH refCur BULK COLLECT INTO code_list;
    
END;

DBMS_SQL.TO_REFCURSOR can be used if the number and type of selected columns is static. Otherwise you have to define columns with DBMS_SQL.DEFINE_COLUMN, then DBMS_SQL.EXECUTE_AND_FETCH and then DBMS_SQL.COLUMN_VALUE

Koen Lostrie · Accepted Answer · 2025-07-08 20:44:23Z

2

A "dynamic list" is not possible ( docs state: "Using clause: Specifies bind variables, using positional notation." but it can be done using the following method:

SELECT x 
  FROM DUMMY_TBL 
 WHERE (:1 IS NULL OR COL1 = :1)
   AND (:2 IS NULL OR COL2 = :2)
   AND (:3 IS NULL OR COL3 = :3)

and always pass all bind variables in the EXECUTE IMMEDIATE statement . If any of the variables is NULL, then the condition for that variable will not be evaluated. It looks a bit odd but it has worked for me.

answered Jul 8 at 20:44

Koen Lostrie

19.4k2 gold badges16 silver badges25 bronze badges

1 Comment

Wernfried Domscheit Jul 9 at 6:49

Note, this works only if all possible columns always exist in the selected table. It might be a problem if the table is also dynamic.

Matthew McPeak · Accepted Answer · 2025-07-08 22:15:54Z

You can build the SQL in such a way that every possible bind variable is always represented exactly once in the query.

For each variable, if the variable has a value (not null), add the condition on the bind variable (e.g., COL2 = :2). Otherwise, add a meaningless a null check (e.g., :2 IS NULL).

For example,

SELECT x 
  FROM DUMMY_TBL 
 WHERE COL1 = :1
   AND COL2 = :2
   AND COL3 = :3

Or.

SELECT x 
  FROM DUMMY_TBL 
 WHERE COL1 = :1
   AND :2 IS NULL
   AND COL3 = :3

Or.

SELECT x 
  FROM DUMMY_TBL 
 WHERE :1 IS NULL
   AND :2 IS NULL
   AND COL3 = :3

In any case, you'll pass the same number of bind values every time.

This approach also has the benefit of having separate SQLs for separate sets of conditions, allowing each one to be separately optimized for whatever indexes might exist on the relevant columns.

If the number of bind variables (N) is reasonable, this should be fine. You'll have potentially 2^N distinct SQLs in your library cache. If N gets above, say 10, and it's a high-volume system (lots of queries), library cache could be a problem (which the answer by Koen Lostrie would not have). (This is a concern worth pointing out, though in practice I wouldn't worry about it too much except in extreme circumstances).

Collectives™ on Stack Overflow

EXECUTE IMMEDIATE BULK COLLECT DYNAMIC USING

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related