Re: Does this dynamic SQL/bind variables make sense?

From: Svend Jensen <Svend_at_OracleCare.Com>
Date: Thu, 04 Jul 2002 21:54:05 +0200
Message-ID: <3D24A7DD.2040303@OracleCare.Com>

kopek wrote:

> Does this piece of code from the ORACLE doc make sense to you? Why use
> dynamic SQL when a static one like
>
> DELETE FROM dept WHERE deptno=to_char (my_deptno);
>
> would suffice? I can understand if ORACLE uses this piece of code to
> demonstrate the virtue of using bind variables. But the dynamic SQL
> seems unnecessary. Am I missing something?
>
>
> ****************
> For example, the following native dynamic SQL code does not use bind
> variables:
>
> CREATE OR REPLACE PROCEDURE del_dept (
> my_deptno dept.deptno%TYPE) IS
> BEGIN
> EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char
> (my_deptno);
> END;
> /
> For each distinct my_deptno variable, a new cursor is created, which
> can cause
> resource contention and poor performance. Instead, bind my_deptno as a
> bind
> variable, as in the following example:
>
> CREATE OR REPLACE PROCEDURE del_dept (
> my_deptno dept.deptno%TYPE) IS
> BEGIN
> EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING
> my_deptno;
> END;
> /
> ********************
>

The setting of init.ora parameter cursor_sharing can change things. NDS does a hard parse. I think version 2 might get away with a soft parse on second run but a real cursor reuse cant take place. Optimal reuse is like
parse

    loop

       bind
       execute

    end loop
and that is not possible with NDS

/Svend Jensen Received on Thu Jul 04 2002 - 14:54:05 CDT