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


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
Old 2 Weeks Ago
How to use for loop to execute multiple .sql files while using SQLPLUS for db connection.?

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[
Code:
Unexpected EOF

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

Code:
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

Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by Scrutinizer; 2 Weeks Ago at 03:03 AM.. Reason: Code Tags
# 3  
Old 2 Weeks Ago
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  
Old 2 Weeks Ago
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.

Code:
#!/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

Moderator's Comments:
Mod Comment Please use code tags

Last edited by Scrutinizer; 2 Weeks Ago at 03:23 AM.. Reason: code tags
# 5  
Old 2 Weeks Ago
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  
Old 2 Weeks Ago
When i run, im getting below error:

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

# 7  
Old 2 Weeks Ago
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.
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
How to execute 10 sql files from a folder through sqlplus in shell script? sreekanth Reddy Shell Programming and Scripting 1 09-20-2018 10:09 AM
Execute multiple files in multiple folders and also output on same folder pushpabuzz Shell Programming and Scripting 1 03-23-2017 07:04 AM
Need help to write a function in shell scripting to execute sql files Samah Shell Programming and Scripting 1 05-10-2016 06:53 AM
Create Multiple UNIX Files for Multiple SQL Rows output Rahul_Bhasin Shell Programming and Scripting 14 01-09-2014 05:11 AM
UNIX/SQL loop to spool files!! vr23 Shell Programming and Scripting 2 07-05-2011 09:51 AM
sqlplus: cannot execute yohasini Shell Programming and Scripting 2 05-23-2011 02:10 AM
how to execute multiple .sql scripts from within a shell script using sqlplus TRS80 UNIX and Linux Applications 1 05-14-2011 05:50 AM
SQL query in a loop with single sqlplus connection lsantacana Shell Programming and Scripting 4 02-01-2011 10:26 AM
Execute multiple SQL scripts from single SQL Plus connection guif Shell Programming and Scripting 6 10-25-2010 11:31 AM
Compiling n number of SQL files in loop Dip Shell Programming and Scripting 3 07-12-2010 11:38 AM
Help to merge multiple .sql files snvniranjanrao Shell Programming and Scripting 6 11-30-2009 04:45 AM
For Loop to execute a command on a series of files Gussifinknottle UNIX for Dummies Questions & Answers 2 11-17-2009 08:29 PM
How to execute the multiple line sql using shell script girish.raos Shell Programming and Scripting 1 09-01-2009 06:40 AM
Execute PL/SQL function from Unix script (.sql file) reptile UNIX for Dummies Questions & Answers 1 05-14-2009 07:40 AM
how can i install sqlplus and execute some sql commands talipk AIX 2 12-02-2008 03:54 AM