CODEWITHSUNDEEP
sqloracle-databaseunix
Prashanth
Prashanth

Reputation: 95

Shell script to delete records from Oracle DB table

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.

Upvotes: 0

Views: 5525

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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).

Upvotes: 0

Related Questions