# SQL and shell scripts

**URL:** <https://community.unix.com/t/sql-and-shell-scripts/164533>\
**Category:** UNIX for Dummies Questions & Answers\
**Created:** [August 23, 2006, 10:14pm UTC](https://community.unix.com/t/sql-and-shell-scripts/164533 "2006-08-23T22:14:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Amruta\_Pitkar](https://community.unix.com/letter_avatar/amruta_pitkar/32/6_449c533f586939b6e5f246632a46421f.png) [@Amruta\_Pitkar](https://community.unix.com/u/Amruta_Pitkar)\
**Post date:** [August 23, 2006, 10:14pm UTC](https://community.unix.com/t/sql-and-shell-scripts/164533/1 "2006-08-23T22:14:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Amruta\_Pitkar](https://community.unix.com/letter_avatar/amruta_pitkar/32/6_449c533f586939b6e5f246632a46421f.png) [@Amruta\_Pitkar](https://community.unix.com/u/Amruta_Pitkar)\
**Post date:** [August 24, 2006, 2:48am UTC](https://community.unix.com/t/sql-and-shell-scripts/164533/2 "2006-08-24T02:48:08Z")

</div>

Hi there

I have modified the above code to

```nohighlight
#####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

---

<div class="post-metadata">

**Author:** ![sssow](https://community.unix.com/letter_avatar/sssow/32/6_449c533f586939b6e5f246632a46421f.png) [@sssow](https://community.unix.com/u/sssow)\
**Post date:** [August 24, 2006, 3:02am UTC](https://community.unix.com/t/sql-and-shell-scripts/164533/3 "2006-08-24T03:02:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Amruta\_Pitkar](https://community.unix.com/letter_avatar/amruta_pitkar/32/6_449c533f586939b6e5f246632a46421f.png) [@Amruta\_Pitkar](https://community.unix.com/u/Amruta_Pitkar)\
**Post date:** [August 24, 2006, 3:51am UTC](https://community.unix.com/t/sql-and-shell-scripts/164533/4 "2006-08-24T03:51:04Z")

</div>

Hi

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