Re: Dynamic SQL Reality Check

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 -- Received on Sun Jan 14 2007 - 11:07:04 CST