SQL & PL/SQL

Within the same script, I am using a date variable to pass to a SQL*Plus segment that gets records and drops them in a file. The next SQL*Plus segment uses the same date string to calculate rows to delete for a given number of days prior to that date string. The delete section of the script doesn't work in the script but when I execute it from the command line, it works. Arg!!!!

I'm running on a RHEL 5.6 system with an Oracle 11.2 client install. Oracle related variables (e.g., ORACLE_HOME, etc.) are set and seem to be working fine since the select portion works fine.

Here is the select section of the code:

$ BEGINDATE=$DATEDASHED
$ echo $BEGINDATE
$ $ORACLE_HOME/bin/sqlplus -S username/password@db << EOF > $PROCESSEDLOGSDIR/Raw/RCH_access_log-$BEGINDATE.log
set heading off;
set flush off;
set pagesize 0;
set linesize 4070;
set echo off;
set feed off;
set trimspool on;
set sqlprompt '';
set termout off;
alter session force parallel query;
alter session set isolation_level = serializable;
select /* INDEX(log_contents ix_log_contents_01) */ LOG_CONTENTS.LOG_DATA from LOG_CONTENTS where log_date >= to_date('${BEGINDATE}','YYYY-MM-DD') AND log_date < to_date('${BEGINDATE}','YYYY-MM-DD')+1 AND app_name = 'RCH' order by log_date;

EOF

This above segment works fine. The one just below it in the script does not. I do not get any error nor is there any output in the log file that shows anything but the 'alter' commands being executed. Time stamps in front and back of the sqlplus command below indicate that there is only 1 second gap between the start and finish of sqlplus.

$ echo $BEGINDATE
$ echo `date`
$ $ORACLE_HOME/bin/sqlplus -S username/password@db << EOF > $LOCROOT/logs/Haweye_cleanup-$BEGINDATE.log
set echo ON
alter session force parallel dml;
alter session force parallel ddl;
alter session force parallel query;
DECLARE
i_rowcount pls_integer := 0;
BEGIN
LOOP
DELETE /* INDEX(log_contents ix_log_contents_01) */ FROM log_contents WHERE log_date < to_date('${BEGINDATE}','YYYY-MM-DD')-2 AND rownum <= '1000000';
i_rowcount := i_rowcount + sql%rowcount;
IF sql%rowcount = 0 THEN
COMMIT;
EXIT;
END IF;
COMMIT;
END LOOP;
COMMIT;
END;

EOF

$ echo `date`

I am not editing this script on a Windows system and have checked for any special chars.

The db contains over 50 million rows per day so I know it should take more than a second to delete the records while maintaining indexes. When I perform the task manual, it takes about 2 hours to complete so something is up.

I thought that there may be a problem getting the date passed to the delete statement but it looks almost the same as that of the first sqlplus segment.

I don't see anything but then my eyes are getting old.

Any eyes out there see what I may be doing wrong other than taking a early retirement?

Cheers,

Sam