How to run multiple SQL script files in a Shell script

Question

I have to create 1 UNIX Shell script. In that shell script i want to run multiple SQL script files from the same directory. I have used like this-

#!usr/bin/ksh
SQLPATH = /usr/sql/
(cd $SQLPATH;
'sqlplus usr/password@sid <<EOF
 spool <db_file>.log
 @<db_name>.sql
 set echo off
 set heading off
 spool off
 &&

 spool <db_file2>.log
 @<db_name2>.sql
 set echo off
 set heading off
 spool off
 &&

 spool <db_file3>.log
 @<db_name3>.sql
 set echo off
 set heading off
 spool off
 exit;
 EOF')

exit 0

There are multiple SQL scripts like this and for each SQL script I have to create log files so I used spool here. After every SQL script files execute I used &&. So Is it good to use && here and in 3rd line ; when I define the PATH. Please provide me the better solution.

What's with the single quotes? (And similarly, spaces aren't allowed around the `=` in a variable assignment, and `EOF` ending a heredoc isn't allowed to be indented). I can't see how your code would ever work as currently written. — Charles Duffy, Feb 12 '19 at 11:15
@CharlesDuffy Actually there are around 20 SQL script files which is 3-4 different locations path so frequently path used in the shell script I declare variable here. That's why i used `cd` here. Also after `EOF` what is the use of `&&` which is mentioned in the answer. — Shahin P, Feb 12 '19 at 11:52
`foo && bar` is shorthand for `if foo; then bar; fi` i.e. run `bar` only iff `foo` completed successfully. I thought that's what you tried to ask about -- what do you want the `&&` to mean in your own code? — tripleee, Feb 12 '19 at 12:09

tripleee · Accepted Answer · 2019-02-22T10:55:11.120

3

Don't override the system PATH (or now SQLPATH) and don't put commands in single quotes. Use lowercase for your private variables, and you can't have spaces around the equals sign in an assignment; but a variable you only use once is useless anyway, so I took it out, and hardcoded the cd argument.

I'm guessing you want something like

#!/usr/bin/ksh

# No spaces around equals sign, and don't use uppercase for private variables
# But why use a variable at all anyway
#sqlpath=/usr/sql
cd /usr/sql  # is this really necessary and useful??

sqlplus usr/password@sid <<____EOF &&
 spool <db_file>.log
 @<db_name>.sql
 set echo off
 set heading off
 spool off
____EOF

sqlplus usr/password@sid <<____EOF &&
 spool <db_file2>.log
 @<db_name2>.sql
 set echo off
 set heading off
 spool off
____EOF

sqlplus usr/password@sid <<____EOF
 spool <db_file3>.log
 @<db_name3>.sql
 set echo off
 set heading off
 spool off
____EOF

# exit 0  # Not necessary or useful

If the multiple sqlplus commands can be executed in a single session, that would be an obvious improvement; but I'm guessing sqlplus has no way of expressing what you appear to mean with && (this syntax seems to have a quite distinct meaning in sqlplus).

edited Feb 22 '19 at 10:55

answered Feb 12 '19 at 11:16

tripleee

175,061
34
275
318

can i use `cd /usr/sql` directly without use `()` and what is meaning of ____ before `EOF`. To run every SQL script file I have to connect every time DB through `sqlplus` or only one time and then run all the SQL scripts. Could you please help me to resolve the issue. – Shahin P Feb 12 '19 at 11:24
The parentheses create a subshell but the entire script already runs in its own shell instance, so the subshell does nothing useful. You probably don't need to `cd` at all (do you have write access to this directory?). The underscores are just for legibility -- the token after `<<` can be any text. I don't understand your last sentence but it seems to chime with the last paragraph in this answer. – tripleee Feb 12 '19 at 11:26
when i run the above shell script then it is showing an error. `-ksh: .ksh: not found [No such file or directory]` – Shahin P Feb 22 '19 at 10:42
@ShahinP The problem is not with the script, but with how you are running it. I can't tell you what's wrong because you have replaced the real thing with `` but perhaps you are looking for `./.ksh` – tripleee Feb 22 '19 at 10:51
I have run from 2 type `./.ksh` and `ksh .ksh` but in both time it is showing the same error – Shahin P Feb 22 '19 at 10:53
I noticed a typo but I don't think it affects this script. What happens if you do `sh ./.ksh`? – tripleee Feb 22 '19 at 10:55
Yes i have run this script by using `./` but i am getting error. I don't know why. There s any need to mention ORACLE_HOME path & anything else – Shahin P Feb 22 '19 at 10:57
Notice how I put `sh` instead of `ksh`? Can you try that? – tripleee Feb 22 '19 at 10:58
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188879/discussion-between-tripleee-and-shahin-p). – tripleee Feb 22 '19 at 10:58

score 1 · Answer 2 · answered Feb 18 '19 at 22:00

Make a function

#!/usr/bin/ksh
SQLPATH=/usr/sql # Offtopic: Better use lowercase for your own variables

process_sql_and_log() {
   if [ $# -ne 2 ]; then
      echo "Usage: $0 sqlfile logfile"
      return 1
   fi
   sqlplus usr/password@sid <<EOF
spool ${2}
@${1}
set echo off
set heading off
spool off
exit;
EOF
}

  cd "${SQLPATH}" &&
  process_sql_and_log db_name.sql  db_file.log  &&
  process_sql_and_log db_name2.sql db_file2.log &&
  process_sql_and_log db_name3.sql db_file3.log

exit 0

How to run multiple SQL script files in a Shell script

2 Answers2