0

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;
/
2
  • what error's are you getting?
    – kevinskio
    Jun 24, 2019 at 17:37
  • In cursor select statement, instead of region though for loop i used i.rgn and for the column (code), i used as i.cd = 'XY'; But it's not accepting and thrown the error as table or view does not exists. I feel that, the cursor is not accepting dynamic variable and it's treating that as a line. My doubt is how to handle dynamically this, which method i can use to achieve it.
    – Naveen
    Jun 24, 2019 at 17:46

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.