Re: Building WHERE clause in Native Dynamic SQL

reynolds_at_acadia.ee.vill.edu (Jim Reynolds) wrote in <96u9sb$4e2_at_acadia.ee.vill.edu>:

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

Can one EXECUTE IMMEDIATE an 'BEGIN EXECUTE IMMEDIATE ...; END;' statement? ;^)

<snip>

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

If the maximum number of parameters is limited, one idea that may work: build your WHERE clause as before, but for each unused procedure parameter add a meaningless AND'ed condition which always evaluates to true, e.g. '... AND nvl(:p<n>, 0) IS NOT NULL'. These conditions need not specify any table columns and therefore shouldn't affect optimizer query plan selection.

Hope this helps,

Gerard

-- 
Gerard Averill
gaverill<at>chsra<dot>wisc<dot>edu