LinuxQuestions.org - Pass value from unix to sqlplus

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Pass value from unix to sqlplus (https://www.linuxquestions.org/questions/programming-9/pass-value-from-unix-to-sqlplus-4175452943/)

ankitpandey

03-06-2013 06:51 AM

Pass value from unix to sqlplus

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.

All times are GMT -5. The time now is 08:52 AM.