First of all I would like to say Hi to everyone here.
Second thing:
I have a procedure which I want to delete several records from several tables. Those tables from were I want to delete rows have names stored in another table in database along with the column name that is used for condition.
Main table DB_CLEANUP_PARAMETERS (the one that stores tables names for other) is like this:
table_name varchar2(30)
date_field_name varchar2(30)
retention_days number
I wrote the following procedure to do the things
Code:
CREATE OR REPLACE PROCEDURE DB_CLEANUP_PROCEDURE_2 IS
i INTEGER;
TYPE j IS TABLE OF ROWID;
row_id j;
v_table_name VARCHAR2(30);
v_column_name VARCHAR2(30);
v_retention_days NUMBER(9);
v_chunk_size INTEGER := 1000;
BEGIN
FOR rec IN (SELECT dcp.table_name, dcp.date_field_name, dcp.retention_days
FROM db_cleanup_parameters dcp) LOOP
v_table_name := rec.table_name;
v_column_name := rec.date_field_name;
v_retention_days := rec.retention_days;
EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || v_table_name || ' WHERE ' ||
v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
BULK COLLECT INTO row_id;
FORALL i IN 1 .. row_id.COUNT()
EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
COMMIT;
END DB_CLEANUP_PROCEDURE_2;
Main problem is that I get the following error:
Code:
Compilation errors for PROCEDURE ONAIR.DB_CLEANUP_PROCEDURE_2
Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 589; Unknown expression Expr = 283.; DB_CLEANUP_PROCEDURE_2__ONAIR__P__287291[20, 5]]
Line: 20
Text: EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
Purpose of this procedure is to delete rows from the tables stored in DB_CLEANUP_PARAMETER table, but not one by one. Delete rows in chunks.
What is wrong ? can I get some help ?
Thanks