How to use for loop to execute multiple .sql files while using SQLPLUS for db connection.?

UNIX for Beginners Q & A
1

Hello ,

Im calling every single file inside my script like 1.sql , 2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting error[

Unexpected EOF

] , can anyone please help me out in this..
How i can use for loop to invoke my .sql files in unix

echo "exit" | sqlplus -L username/password | grep Connected > /dev/null
if [ $? -eq 0 ]
then
     echo "Connection is success"
    sqlplus -s username/password << EOF
    whenever sqlerror exit sql.sqlcode;
    @/home/1.sql
    @/home2.sql
    exit;
    EOF
else
echo "connection Fail"
fi
2

Hi, EOF must be at the very start of the line

3

You seem to have two problems in your code snippet: The "unxpected EOF" error, and the non-existing for loop construct.
For the error, Scrutinizer proposed a solution.

For the other: What have you tried, and where are you stuck?

For a for loop, you need a list of items to loop across, i.e. the sql scripts you want to execute. How to generate that list?

4

I tried as below constructing for loop outside SQLplus to execute all .sql files which resides in /home/*.sql files.
Throwing an error: UNEXCPETED EOF.

#!/bin/bash
cat /dev/null > output.log
date '+DATE: %m/%d/%y%nTIME:%H:%M:%S' > output.log
for file in `ls /home/*.sql`
do
echo "Executing file $file..."
sqlplus -s username/pwd << EOF >> output.log
@$file
EOF
done
5

I can't see an immediate fault in your script. It works if I copy it to my system. Do you have non-printing characters in there, e.g. DOS line terminators (^M = \r = 0x0D)? How did you produce the script?

P.S.: why did you register as a second user shilpa87?

6

When i run, im getting below error:

SP2-0734: unknown command beginning "echo "Exec..." - rest of line ignored.
SP2-0734: unknown command beginning "SQLFILE=/h..." - rest of line ignored.
7

Please utmost carefully read, understand, and reply to helping posts!

Above errors obviously are NOT the "unexpected EOF", and their solitary presentation doesn't help analysing the problem. When and where do they occur? In the first loop, or later? Is the original error gone?

Please post the entire script, set your shell's -x ( --xtrace ) option (if available in your shell, which btw you didn't mention), run the script and post the resulting output.