Deleting the records based on the condition

Shell Programming and Scripting
1

Hi,

Can any one help me, in deleting the records from the database table based on the following condition:
script should take a configurable parameter as input. The input is nothing but �no. of years�.
For example, if I enter 2 as input parameter, then the 2 year old records should get deleted. In the same way if i enter 3 as a parameter then 3 years old records should be deleted and so on...

its a great help,

thanks,
zxcjggu.

2

What kind of database?
What is the record layout?
What field is being matched against your input parameter?
and on and on....

3

Hi joeyg,
Please find the below details::

Testpurging.sh 2
Response::

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> DELETE FROM Testpurging TAI WHERE TAI.YEAR in ( select TAI1.YEAR from Testpurging MAX(ENTRY_DATE) < (select add_months(SYSDATE,-2) from dual))
                                                                                     *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>
Commit complete.

Request

#!/bin/ksh
USAGE_STRING="USAGE $0 [Purge_criteria]"
if [ "`whoami`" != "mqm" ]
        then
        echo "SORRY you need to be user 'mqm'. Only 'mqm' has required permissions."
        return 1
fi
##
if [ -z "${1}" ]
        then
        echo "No purge criteria was specified. Purge Criteria is the number-of-months."
        echo "${USAGE_STRING}"
        return 1
fi
export ORACLE_HOME=/u001/oracle/product/10.2.0
export ORACLE_BASE=/u001/oracle
export ORACLE_DOC=/u001/oracle/doc
export ORACLE_TERM=vt100
export LD_LIBRARY_PATH=/u001/oracle/product/10.2.0/lib32:/usr/dt/lib
export PATH=${PATH}:${ORACLE_HOME}/bin
User=mqiuedev01
Pass=mqiuedev01
DB=mwdbd1
QMgr=PBD1MWD1
sqlplus ${User}/${Pass}@${DB} << EOF
spool /mw/${QMgr}/log/Testpurging.log.`date +"%Y%m%d%H%M%S"`;
DELETE FROM Testpurging TAI WHERE TAI.YEAR in ( select TAI1.YEAR from Testpurging MAX(ENTRY_DATE) < (select add_months(SYSDATE,-${1}) from dual)) ;
commit;
spool off;
exit
EOF