Building WHERE clause in Native Dynamic SQL

I'm writing a PL/SQL procedure that dynamically generates a SQL statement based on the parameters it receives. Essentially, each parameter maps to a WHERE clause condition. The rest of the statement is constant (i.e. table names, select fields, etc).

I can do this just fine using string concatenation and EXECUTE IMMEDIATE. However, since the procedure will be run very often I'd like to use bind variables. The problem is that there doesn't seem to be a way to parameterize the USING clause of EXECUTE IMMEDIATE. In other words, I build my list of bind variables in the SQL text, I need to be able to do the same thing in the USING clause.

For example, one query might look like this:

insert into taby (cols)
select 'x' from tab1 where field1=:1 and field2=:2 and field4=:3;

EXECUTE IMMEDIATE query USING field1, field2, field4;

And another query:

insert into taby (cols)
select 'x' from tab1 where field1=:1 and field5=:2;

EXECUTE IMMEDIATE query USING field1, field5;

Is it possible to pass a variable of some sort to USING instead of hardcoding field names? Otherwise I'd have to write an execute for every combination, which is impractical.

I'm using Oracle 8.1.7, and would like to use native dynamic SQL if possible. It looks doable with the DBMS_SQL package, but we've had some performance problems with that.

Any advice would be appreciated. Thanks.

Jim. Received on Tue Feb 20 2001 - 11:35:07 CST