Home » Infrastructure » Unix » help in running multiple queries (11g)
help in running multiple queries [message #629598] Thu, 11 December 2014 16:58 Go to next message
sisimoz
Messages: 3
Registered: December 2014
Location: MOZ
Junior Member
Hi,
Sorry if is not the right forum.
I would like to run multiple queries like bellow
file1.sql, file2.sql, filen.sql

Oracle_home=/.../
$Oracle_home/bin/sqlplus user/password@tnsname <<EOF
i=1
while $i<5 do;
@/home/scripts/file$i.sql
i=$(($i+1))
done
exit

So how can do it, help
So i need help in running such queries
Re: help in running multiple queries [message #629599 is a reply to message #629598] Thu, 11 December 2014 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

It appears that you only know a little about both *NIX & Oracle and neither to any level competency.
In any *NIX, ORACLE_HOME must be all UPPERCASE in order for it to be recognized & used.
Line like below are NOT valid sqlplus commands.
>i=1
>while $i<5 do;

The posted script lacks the needed
EOF
line to terminate the "here script"

@tnsname is not required when you are logged onto the DB Server itself.

you could have a plain text file, sql1to5.sql that contains lines below

@/home/scripts/file1.sql
@/home/scripts/file2.sql
@/home/scripts/file3.sql
@/home/scripts/file4.sql
@/home/scripts/file5.sql
exit

then do as below
sqlplus username/password @sql1to5.sql

Re: help in running multiple queries [message #629600 is a reply to message #629598] Fri, 12 December 2014 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or
export ORACLE_HOME=/.../
var=''
i=1
while ($i<5)
do
  var="$var
@/home/scripts/file$i.sql"
  i=$(($i+1))
done
$ORACLE_HOME/bin/sqlplus user/password@tnsname <<EOF
$var
exit
EOF

Re: help in running multiple queries [message #630062 is a reply to message #629600] Fri, 19 December 2014 08:20 Go to previous messageGo to next message
sisimoz
Messages: 3
Registered: December 2014
Location: MOZ
Junior Member
Hi,
Thank you for your help. I could run my queries successful.
Re: help in running multiple queries [message #630063 is a reply to message #629599] Fri, 19 December 2014 08:22 Go to previous message
sisimoz
Messages: 3
Registered: December 2014
Location: MOZ
Junior Member
Thank you for great tips. It is exceptional.
Previous Topic: Telnet 192.168.253.98 5000 gives connection refused
Next Topic: Shell script - update dates every month
Goto Forum:
  


Current Time: Mon Dec 18 01:30:39 CST 2017

Total time taken to generate the page: 0.08408 seconds