dbms_sql ORA-29471: DBMS_SQL 访问被拒绝

come from  :  http://www.itpub.net/thread-1939425-1-1.html

thanks  the writer.

原始出处：
http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

我执行了下列语句:

CREATE TABLE plch_greens
(
   id   INTEGER PRIMARY KEY,
   nm   VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO plch_greens
        VALUES (1, 'Broccoli');

   INSERT INTO plch_greens
        VALUES (2, 'Kale');

   COMMIT;
END;
/

哪些选项在执行之后会导致下列文本被输出?

Updated Broccoli
Updated Kale

注：每个选项的循环体内部的代码大部分是相同的，请看注释说明。

(A)
DECLARE
   l_cursor     INTEGER := DBMS_SQL.open_cursor;
   l_feedback   INTEGER;
BEGIN
   FOR rec IN (  SELECT *
                   FROM plch_greens
               ORDER BY id)
   LOOP
      /* 相同代码起始点... */
      DBMS_SQL.parse (
         l_cursor,
         'update plch_greens set nm = upper (nm) where id = :my_id',
         DBMS_SQL.native);
      DBMS_SQL.bind_variable (l_cursor, 'my_id', rec.id);
      DBMS_OUTPUT.put_line ('Updated ' || rec.nm);
      l_feedback := DBMS_SQL.execute (l_cursor);
      /* ...相同代码结束点 */

   END LOOP;

   DBMS_SQL.close_cursor (l_cursor);
END;
/

A: 我打开游标一次，在循环中重复使用它，在循环结束后关闭了它。没有必要反复打开关闭，正如这个逻辑中所体现的。

然而它还有改善的余地。既然SQL语句本身没有改变（只有绑定变量改变了），对PARSE的调用也只需要在循环外执行一次，然后只需绑定一个新值。

当然，对于这么简单的动态SQL场景，我们还可以更进一步，把DBMS_SQL改成EXECUTE IMMEDIATE。

B)
DECLARE
   l_cursor     INTEGER;
   l_feedback   INTEGER;
BEGIN
   FOR rec IN (  SELECT *
                   FROM plch_greens
               ORDER BY id)
   LOOP
      l_cursor := DBMS_SQL.open_cursor;

      /* 相同代码起始点... */
      DBMS_SQL.parse (
         l_cursor,
         'update plch_greens set nm = upper (nm) where id = :my_id',
         DBMS_SQL.native);
      DBMS_SQL.bind_variable (l_cursor, 'my_id', rec.id);
      DBMS_OUTPUT.put_line ('Updated ' || rec.nm);
      l_feedback := DBMS_SQL.execute (l_cursor);
      /* ...相同代码结束点 */

      DBMS_SQL.close_cursor (l_cursor);
   END LOOP;
END;
/

没有发生错误，显示的信息也正确，但是我做了很多不必要的事情，在循环体中不断打开和关闭游标。

(C)
DECLARE
   l_cursor     INTEGER;
   l_feedback   INTEGER;
BEGIN
   FOR rec IN (  SELECT *
                   FROM plch_greens
               ORDER BY id)
   LOOP
      l_cursor := DBMS_SQL.open_cursor;

      /* 相同代码起始点... */
      DBMS_SQL.parse (
         l_cursor,
         'update plch_greens set nm = upper (nm) where id = :my_id',
         DBMS_SQL.native);
      DBMS_SQL.bind_variable (l_cursor, 'my_id', rec.id);
      DBMS_OUTPUT.put_line ('Updated ' || rec.nm);
      l_feedback := DBMS_SQL.execute (l_cursor);
      /* ...相同代码结束点 */
   END LOOP;
END;
/

仍然可以看到正确结果，但是更加糟糕了。我不断打开新游标，但是没有关闭，这意味着它会在我的会话中保持打开状态，直到会话结束为止。

(D)
DECLARE
   l_cursor     INTEGER := DBMS_SQL.open_cursor;
   l_feedback   INTEGER;
BEGIN
   FOR rec IN (  SELECT *
                   FROM plch_greens
               ORDER BY id)
   LOOP
      /* 相同代码起始点... */
      DBMS_SQL.parse (
         l_cursor,
         'update plch_greens set nm = upper (nm) where id = :my_id',
         DBMS_SQL.native);
      DBMS_SQL.bind_variable (l_cursor, 'my_id', rec.id);
      DBMS_OUTPUT.put_line ('Updated ' || rec.nm);
      l_feedback := DBMS_SQL.execute (l_cursor);
      /* ...相同代码结束点 */

      DBMS_SQL.close_cursor (l_cursor);
   END LOOP;
END;

D: 这个选项会报错：
"ORA-29471: DBMS_SQL access denied".
我打开游标一次，但是在循环中关闭了它，所以第二次我试图解析的时候游标已经无效了。