Computing.Net > Forums > Unix > Unix shell script runing SQL files

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

Unix shell script runing SQL files

Reply to Message Icon

Name: richard
Date: July 10, 2003 at 12:10:06 Pacific
OS: XP
CPU/Ram: 128
Comment:

How can I create a Unix shell script that will run individual SQL files having a seperate result file for each SQL file?

Thanks,
Richard



Sponsored Link
Ads by Google

Response Number 1
Name: nails
Date: July 10, 2003 at 13:09:16 Pacific
Reply:

Richard:

You need to tell us what database.

Nails


0

Response Number 2
Name: richardg
Date: July 10, 2003 at 13:58:10 Pacific
Reply:

Sorry...I am using Oracle and it's HP-Unix.

Thanks,
Richard


0

Response Number 3
Name: nails
Date: July 10, 2003 at 14:12:31 Pacific
Reply:

Richard:

I'm not an Oracle guy, but check out "Using SQL in Shell Scripts" by Gleed and Tarvainen:

http://www.samag.com/documents/s=1434/sam9505f/9505f.htm

Regards,

Nails


0

Response Number 4
Name: WilliamRobertson
Date: July 10, 2003 at 16:41:44 Pacific
Reply:

You can use a here-document, e.g:

sqlplus -s /nolog <<ENDSQL
connect ${connectstring}
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
SPOOL ${outfile1}
@script1.sql
SPOOL OFF
EXIT;
ENDSQL

An advantage of this is that you don't have to give away your username/password to anyone running 'ps -ef'.

Starting SQL*Plus and connecting to the database is relatively slow though, so if you have a lot of scripts to run you could call several scripts per SQL*Plus session, or set off one session in background as a coprocess.

None of this is great use of the database though. Perl has database connectivity modules that allow you to make a connection programmatically without having to call SQL*Plus (I'm going to have to learn Perl one day). Or you can write a PL/SQL package to output text files, XML and so on.


0

Response Number 5
Name: Richardgd
Date: July 18, 2003 at 11:43:55 Pacific
Reply:

Thanks, however I am trying to use this shell script. It works great with one sql file but when I place each file on separate lines it doesn't work. I know it's something minor but I can't figure it out.

This works fine!!
#!/bin/ksh
sqlplus -s user/password @/var/cri/test.sql
exit
EOF

Error stating it can't find test1.sql
#!/bin/ksh
sqlplus -s user/password
@/var/cwi/test1.sql
@/var/cwi/test2.sql
exit
EOF


0

Related Posts

See More



Sponsored Link
Ads by Google
Reply to Message Icon






Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Unix Forum Home


Sponsored links

Ads by Google


Results for: Unix shell script runing SQL files

Unix shell script and Oracle data www.computing.net/answers/unix/unix-shell-script-and-oracle-data/5298.html

Unix Shell Script www.computing.net/answers/unix/unix-shell-script/3580.html

Unix shell script problem... www.computing.net/answers/unix/unix-shell-script-problem/6217.html