当前位置： 文江博客话题详情

将ROWID传递给REF光标以获取数据删除时，获得无效的标识符错误

发布于 2025-01-26 19:10:24 字数 2467 浏览 4 评论 0原文

执行下面给出的代码后，我会遇到以下错误：

“ error1：内部删除块： ora-00904 ：“ ajappdameaad6hpaaf”：无效 标识符“

它不识别删除语句中的ROWID值，该块正在进入撤销器内部并为表格获取ROWID，然后无法执行同一rowID的删除语句，请帮助我如何使用此操作， rowid。

CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2) AS
       l_sql        VARCHAR2 (2000);
    
       CURSOR c_x
       IS
          SELECT *
            FROM TEST_TAB
           WHERE     UPPER (SCHEMA) = UPPER (V_SCHEMA)
                 AND UPPER (TABLE_NAME) = UPPER (V_TAB)
                 AND STATUS IS NULL;
    
       TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
       tab_names    tab_names_type;
       l_cursor     SYS_REFCURSOR;
    
       TYPE c_1 IS TABLE OF UROWID;
       tab_row      c_1;
    
       l_sql_errm   VARCHAR2 (2000);
       V_STATUS     VARCHAR2 (1) := 'N';
BEGIN
       OPEN c_x;
    
       LOOP
            FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
          EXIT WHEN tab_names.COUNT = 0;
    
          FOR id IN tab_names.FIRST .. tab_names.LAST
          LOOP
             l_sql :=
                   'SELECT a.ROWID FROM '
                || tab_names (id).SCHEMA
                || '.'
                || tab_names (id).TABLE_NAME
                || ' a';
    
             OPEN l_cursor FOR l_sql;
    
             LOOP
                  FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;    
                EXIT WHEN tab_row.COUNT = 0;
    
                FOR i IN tab_row.FIRST .. tab_row.LAST
                LOOP
                   BEGIN
                      EXECUTE IMMEDIATE
                            'DELETE '
                         || tab_names (id).SCHEMA
                         || '.'
                         || tab_names (id).TABLE_NAME
                         || ' a WHERE a.ROWID = '
                         || tab_row (i);
    
                      COMMIT;
                      V_STATUS := 'Y';
                   EXCEPTION
                      WHEN OTHERS
                      THEN
                         l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
                         V_STATUS := 'N';
                         ROLLBACK;
                   END;
                END LOOP;
             END LOOP;
          END LOOP;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_sql_errm := 'Inside Others Block: ' || SQLERRM;
          V_STATUS := 'N';
          ROLLBACK;
END;
原文

I am getting the below error after executing the code given below :

"Error1: Inside Delete Block: ORA-00904: "AJAPPDAMEAAD6HPAAF": invalid
identifier"

Its not identifying the rowid value in the delete statement, the block is getting inside the refcursor and fetching the rowid for the table and then its not able to execute the delete statement for the same rowid, Please help me how to achieve this using rowid. Thanks In Advance...

CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2) AS
       l_sql        VARCHAR2 (2000);
    
       CURSOR c_x
       IS
          SELECT *
            FROM TEST_TAB
           WHERE     UPPER (SCHEMA) = UPPER (V_SCHEMA)
                 AND UPPER (TABLE_NAME) = UPPER (V_TAB)
                 AND STATUS IS NULL;
    
       TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
       tab_names    tab_names_type;
       l_cursor     SYS_REFCURSOR;
    
       TYPE c_1 IS TABLE OF UROWID;
       tab_row      c_1;
    
       l_sql_errm   VARCHAR2 (2000);
       V_STATUS     VARCHAR2 (1) := 'N';
BEGIN
       OPEN c_x;
    
       LOOP
            FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
          EXIT WHEN tab_names.COUNT = 0;
    
          FOR id IN tab_names.FIRST .. tab_names.LAST
          LOOP
             l_sql :=
                   'SELECT a.ROWID FROM '
                || tab_names (id).SCHEMA
                || '.'
                || tab_names (id).TABLE_NAME
                || ' a';
    
             OPEN l_cursor FOR l_sql;
    
             LOOP
                  FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;    
                EXIT WHEN tab_row.COUNT = 0;
    
                FOR i IN tab_row.FIRST .. tab_row.LAST
                LOOP
                   BEGIN
                      EXECUTE IMMEDIATE
                            'DELETE '
                         || tab_names (id).SCHEMA
                         || '.'
                         || tab_names (id).TABLE_NAME
                         || ' a WHERE a.ROWID = '
                         || tab_row (i);
    
                      COMMIT;
                      V_STATUS := 'Y';
                   EXCEPTION
                      WHEN OTHERS
                      THEN
                         l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
                         V_STATUS := 'N';
                         ROLLBACK;
                   END;
                END LOOP;
             END LOOP;
          END LOOP;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_sql_errm := 'Inside Others Block: ' || SQLERRM;
          V_STATUS := 'N';
          ROLLBACK;
