Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t42 (some_col) as select 42 from dual;
1 rows affected

select * from t42;


SOME_COL


42


create table test_tab(schema, table_name, status) as select user, 'T42', cast(null as varchar2(1)) from dual;
1 rows affected

select * from test_tab


SCHEMA
TABLE_NAME
STATUS


FIDDLE_OJYEQSAHGHKVDBYTONSP
T42
null


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 FROM '
                         || tab_names (id).SCHEMA
                         || '.'
                         || tab_names (id).TABLE_NAME
                         || ' a WHERE a.ROWID = :r'
                         USING tab_row (i);

                      COMMIT;
                      V_STATUS := 'Y';
                   EXCEPTION
                      WHEN OTHERS
                      THEN
                         l_sql_errm := 'Inside Delete Block: ' || SQLERRM;
dbms_output.put_line(l_sql_errm);
                         V_STATUS := 'N';
                         ROLLBACK;
                   END;
                END LOOP;
             END LOOP;
          END LOOP;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_sql_errm := 'Inside Others Block: ' || SQLERRM;
dbms_output.put_line(l_sql_errm);
          V_STATUS := 'N';
          ROLLBACK;
    END;
/
select * from user_errors
begin
  dbms_output.enable;
  process_test(user, 't42');
end;
/
1 rows affected

select * from t42;