Deleting from multiple tables cleanup script SQL

Question

Answer in the bottom, found out eventually. thx for all the contributions.

I have to make a script which i will make into a stored procedure which will then run every few months. It will need to go into some specific tables and delete old records. I thought this would be pretty straight forward, but I keep running into the same problem.

if i try to run the code i get and invalid identifier error, it seems to be a problem with the date field, where oracle defaults to using letters for months, i have tryed various date formating, but i doesn't seem to work.

I declare which date i want to delete from, and then string together an execute immediate statement, which will loop through the tabletoclean table which holds the table name, and datefield name of the tables that i will do the cleaning on.

The error code is ORA-00904: "APR" invalid intendifier I guess its because it tries to use APR - APRIL instead of 04. but i don't know. As far as i can see none of the dtl_fields use the Month name date formating.

Declare
dtldate date := to_date(add_months( to_date(sysdate), -24 ), 'dd-mm-yy');

Begin
  for tbl IN (Select * from tbltoclean)
loop 

 execute immediate 'Delete from '||tbl.tbl_name || ' where ' || tbl.dtl_field ||' < ' || dtldate;

 DBMS_OUTPUT.PUT_LINE ('Deleted from '|| to_char(tbl.tbl_name));
 end loop;
 end;

if i run the execute immediate string in dbms.output i return string like Select * from mytable where datefield < 30-APR-16

Confirming that the problem properly lies with the date formating.

EDIT / ANSWER The problem was withthe dtldate variable. it posted dates to the execute immediate as follows.

Select * from mytable where datefield < 30-APR-16

This didn't work but

Select * from mytable where datefield < '30-APR-16'

would work, so i edited the string the following code works.

Declare
dtldate date := add_months( to_date(sysdate), -24 );

Begin
  for tbl IN (Select * from tbltoclean)
loop 

 execute immediate 'Select null from '||tbl.tbl_name || ' where ' || tbl.dtl_field ||' < ' || 'to_date('''||dtldate||''')'; --dtldate have escaped ''

 DBMS_OUTPUT.PUT_LINE ('Deleted from '|| to_char(tbl.tbl_name));
 end loop;
 end;

Answer 1

SYSDATE is a function that returns DATE datatype; there's no use in converting it to a date (with the TO_DATE) once again.

So, this might do what you want:

declare
  dtldate date := add_months(trunc(sysdate), -24);
begin
  for tbl in (select * from tbltoclean)
  loop 
    execute immediate 'Delete from '||tbl.tbl_name || 
                      ' where ' || tbl.dtl_field ||' < DATE ''' || to_char(dtldate, 'YYYY-MM-DD') || '''';

    dbms_output.put_line ('Deleted from '|| to_char(tbl.tbl_name));
  end loop;
end;

Answer 2

This is the correct way to treat DATES:

DECLARE
    --
    DTLDATE DATE;
    --
BEGIN
    --
    DTLDATE := TRUNC(SYSDATE) -- 30/04/2018 14:36 -> 30/04/2018
    --
    DTLDATE := ADD_MONTHS(DTLDATE, -24) -- 30/04/2018 -> 30/04/2016
    --
    FOR TBL IN (SELECT * FROM TBLTOCLEAN) LOOP 
        --
        EXECUTE IMMEDIATE 'DELETE FROM '||TBL.TBL_NAME || 
                          ' WHERE ' || TBL.DTL_FIELD ||' < :DTLDATE' 
          USING DTLDATE;
        DBMS_OUTPUT.PUT_LINE ('DELETED FROM '|| TBL.TBL_NAME);
        --
    END LOOP;
    --
 END;