I am trying to get a proc to work. Any help appreciated.
The plan is to be able to run it like so:
exec MY_PURGE_RECYCLEBIN(‘30’) -- To take a parameter e.g 30 for the last 30 days to purge
create or replace
procedure MY_PURGE_RECYCLEBIN (days IN number)
IS
BEGIN
FOR r1 IN (select 'purge table '|| owner ||'.'|| object_name ||'') AS v_sql
from dba_recyclebin
where can_purge = 'YES'
and type = 'TABLE'
and owner = 'USER01'
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS') < sysdate - days);
LOOP
EXECUTE IMMEDIATE r1.v_sql;
END LOOP;
END;
/
Cheers