How to pass table name and column name dynamically in Cursor

oracleplsql

I would like to delete millions of records from lots many tables. In the below code, i'm trying to select a table with some filter condition and trying to delete using its row id. In the below, for the cursor test_cusr i'm selecting from (region) table and column (code) = 'XY'. I would like to pass the table, column and the value in dynamically. I tried with for loop and ref cursor but getting some error. Can any one help me in this.

DECLARE
CURSOR test_cur
  IS
    SELECT rowid row_id FROM region WHERE code = 'XY';
TYPE test_aat
IS
  TABLE OF ROWID INDEX BY PLS_INTEGER;
  l_test test_aat;
  limit_in NUMBER := 10000;
BEGIN
  OPEN test_cur;
  LOOP
    FETCH test_cur BULK COLLECT INTO l_test LIMIT limit_in;
    IF l_test.COUNT = 0 THEN
      EXIT;
    END IF;
    FORALL indx IN l_test.FIRST .. l_test.LAST
    DELETE FROM region WHERE rowid = l_test(indx);
    COMMIT;
  END LOOP;
  IF test_cur%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE(test_cur%ROWCOUNT);
    CLOSE test_cur;
  END IF;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  IF test_cur%ISOPEN THEN
    CLOSE test_cur;
  END IF;
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Best Answer

If I were you, I'd put the values of the tables being deleted into an array, and then loop through that to do a single delete on that table. E.g.:

DECLARE
  TYPE tab_col_rec IS RECORD (owner all_tables.owner%TYPE,
                              table_name all_tables.table_name%TYPE,
                              column_name all_tab_columns.column_name%TYPE,
                              predicate_value VARCHAR2(4000));

  TYPE tab_col_arr IS TABLE OF tab_col_rec INDEX BY PLS_INTEGER;

  v_arr tab_col_arr;
  v_sql CLOB;

  v_idx PLS_INTEGER;
BEGIN
  v_arr(1).owner := 'owner1';
  v_arr(1).table_name := 'region';
  v_arr(1).column_name := 'code';
  v_arr(1).predicate_value := 'XY';

  v_idx := v_arr.first;

  WHILE v_idx IS NOT NULL
  LOOP
    v_sql := 'DELETE FROM '|| dbms_assert.qualified_sql_name(v_arr(v_idx).owner || '.' || v_arr(v_idx).table_name) || CHR(10) ||
             'WHERE  ' || dbms_assert.simple_sql_name(v_arr(v_idx).column_name) || ' = :predicate_value';

    --dbms_output.put_line(v_sql);  -- useful for checking the sql to be run

    EXECUTE IMMEDIATE v_sql USING v_arr(v_idx).predicate_value;

    COMMIT;

    v_idx := v_arr.next(v_idx);
  END LOOP;
END;
/

I've put what I thought was relevant into the array record type, but you may need more or fewer fields as you see fit.

I've also used a single DELETE statement, since that will be faster than committing every N rows. If you don't have enough UNDO tablespace to do a single delete, I recommend you get it extended, rather than having to commit periodically.

Related Question