CODEWITHSUNDEEP
oracle-databasebashunix
misguided
misguided

Reputation: 3799

SQL script not executing in bash

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

Upvotes: 1

Views: 3468

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 3

Related Questions