How to pass table name and column name dynamically in Cursor

Question

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;
/

• kevinskio: what error's are you getting?
• Naveen: 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.

Answer 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.