Re: Dynamic SQL Reality Check

Michael42 wrote:
> Hello,
>
> In getting deeper with PL/SQL using Oracle 10g R2 I have a need to
> execute SQL statements that are not static. In wading through the
> internet common Oracle docs sites it seems I must use "Oracle Native
> Dynamic SQL".
>
> Please tell me I am wrong. In comparison to the years of using SQL
> with other SQL engines this is like a real bad LCD trip ...R-E-A-L
> B-A-D. :-)
>
> At the crux, I am using a package and have a function I wish to pass a
> completely dynamic (yet simple) SQL string where the only thing that is
> a constant is the table name.
>
> A psuedo code example function specs:
>
> FUNCTION qryTable (
> vTable IN varchar2,
> vFields IN varchar2,
> vWhere IN varchar2
> ) RETURN varchar2 IS
>
> sSQL := 'SELECT ' || vFields || ' FROM ' || vTable || ' WHERE ' ||
> vWhere ;
> -- Create a cursor from sSQL next ...
>
>
> What is the easiest way to do this that will work in the structure of a
> Package via a function call?
>

Often in a case like there there are really only a certain number of combinations of columns that will be selected along with other columns that will be used in the where clause.

It can be tedious but often a better solution than going dynamic is to check within the procedure "which" of the cases are used and have multiple cursor's defined in the plsql routine of which only one of them matches and will be used. In your case you could build in some parsing logic and tear apart the columns and where clause parameters and see which cursor to use. Received on Tue Jan 16 2007 - 04:34:55 CST