Re: Dynamic SQL Reality Check

From: William Robertson <williamr2019_at_googlemail.com>
Date: 16 Jan 2007 02:25:10 -0800
Message-ID: <1168943109.065866.294490@v45g2000cwv.googlegroups.com>

Mark D Powell wrote:
> William Robertson wrote:
> > 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 ;)\

>
>

> If the only difference is some of the SQL that is going to be
> dynamically executed is the value of a constant in the statment then
> look up the "using" clause of execute immediate.

>

> sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
> EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

>

> sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
> EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

>

> Examples from pl/sql manual for version 9.2. Using this form of the
> execute immediate will reduce the associated latching required due to
> the reduction in parsing steps required to execute this form from the
> same SQL submitted with constants:

>

> select * from emp where empno = 'XXXX' followed by
> select * from emp where empno = 'YYY'

>
> HTH -- Mark D Powell --

I'm still intrigued about why any of this is a bad CRT trip though. Perhaps he thought "native dynamic SQL" meant DBMS_SQL. I guess we'll never know... Received on Tue Jan 16 2007 - 04:25:10 CST