Run SQL script from another directory in Shell script

Question

in UNIX Shell script I am inside one directory ex:- cd /usr/was/scr, then after i connect to the SQLPLUS to login into the Database to run the SQL script file xyz.sql from different directory location ex:- /usr/was/abcd. How to execute the xyz.sql after login into the database. Here i the sample code.

cd /usr/was/scr
sqlplus -s <schema>/<user>@<tns> << EOF
exec xyz.sql
spool xyz.log
exit;
EOF

xyz.sql file is in /usr/was/abcd directory.

Doesn't exec /usr/was/abcd/xyz.sql work? Why do you want to cd in the first place anyway? — tripleee
– tripleee, Commented Feb 8, 2019 at 7:32

hmedia1 · Accepted Answer · 2019-02-08 09:34:57Z

1

If you're calling this from within a script, I would think the safest way would be to specify your paths first:

sqlpluspath=/usr/was/scr        #or "${ORACLE_HOME}/bin" or wherever it is if not in $PATH 
spoolfile=/usr/was/scr/xyz.log  #spool file
PATH="${PATH}:$sqlpluspath" \
SQLPATH="${SQLPATH}:/usr/was/abcd" \
sqlplus -s <schema>/<user>@<tns> << EOF
spool $spoolfile
@xyz.sql
exit;
EOF

answered Feb 8, 2019 at 9:34

hmedia1

6,2702 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user8487380 Over a year ago

SQL file is in /usr/was/abcd but you mentioned log file is in /usr/was/scr/xyz.log. also could not understand 3rd & 4th line.

hmedia1 Over a year ago

@user8487380 Where is the sqlplus binary located ? Those lines set environment variables for the program and the shell. $PATH is so you can call the binary from anywhere, and $SQLPATH is specifically so that you can refer to any script within the program by calling it's name. i.e. @xyz.sql. I don't know enough about SQLPLUS to understand what the exec directive is supposed to do, but it's well documented that @ is an alias for START. I'm happy to be corrected

user8487380 Over a year ago

can i use Alias instead of cd. How to go to the path using alias where i have more than 20 sql script files, so that i can not declare one by one.

hmedia1 Over a year ago

@user8487380 It's up to you; If you have 20 script locations, you will need to either: Move them all into one location, reference each script location to set the $SQLPATH, or reference the full path of every script file individually. To me it makes sense to use a combination of organizing scripts together into just a few locations, and adding those locations to the $SQLPATH so that you can run them just using their name.

hmedia1 Over a year ago

@user8487380 You said that's what you were doing already ? In any case, running commands inside a script are much like running them in the shell. See here

|

Collectives™ on Stack Overflow

Run SQL script from another directory in Shell script

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related