SQL & PL/SQL

This isn’t a question, I just wanted to share a solution to a problem I have encountered several times.

e.g. You have ‘n’ number of variables, of which any combination could be used in a SQL string. But it’s difficult to execute the SQL string with USING without an unpleasant CASE statement.

Solution: Reference all ‘n’ bind variables in the FROM clause of the SQL in an in-line-view. And specify all variables in the USING Clause.
When building the SQL String, specify the view.variable rather than directly referencing bind variables
This eliminates the requirement to check which variables have been populated.

-----
<h6>
t_select := ‘SELECT tab.cola, tab.colb’;

t_from := ‘FROM (SELECT :1 cola, :2 colb, :3 colc, :4 cold, :5 cole) parms, tablea tab’;

t_where := ‘WHERE tab.cola = parms.cola’;


OPEN c t_select||t_from||t_where USING v1, v2, v3, v4, v5;
</h6>
-----

I hope this is useful to someone...

PeteD