END;

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

暖树树初阳… 2025-02-02 19:10:24

您将您的rowID值作为字符串连接到删除语句中。如果要执行此操作，则至少需要引用它，然后更正确地通过 charterowid（）urowid value to a String）：

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = chartorowid('''
                     || tab_row (i)
                     || ''')';

最好使用绑定变量：

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = chartorowid(:r)'
                     USING tab_row (i);

或仅：

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = :r'
                     USING tab_row (i);

您的版本和我的版本，添加了调试，因此您实际上可以看到错误（根据您是否要继续进行；将其扔或记录；处理和交易控制表明您要继续进行，但是您仍然需要报告/可靠地记录问题 - 这意味着不使用dbms_output！）。

You are concatenating your rowid value into the delete statement as a string. If you're going to do that you need to at least quote it, and more correctly convert it via chartorowid() (though you're still implicitly converting your urowid value to a string):

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = chartorowid('''
                     || tab_row (i)
                     || ''')';

It would be better to use a bind variable:

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = chartorowid(:r)'
                     USING tab_row (i);

or just:

                  EXECUTE IMMEDIATE
                        'DELETE FROM '
                     || tab_names (id).SCHEMA
                     || '.'
                     || tab_names (id).TABLE_NAME
                     || ' a WHERE a.ROWID = :r'
                     USING tab_row (i);

db<>fiddles of your version and my version, with debugs added so you can actually see the error (it would be better to throw or log it, depending on whether you want to carry on; the handling and transaction control suggest you want to carry on, but you still need to report/record the problem reliably - which means not using dbms_output!).

回复 原文
白芷 2025-02-02 19:10:24

rowID是 string ，而不是号码。您必须将其包装到单个引号（chr（39））中：

              EXECUTE IMMEDIATE   'DELETE FROM '
                               || tab_names (id).SCHEMA
                               || '.'
                               || tab_names (id).TABLE_NAME
                               || ' a WHERE a.ROWID = '
                               || CHR (39)
                               || tab_row (i)
                               || CHR (39);

SQL> CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2)
  2  AS
  3     l_sql       VARCHAR2 (2000);
  4
  5     CURSOR c_x IS
  6        SELECT *
  7          FROM TEST_TAB
  8         WHERE     UPPER (SCHEMA) = UPPER (V_SCHEMA)
  9               AND UPPER (TABLE_NAME) = UPPER (V_TAB)
 10               AND STATUS IS NULL;
 11
 12     TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
 13
 14     tab_names   tab_names_type;
 15
 16
 17     l_cursor    SYS_REFCURSOR;
 18
 19     TYPE c_1 IS TABLE OF UROWID;
 20
 21     tab_row     c_1;
 22
 23     l_sql_errm  VARCHAR2 (2000);
 24     V_STATUS    VARCHAR2 (1) := 'N';
 25  BEGIN
 26     OPEN c_x;
 27
 28     LOOP
 29        FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
 30
 31        EXIT WHEN tab_names.COUNT = 0;
 32
 33        FOR id IN tab_names.FIRST .. tab_names.LAST
 34        LOOP
 35           l_sql :=
 36                 'SELECT a.ROWID FROM '
 37              || tab_names (id).SCHEMA
 38              || '.'
 39              || tab_names (id).TABLE_NAME
 40              || ' a';
 41
 42           OPEN l_cursor FOR l_sql;
 43
 44           LOOP
 45              FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;
 46
 47              EXIT WHEN tab_row.COUNT = 0;
 48
 49              FOR i IN tab_row.FIRST .. tab_row.LAST
 50              LOOP
 51                 BEGIN
 52                    EXECUTE IMMEDIATE   'DELETE FROM '
 53                                     || tab_names (id).SCHEMA
 54                                     || '.'
 55                                     || tab_names (id).TABLE_NAME
 56                                     || ' a WHERE a.ROWID = '
 57                                     || CHR (39)
 58                                     || tab_row (i)
 59                                     || CHR (39);
 60
 61                    COMMIT;
 62                    V_STATUS := 'Y';
 63                 EXCEPTION
 64                    WHEN OTHERS
 65                    THEN
 66                       l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
 67                       V_STATUS := 'N';
 68                       ROLLBACK;
 69                 END;
 70              END LOOP;
 71           END LOOP;
 72        END LOOP;
 73     END LOOP;
 74  EXCEPTION
 75     WHEN OTHERS
 76     THEN
 77        l_sql_errm := 'Inside Others Block: ' || SQLERRM;
 78        V_STATUS := 'N';
 79        ROLLBACK;
 80  END;
 81  /

