Re-using bind variables in Oracle PL/SQL

oracle, oracle10g, plsql

Solution

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.

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

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

- 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:

- 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 || '%'.`

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

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

Problem

Original source