Shell Programming and Scripting

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.

#!/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