Hi all,

I've been assigned the task of modifying a script which reads in names of tables from a list file, exports a 30 days worth of data from these tables, then deletes the table. The list file will now contain a table name and a number next to it indicating how many days to archive. I need to make the script read in the table name, then the number of days to go back, if no number is specified, the default will be 30. Any idea how to start off? Here is some of the current script now:

echo "Start of archive_history.ksh"
TBL_HIST_LIST=$JOB_HOME/archiveHistTbl.list
TBLLIST=$JOB_HOME/archiveTbl.list
DATADIR=/tmp
TODAY=`date +%Y%m%d`

# Set path
PATH="${JOB_HOME}:$HOME/Dear/jobs:${PATH}"

# Set date to delete from
DELDATE=`shift_date.ksh $TODAY -$1`

# For each table, delete based on dt_last_upd_hist
for tbl in `cat $TBL_HIST_LIST`
do
$ORACLE_HOME/bin/exp $DBO_USER/$DB_PASSWORD FILE=$DATADIR/$tbl-${DELDATE}.exp TABLE
S=$tbl QUERY=\"where dt_last_upd_hist \< to_date\(\'$DELDATE\', \'DD-MON-YYYY\'\
)\"
$ORACLE_HOME/bin/sqlplus $DBO_USER/$DB_PASSWORD <<!
delete from $tbl where dt_last_upd_hist < to_date('$DELDATE', 'DD-MON-YYYY');
/
!
end
done

# For each table, delete based on dt_last_upd
for tbl in `cat $TBLLIST`
do
$ORACLE_HOME/bin/exp $DBO_USER/$DBO_PASS FILE=$DATADIR/$tbl-${DELDATE}.exp TABLE
S=$tbl QUERY=\"where dt_last_upd \< to_date\(\'$DELDATE\', \'DD-MON-YYYY\'\)\"
$ORACLE_HOME/bin/sqlplus $DBO_USER/$DBO_PASS <<!
delete from $tbl where dt_last_upd < to_date('$DELDATE', 'DD-MON-YYYY');


Thanks in advance for any help!
Chris

Mods feel free to move this post if its in the wrong forum/topic.

Maybe: