SQL script not executing in bash

Question

I am running an SQL script from bash. One of the scripts seems to be running fine, but the other script fails. Can you please advise what might be the cause for the same?

#!/bin/bash
sqlplus -S user/password@database << EOF
whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off
@MyScript1
@MyScript2
exit;
EOF

Error: SP2-0310: unable to open file "MyScript2.sql"

In Unix the access level for both is:

 -rwxrwxrwx  MyScript1.sql
 -rwxrwxrwx  MyScript2.sql

The error does give an indication that it is not able to access the file MyScript2.sql. But what I am curious about is how come it can access MyScript1.sql which is present in the same folder, but not MyScript2.sql?

Also if I run the file just in unix (using SQL*Plus) from the folder where the files are present it works fine. But if I run the same from a different folder it doesn't. Below example will explain it better

/Folder/having/the/files

both MyScript1.sql and MyScript2.sql run fine

/Some/random/folder

MyScript1.sql runs fine , but MyScript2.sql errors out

Do commands from MyScript1 actually get executed? What happens if you comment out the @MyScript1 line? — 9000
– 9000, Commented Apr 18, 2013 at 1:36
yes MyScript1 does get execute . if I comment out @MyScript1, I only get the error SP2-0310: unable to open file "MyScript2.sql" — misguided
– misguided, Commented Apr 18, 2013 at 1:43
My best bet so far is a typo in the name, something like MyScript2.sql vs Myscript2.sql. Have you tried renaming MyScript2.sql somehow differently? — 9000
– 9000, Commented Apr 18, 2013 at 1:47
@BobJarvis I will try that and it might work. But the question still remains how does MyScript1.sql work without the full path but MyScript2.sql doesn't? — misguided
– misguided, Commented Apr 18, 2013 at 2:42
Do you have $SQLPATH set, and if so do you have a file called MyScript1.sql, or a link to your real file with that name, in that directory? If you aren't running the script in the same directory as your files then you will need to give the full path (or set $SQLPATH in your acript, which might have side effcts (skipping login.sql, so just using the full path ought to work better). — Alex Poole
– Alex Poole, Commented Apr 18, 2013 at 7:02

Alex Poole · Accepted Answer · 2013-04-18 09:10:14Z

You said:

if I run the file just in unix (using SQL*Plus) from the folder where the files are present it works fine. But if I run the same from a different folder it doesn't.

If you run the bash script from a different folder to where you have the SQL files, how do you expect SQL*Plus to know where to find those? The question becomes not 'why can't it see MyScript2.sql, but why it can see MyScript1.sql. The obvious answer is that it can't, or at least can't see the version of the file you think it's seeing.

From the SQL*Plus documentation:

SQL*Plus searches for SQL scripts, including login.sql, in the current directory and then in the directories specified by SQLPATH, and in the subdirectories of SQLPATH directories.

So if you haven't given the full path to the SQL file, it will search in the current working directory - where you are sitting when you execute the bash script, not the directory the bash script is in, i.e. what pwd shows - and the in $SQLPATH if it is set.

That suggests you have a copy of MyScript1.sql in one of those places, or possibly a soft link to your real file. If I had to guess, I'd speculate that you originally wrote MyScript.sql the same directory as the script, then copied it to another directory before writing MyScript2.sql. In any case, the MyScript1.sql you're running might be out of date, or is likely to become so in the future.

The short answer is to give the full path to the SQL files, either as part of the @ command, or by changing to that directory in the bash script before launching SQL*Plus.

Collectives™ on Stack Overflow

SQL script not executing in bash

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related