Re: Execute Immediate and updates

Is there a similar technique to handle query statement? We have a need to write a query which the 'where clause' may contain 1, 2, or more field.

We would like to write a stored procedure so the application will invoke it passing in the necessary parameters.

How can we take advantage of dynamic SQL in this case.

Thanks.

        Wing

In article <hcth6s86i5iqsc87as5evjlv1ffvl660sv_at_4ax.com>, tkyte_at_us.oracle.com says...
>
>A copy of this was sent to Chris Weiss <weisschr_at_home.com>
>(if that email address didn't require changing)
>On Tue, 28 Dec 1999 16:09:49 GMT, you wrote:
>
>>Hi,
>>
>>We are building Oracle 8i application from the ground up. We are using
>>a PL/SQL stored procedure wrapper for all transactions to the
>>applications database to control consistency, security, etc. Our
>>internal benchmarks show that stored procedures beat straight SQL
>>submitted by an external process for performance.
>>
>>Our wrapper model works in a very straight forward manner for inserts
>>and deletes, but we are having trouble coming up with a manageable model
>>for updates. Since there are 2^n - 1 possible update statements, a
>>parameter driven decision tree seems out of the question. Even
>>restricting updates to non-primary key fields does not provide enough of
>>a simplification. Hence, I see no other option than to use dynamic SQL
>>using the EXECUTE IMMEDIATE command in 8i.
>>
>>Unfortumately, EXECUTE IMMEDIATE statements seem to operate
>>approximately 2x slower than static SQL. Both forms of the queries were
>>executed in stored proceures.
>>
>>Does anyone have any tips or suggests for how to improve the performance
>>of EXECUTE IMMEDIATE statements? We are hitting the indexs, etc., but
>>the performance hit is substantial.
>>
>
>how are you doing the execute immediates? you are probably NOT using bind
>variables (else you are back to the 2^n problem again). by not using bind
>variables -- you are spending perhaps as much as much as 90% of your execution
>time parsing queries -- not executing them. I'm guessing your update looks
>like:
>
> string := 'update emp set ename = ''KING'' where empno = 1234';
> execute immediate string;
>
>and not like:
>
> string := 'update emp set ename = :1 where empno = :2';
> execute immediate string using p_ename, p_empno;
>
>
>
>You could try using DECODE to use static sql when possible. For example, take
>the DEPT table. You do not permit updates to the primary key and could write
a
>procedure like:
>
>create or replace procedure update_dept
>( p_deptno in dept.deptno%type,
> p_dname in dept.dname%type default NULL,
> p_loc in dept.loc%type default NULL )
>as
>begin
> update dept
> set dname = nvl( p_dname, dname ),
> loc = nvl( p_loc, loc )
> where deptno = p_deptno;
>end;
>
>
>That will update the dept row and will only update th dname column to a new
>value IF p_dname is not null. Likewise with loc. Now I can call:
>
>tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
>---------- -------------- -------------
> 10 ACCOUNTING NEW YORK
>
>tkyte_at_8.0>
>tkyte_at_8.0> exec update_dept( p_deptno => 10, p_dname => 'Something' );
>
>PL/SQL procedure successfully completed.
>
>tkyte_at_8.0> exec update_dept( p_deptno => 10, p_loc => 'SomeWhere' );
>
>PL/SQL procedure successfully completed.
>
>tkyte_at_8.0>
>tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
>---------- -------------- -------------
> 10 Something SomeWhere
>
>tkyte_at_8.0>
>tkyte_at_8.0> exec update_dept( p_deptno => 10, p_dname => 'X', p_loc => 'Y' );
>
>PL/SQL procedure successfully completed.
>
>tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
>---------- -------------- -------------
> 10 X Y
>
>
>
>will something like that work.
>
>>Our benchmarks were performed on an NT box, and our target platform will
>>be a Unix box. NT seems to do some things poorly when compared to Unix,
>>but I can't imagine this much of a performance difference.
>>
>>Thanks,
>>Chris Weiss
>>Intralect Solutions
>>chris_at_intralect.com
>
>
>--
>See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
>Thomas Kyte tkyte_at_us.oracle.com
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Jan 12 2000 - 10:51:14 CST