Re: Dynamic SQL Reality Check

On Jan 14, 2:59 pm, "Michael42" <melliot..._at_yahoo.com> 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?
>
> Thanks for your advice,
>
> Michael

EXECUTE IMMEDIATE sSQL;

although you would still have to code something to handle the result set, perhaps

EXECUTE IMMEDIATE sSQL INTO v_somevariable;

Or you could make it a dynamic cursor by declaring a cursor variable (or adding an OUT parameter) of type SYS_REFCURSOR, and then opening it:

OPEN c_resultset FOR sSQL;

Both these methods are known as Native Dynamic SQL.

I don't really see where the bad LCD trip (liquid crystal display abuse?) comes in. It is hard to see how they could have made it any simpler. I suppose the keyword "IMMEDIATE" is redundant since there is no non-immediate option. Is that what you mean?

If you want a real bad LED trip, you should read up on SQL Injection ;) Received on Sun Jan 14 2007 - 10:11:14 CST