OraFAQ Forum: Unix » help in running multiple queries

Home » Infrastructure » Unix » help in running multiple queries (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help in running multiple queries [message #629598]

Thu, 11 December 2014 16:58

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

Report message to a moderator

Re: help in running multiple queries [message #629599 is a reply to message #629598]

Thu, 11 December 2014 22:23

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: help in running multiple queries [message #629600 is a reply to message #629598]

Fri, 12 December 2014 00:41

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator