You might want a procedure to delete a single day's data for some other reason, but it won't help this job. Stick with the solution you got yesterday.
If you need a statement that will only DELETE rows if it is run on the 1st Saturday of a month, then you can add conditions to your DELETE statement, like this:
DELETE FROM audit_logs1
WHERE created < ADD_MONTHS (SYSDATE, -2)
AND TO_CHAR (created, 'DY') != 'SUN'
AND TO_CHAR (created, 'DD') != '01';
AND TO_CHAR (SYSDATE, 'DY') = 'SAT' -- *** NEW ***
AND TO_CHAR (SYSDATE, 'DD') <= '07' -- *** NEW ***
No PL/SQL is required, but if you have some reason for using PL/SQL, then the DELETE statement above will work just as well in PL/SQL.
As mentioned above, if you're using Oracle Enterprise Edition, you might consider partitioning the table by day, and dropping the unwanted partitions.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), a couple of run dates, and the results you want from that data (that is, what's left in the table) for each of the run dates.
Point out where the statement above is getting the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.
Always say which version of Oracle you're using (e.g., 22.214.171.124.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Oracle version is 126.96.36.199
thanks frank... I will try to provide more details as mentioned in this and more comments.
mentioned delete statement will works but since pkg is already using delete_logs() procedure my senior developer don't want to have another delete statement, so suggested to get dates to delete and feed into delete_logs() procedure.
so I'm trying to use call_delete() procedure to query and loop everydate into delete_logs().
Sorry for pitching in, (More specifically as this call is for Gurus).. yeah this query will delete the records, however with this kind of table & data, why don't you consider partitioning and then partition purging, which can be much more efficient in every sense.
Precisely what I was going to suggest.
create table audit_logs_partitioned partition by range ( created ) interval ( numtoyminterval(1,'MONTH') ) (partition p0 values less than (to_date('20130701','yyyymmdd'))) as select * from audit_logs; -- setup PKs, indexes, grants, etc. rename audit_logs to audit_logs old; rename audit_logs_partitioned to audit_logs; -- change synonyms make sure point to audit_logs
Then all you need to do is cleanup old partitions when you want to purge old data:
alter table audit_logs drop partition '<partition name>';
That'll go very quickly.
oh, by day? yeah, same difference, just alter the interval to numtodsinterval(1,'day') if you get that many .. wow lot of audit lol
My vote is definitely still partitions - saves alot of headache in this kind of scenario