I have a situation where I need to run a shell script every 30 days to delete records from Oracle database table.
The table has a column 'updated_date'. I need to write a query to delete records where 'updated_date' less than current date minus 30 days.
In Java I can do it comfortably. How to calculate the dynamic date and pass it to SQL query in Unix shell script.
Can someone please help me.
You could use the delete statement suggested by Littlefoot to utilize the system date from within the database.
But, since you asked -
How to calculate the dynamic date and pass it to SQL query in Unix shell script,
This is how you can do it.
First, use the date command to get the current date in 'yyyy-mm-dd' format.
export dt=$(date +%Y-%m-%d)
You may then use the date variable within your shell script in sqlplus.
sqlplus -s userid/passwd@db_server<<EOF
delete from yourtable
where updated_date < DATE '${dt}' - 30;
commit;
exit
EOF
That would be something like this:
delete from your_table
where updated_date < trunc(sysdate) - 30;
SYSDATE is a function that returns current date (and time - that's why I used TRUNC function which will remove time component). It also means that you don't have to calculate date and pass it to SQL query - Oracle know it itself.
Though, note that SYSDATE shows database server date, so - if you work on different time zones, you might need to consider that fact.
Also, "minus 30 days": is it always 30 days, or did you actually mean "minus 1 month"? If so, you'd change the condition to
where updated_date < add_months(trunc(sysdate), -1)
and it'll take care about number of days in a month (30, 31; including February as well as leap years).