Procedure created.

测试：

SQL> SELECT * FROM test_tab;

SCHEM TABLE_ S
----- ------ -
SCOTT MOVIES

SQL> SELECT title_id, title FROM movies;

  TITLE_ID TITLE
---------- ------------------------------
         1 Titanic

SQL> EXEC process_test('SCOTT', 'MOVIES');

PL/SQL procedure successfully completed.

SQL> SELECT title_id, title FROM movies;

no rows selected

SQL>

ROWID is a string, not a number. You have to enclose it into single quotes (chr(39)):

              EXECUTE IMMEDIATE   'DELETE FROM '
                               || tab_names (id).SCHEMA
                               || '.'
                               || tab_names (id).TABLE_NAME
                               || ' a WHERE a.ROWID = '
                               || CHR (39)
                               || tab_row (i)
                               || CHR (39);

SQL> CREATE OR REPLACE PROCEDURE PROCESS_TEST (V_SCHEMA VARCHAR2, V_TAB VARCHAR2)
  2  AS
  3     l_sql       VARCHAR2 (2000);
  4
  5     CURSOR c_x IS
  6        SELECT *
  7          FROM TEST_TAB
  8         WHERE     UPPER (SCHEMA) = UPPER (V_SCHEMA)
  9               AND UPPER (TABLE_NAME) = UPPER (V_TAB)
 10               AND STATUS IS NULL;
 11
 12     TYPE tab_names_type IS TABLE OF TEST_TAB%ROWTYPE;
 13
 14     tab_names   tab_names_type;
 15
 16
 17     l_cursor    SYS_REFCURSOR;
 18
 19     TYPE c_1 IS TABLE OF UROWID;
 20
 21     tab_row     c_1;
 22
 23     l_sql_errm  VARCHAR2 (2000);
 24     V_STATUS    VARCHAR2 (1) := 'N';
 25  BEGIN
 26     OPEN c_x;
 27
 28     LOOP
 29        FETCH c_x BULK COLLECT INTO tab_names LIMIT 1000;
 30
 31        EXIT WHEN tab_names.COUNT = 0;
 32
 33        FOR id IN tab_names.FIRST .. tab_names.LAST
 34        LOOP
 35           l_sql :=
 36                 'SELECT a.ROWID FROM '
 37              || tab_names (id).SCHEMA
 38              || '.'
 39              || tab_names (id).TABLE_NAME
 40              || ' a';
 41
 42           OPEN l_cursor FOR l_sql;
 43
 44           LOOP
 45              FETCH l_cursor BULK COLLECT INTO tab_row LIMIT 1000;
 46
 47              EXIT WHEN tab_row.COUNT = 0;
 48
 49              FOR i IN tab_row.FIRST .. tab_row.LAST
 50              LOOP
 51                 BEGIN
 52                    EXECUTE IMMEDIATE   'DELETE FROM '
 53                                     || tab_names (id).SCHEMA
 54                                     || '.'
 55                                     || tab_names (id).TABLE_NAME
 56                                     || ' a WHERE a.ROWID = '
 57                                     || CHR (39)
 58                                     || tab_row (i)
 59                                     || CHR (39);
 60
 61                    COMMIT;
 62                    V_STATUS := 'Y';
 63                 EXCEPTION
 64                    WHEN OTHERS
 65                    THEN
 66                       l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
 67                       V_STATUS := 'N';
 68                       ROLLBACK;
 69                 END;
 70              END LOOP;
 71           END LOOP;
 72        END LOOP;
 73     END LOOP;
 74  EXCEPTION
 75     WHEN OTHERS
 76     THEN
 77        l_sql_errm := 'Inside Others Block: ' || SQLERRM;
 78        V_STATUS := 'N';
 79        ROLLBACK;
 80  END;
 81  /

Procedure created.

Testing:

SQL> SELECT * FROM test_tab;

SCHEM TABLE_ S
----- ------ -
SCOTT MOVIES

SQL> SELECT title_id, title FROM movies;

  TITLE_ID TITLE
---------- ------------------------------
         1 Titanic

SQL> EXEC process_test('SCOTT', 'MOVIES');

PL/SQL procedure successfully completed.

SQL> SELECT title_id, title FROM movies;

no rows selected

SQL>
回复 原文
~没有更多了~

关于作者

深府石板幽径

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文