Different way to connect to database ans execute sql query

Shell Programming and Scripting
1

Hi Friends,

I am using AIX version and sqlplus to connect to database while writing a shell script.
I have implemented three ways to connect to database :
1)

sqlplus -s <USERNAME>/<PASSWORD> <<!
@<SQL FILE TO EXECUTE>
exit
!

2)

sqlplus -s <USERNAME>/<PASSWORD> <<!

-----sql statemenets like -----


SELECT...

exit
!

3)

sqlplus -s <USERNAME>/<PASSWORD> @ <filename.sql>

********************************************

Please help me to find out the difference in above three, and how does it makes a difference if the .sql file is lying in any different location than the script location ??

Or in better words , which way is most economic to use in script for most quick execution.

2

The first one looks like you're telling the database to load the file.

The second one is feeding it select statements right from a here document in the shell script.

The third one is telling it the file to load right from the commandline.

None of these are going to be important to the speed of the query, 99% of the work is happening inside the database and not in the shell. Where the shell script resides makes little difference to the speed of execution either (unless you have a really slow disk or NFS link lurking on your system somewhere.) The third would probably be microscopically fastest since the shell needs to do the least amount of work to do it.

3

Hi
There is no difference between the three with respect to execution. In fact, we have even more different ways to call the sqlplus. Regarding the .sql file, if the sql file is present in a different place, you need to provide the full path while running the sql statement.

Thanks
Guru.

4

Ideally you should pass the username and password inside the here doc for security reasons:

sqlplus -s <<!
<USERNAME>/<PASSWORD>
@<SQL FILE TO EXECUTE>
exit
!
5

Guru, when i am providing the sql file name in the third way, script is not executing the sql file. what could be the reason. And when i am giving the absolute path for the .sql file with a space after "@" then it is getting executed. Whats the significance of space in this ..... for ex :

sqlplus -s <USERNAME>/<PASSWORD> @ /home/abc/xyz/<filename.sql>

and why it is not picking up the sql file though it is lying in the same directory where the script is executed. this problem is coming when i am running the script from cron, however when i run it through its path , it is executing the sql file even without specifying the absolute path in the script. why so ??

My SQL_PATH is different from where the script is lying.

6

You can try doing an 'echo $PWD' to see the value of the working directory. Most likely the value will be different when run from cron. Specifying the absolute path is always a good idea.

Not sure why you need the space after @. I am able to run my scripts without any space.

7

Thanks all for the help !!