Visit Our UNIX and Linux User Community


Different way to connect to database ans execute sql query


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Different way to connect to database ans execute sql query
# 1  
Old 04-28-2010
MySQL Different way to connect to database ans execute sql query

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)
Code:
sqlplus -s <USERNAME>/<PASSWORD> <<!
@<SQL FILE TO EXECUTE>
exit
!

2)
Code:
sqlplus -s <USERNAME>/<PASSWORD> <<!

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


SELECT...

exit
!

3)

Code:
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.

Last edited by Scott; 04-28-2010 at 03:47 PM.. Reason: Code tags, please...
# 2  
Old 04-28-2010
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  
Old 04-28-2010
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.

Last edited by Scott; 05-04-2010 at 09:38 AM.. Reason: Removed self-promotion link
# 4  
Old 04-28-2010
Ideally you should pass the username and password inside the here doc for security reasons:

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

# 5  
Old 04-29-2010
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  
Old 04-29-2010
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  
Old 04-30-2010
Thanks all for the help !!

Previous Thread | Next Thread
Test Your Knowledge in Computers #301
Difficulty: Easy
Slackware was originally derived from the Software Linux System (SLS).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to execute sql query.

Hi Experts, Need your support. Not able to use sql query alias in shell script. Could you please help me in finding right way to use alias with sql query in shell script. Below is the code i am using. #!/bin/bash sqlplus -s abc/abc@abc << EOF> bcd.csv set trimspool on select zone_id... (4 Replies)
Discussion started by: as7951
4 Replies

2. AIX

Connect to database server and execute sql

I have a requirement and below is the detail. Create a shell script and needs to run in server "a". Connect to teradata database server "b". execute the .sql file from server "a" Save the output of the query to a file in server "a" Schedule this shell script to run every day for every 4... (1 Reply)
Discussion started by: MadhuSeven
1 Replies

3. Shell Programming and Scripting

Korn Script to connect and query oracle database

I've been sent the following script to finish. It's supposed to connect to an oracle database, query it, and send an email if the query result value is one or more. Currently it isn't connecting properly, just giving the following error: ERROR: ORA-01017: invalid username/password; logon denied... (2 Replies)
Discussion started by: jackmorgan2007
2 Replies

4. Programming

JDBC code to pass the SQL query as parameter and execute?

Below i have the sample code. i need to pass the entire query from file or as parameter and read the results and write into a output file. here the number of columns are unknown. some times it may be 2,3 or entire columns from the table. read all the column results and write into a comma... (0 Replies)
Discussion started by: laknar
0 Replies

5. Shell Programming and Scripting

Shell Linux to connect to a database and execute store procedure

HI, i want to write a script (Linux) that: 1) connect to a database oracle 2) execute some store procedure. Can anybody help me, please? Thanks a lot Andrew (3 Replies)
Discussion started by: manichino74
3 Replies

6. Shell Programming and Scripting

We need a script that invokes the sql query every 14 days ans send email

HI, We need a script that invokes the sql query every 14 days ans send email (0 Replies)
Discussion started by: bujjisveeru
0 Replies

7. Shell Programming and Scripting

We need a script that invokes the sql query every 14 days ans send email

Hi, We need a script that invokes the sql query every 14 days ans send email (0 Replies)
Discussion started by: bujjisveeru
0 Replies

8. Shell Programming and Scripting

Execute SQL query in unix script

Hi I am new in unix. oracle and unix are installed in my sytem.i need the script which could connect to the oracle using username ,password and schema and can run the select * from tab query. Thanks vijay (8 Replies)
Discussion started by: vijays3
8 Replies

9. Shell Programming and Scripting

Oracle SQL Query & connect?

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best... (8 Replies)
Discussion started by: Dird
8 Replies

10. Shell Programming and Scripting

TO execute .sql 2005 query file in shell script

Hi, I know in oracle a .sql file is called by @ <path> /<filename>. But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
Discussion started by: n2ekhil
0 Replies

Featured Tech Videos