Pass value from unix to sqlplus

ankitpandey · 03-06-2013, 06:51 AM

Hello Team,

I am trying to delete records from DB day wise. Below is the code i am trying but it is not deleting any record. Please suggest.

Code:

#!/usr/bin/ksh

LogDirectory=/path/
DataDirectory=/path/

for date in `cat list.txt`
do
echo "delete record for $date"

sqlplus -s user/passwd@DB <<EOF > ${LogDirectory}/output.log

set linesize 32767
set feedback off
set heading off

spool ${DataDirectory}/query_output.txt
delete from table where TO_CHAR(date_column, 'MM/DD/YY') = '$date';

commit;
spool off
EOF
done

Thanks

chrism01 · 03-06-2013, 07:26 PM

Try this page https://forums.oracle.com/forums/thr...hreadID=588156

NevemTeve · 03-06-2013, 11:23 PM

try to debug, eg:

Code:

select '$date' from dual;
select count(*) from table where TO_CHAR(date_column, 'MM/DD/YY') = '$date';

ankitpandey · 03-08-2013, 01:48 AM

Thanks for suggestion, where as i was trying to extend this but unable to find correct way.

All other code part being same, i am trying to modigy the delete condition.

Code:

spool ${DataDirectory}/query_output.txt
delete from table where TO_CHAR(date_column, 'MM/DD/YY') >= '$date' and TO_CHAR(date_column, 'MM/DD/YY') <= <Month end date>;

commit;
spool off

I want to loop it in such a way that date variable will take start date from list.txt file and then it needs to delete till month end date. Please suggest how to do this.