Does this dynamic SQL/bind variables make sense?

From: kopek <matezuka_at_yahoo.com>
Date: 3 Jul 2002 11:46:23 -0700
Message-ID: <f7cb1d69.0207031046.7077369e@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 - 13:46:23 CDT