Re-using bind variables in Oracle PL/SQL

Question

I have a hefty SQL statement with unions where code keeps getting re-used. I was hoping to find out if there is a way to re-use a single bind variable without repeating the variable to for "USING" multiple times.

The code below returns "not all variables bound" until I change the "USING" line to "USING VAR1,VAR2,VAR1;"

I was hoping to avoid that as I'm referring to :1 in both instances - any ideas?

declare
var1 number :=1;
var2 number :=2;
begin
execute immediate '
select * from user_objects 
where 
rownum = :1
OR rownum = :2  
OR rownum = :1 '
using var1,var2;
end;
/

EDIT: For additional info, I am using dynamic SQL as I also generate a bundle of where conditions.

I'm not great with SQL arrays (I am using a cursor in my code but I think that will overcomplicate the issue) but the pseudocode is:

v_where varchar2(100) :='';
FOR i in ('CAT','HAT','MAT') LOOP
  v_where := v_where || ' OR OBJECT_NAME LIKE ''%' || i.string ||'%''
END;
  v_where := ltrim(v_where, ' OR');

And then modifying the SQL above to something like :

execute immediate '
select * from user_objects 
where 
rownum = :1
OR rownum = :2  
OR rownum = :1 AND ('||V_WHERE||')'
using var1,var2;

Answer 1

There are some options you might consider, although they may require changes, either to how you execute your SQL statement or to your SQL statement itself.

1. Use DBMS_SQL instead of EXECUTE IMMEDIATE -- DBMS_SQL (see http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm) is harder to use than EXECUTE IMMEDIATE, but gives you more control over the process -- including the ability (through DBMS_SQL.BIND_VARIABLE and DBMS_SQL.BIND_ARRAY) to bind by name instead of by position.
2. Use EXECUTE IMMEDIATE with a WITH clause -- You might be able restructure your query to use WITH clause that gathers your bind variables in subquery at the beginning, and then joins to the subquery (instead of referencing the bind variables directly) whenever it needs them. It might look something like this

with your_parameters as 
    (select :1 as p1, :2 as p2 from dual) 
select * 
from your_table, your_parameters 
where your_table.some_column1 = your_parameters.p1 
  and your_table.some_column2 <= your_parameters.p1 
  and your_table.some_column3 = your_parameters.p2

This could affect the performance of your query, but it might be an acceptable compromise.

3. Don't use dynamic SQL -- Of course, if you don't need dynamic SQL, you don't need to use EXECUTE IMMEDIATE, so the "bind only by position" limitiation does not apply. Are you sure you really need to use dynamic SQL?

EDIT: If you're using dynamic SQL because you have a variable number of OR conditions like you posted in your edit, you might be able to avoid using dynamic SQL by doing one of the following:

1. If the OR criteria come from a table (or query) -- Join to that table (or query) instead of using a list of OR criteria. For example, if CAT, HAT, and MAT are listed in a column named YOUR_CRITERIA in a table named YOUR_CRITERIA_TABLE you might add YOUR_CRITERIA_TABLE to the FROM clause and replace the OBJECT_NAME LIKE '%CAT% OR OBJECT_NAME LIKE '%MAT% OR OBJECT_NAME LIKE '%HAT% OR OBJECT_NAME LIKE '%MAT% in the WHERE clause with something like OBJECT_NAME LIKE '%' || YOUR_CRITERIA_TABLE.YOUR_CRITERIA || '%'.
2. Otherwise, you might put the criteria in a global temporary table -- If your criteria don't come from a table (or query), you could (once, at design time, not at run time) create a global temporary table to hold them, and then at run time, insert the criteria into the global temporary table and then join to it as described in item 1.
3. Or, you might put the criteria in an nested table -- This is like item 2, except uses a nested table (one created using CREATE TYPE...IS TABLE OF) instead of a global temporary table. You could create or own nested table type, or use a built-in one like SYS.ODCIVARCHAR2LIST. In PL/SQL, you would populate an variable of this type, and then use it like a "real" table like in item 1.

An example of item 3 might look something like:

DECLARE
    tblCriteria SYS.ODCIVARCHAR2LIST;

BEGIN
    tblCriteria := SYS.ODCIVARCHAR2LIST();

    -- In "real" code you might populate the nested table in a loop.
    -- This example populates it explicitly so that it will compile.  For the
    -- purpose of the example, we could have populated the nested table in 
    -- a single statement:

    -- tblCriteria := SYS.ODCIVARCHAR2LIST('CAT', 'HAT', 'MAT');

    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'CAT';

    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'HAT';

    tblCriteria.EXTEND(1);
    tblCriteria(tblCriteria.LAST) := 'MAT';

    FOR rec IN
    (
        SELECT
            USER_OBJECTS.*
        FROM
            USER_OBJECTS,
            TABLE(tblCriteria) YOUR_NESTED_TABLE
        WHERE
            USER_OBJECTS.OBJECT_NAME LIKE '%' || YOUR_NESTED_TABLE.COLUMN_VALUE || '%'
    )
    LOOP
        -- Do something.  For example, print out the object name.
        DBMS_OUTPUT.PUT_LINE(rec.OBJECT_NAME);
    END LOOP;
END;