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

As far as I know .. you are correct. The delete from ... in the pl/sql would have done the job using the bind
variable (as well as the dynamic sql using bind variables). However, examples should be concise and I guess thats what Oracle intented to do.

Anurag

"kopek" <matezuka_at_yahoo.com> wrote in message news:f7cb1d69.0207031046.7077369e_at_posting.google.com...
> 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;
> /
> ********************
Received on Wed Jul 03 2002 - 14:21:07 CDT