Getting Invalid Identifier error while passing the rowid to a ref cursor for data deletion

Question

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;

user_266304 · Answer

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!).

user_9097906 · Answer

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>

Getting Invalid Identifier error while passing the rowid to a ref cursor for data deletion

2 Answers