SQL and shell scripts

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Initial code is modified...Can u help me with the next post.....

2

Hi there

I have modified the above code to

#####Connecting sqlplus to check the connectivity
sqlplus -s /nolog <<EOF>/dev/null
connect ${DB_LOGIN}/${DB_PASSWORD}@${DB_NAME}
cat /home/ampi/EnablementProject/MyWorkSpace/emails/$FILENAME | while read LINE
do
    echo ${LINE}
    acctno=`echo $LINE | cut -f1 -d','`
    echo $acctno
    salutation=`echo $LINE | cut -f2 -d','`
    name=`echo $LINE | cut -f3 -d','`
    billdate=`echo $LINE | cut -f4 -d','`
    totaldue=`echo $LINE | cut -f5 -d','`
    billdeductiondate=`echo $LINE | cut -f6 -d','`
    billduedate=`echo $LINE | cut -f7 -d','`
    templatecode=`echo $LINE | cut -f8 -d','`
    billid=`echo $LINE | cut -f9 -d','`
    emailid=`echo $LINE | cut -f10 -d','`
    brnno=`echo $LINE | cut -f11 -d','`
    billsummaryid=`echo $LINE | cut -f12 -d','`
    batchno=`echo $LINE | cut -f13 -d','`
    emailfile=`echo $LINE | cut -f16 -d','`
    echo "Insert into table"
    spool ${SYS_TEMP_DIR}/${JOB_ID}_db_sql.log
    INSERT INTO SendMailDetails (acctno) values ('${acctno}');
    echo "Table Appended"
done
commit;
spool off;
exit;
EOF

The do..while loop when executed without the SQL statement reads the input filecorrectly and also stores values into the variables. but when I include SQL statements in the loop...no values are read ...Variables are blank...why ?

Amruta Pitkar

3

escape the brackets in insert statement.

INSERT INTO SendMailDetails \(acctno, salutation, name\) values \('$acctno','$salutation','$name'\);

Not sure how it is going to work. You need to use sqlplus -s and here document <<EOF and EOF to make the interactive things work..

sqlplus -s username/password@connectstring <<EOF
INSERT INTO SendMailDetails (acctno, salutation, name) values ('$acctno','$salutation','$name');
commit;
exit;
EOF

4

Hi

The Escape characters before the brackets ...still gives the same problem..The variables are empty...