Shell Programming and Scripting

I am oracle devloper currently i get a chance to work on shell scripts to run sql statements.

extarct.txt contains 15 .sql files, want to using loop continue three times,first it should take 1 to 5 next 5 to 10 and last 10-15
I can't run all the .sql file in single run to avoid deadlock condition.

tried below code

Code:

#!/bin/sh 

cat extract.txt |while read name
do
sqlplus -s connect << EOF
userid/password@database
echo $name
exit
EOF
done

extact.txt

Code:

@1.sql
@2.sql
@3.sql
@4.sql
@5.sql
@6.sql
@7.sql
@8.sql
@9.sql
@10.sql
@11.sql
@12.sql
@13.sql
@14.sql
@15.sql

Welcome to the forum.

Please become accustomed to supply info on your system / environment like OS, shell's, tools' versions.

We had a similar problem recently. What is your split version?
Do you want to run five scripts in parallel, or three, or all sequential?

OS-IBM AIX
Yes i need to run the script five scripts in parallel

So, I think you mean you want to run 1-5, then 6-10, then 11-15.

Can you use split to create 5 line input files? You can loop round the files you have generated and submit jobs in the background for each then wait for them all to complete. That is one way.

Another way might be to read the whole file as a single loop, but you might hit problems if your record count is not strictly divisible by 5.

Yet another way (but a logical change) would be to read the file in as a single loop and start 5 jobs. Then watch those jobs (get the background process id from $! just after it is launched) and when one finishes, start another. This way you will have 5 jobs running always rather than one long running job stopping all the others, but it depends if that's what you want your logic to do. It is a structural difference which may not be what you want.

Can you clarify what you want it to do?

I presume you are writing this in ksh or are you using bash or something else? This is usually considered better as a loop to prevent anything within the loop from reading (eating up) your input file data:-

Code:

while read name
do
   # whatever
done < extract.txt

I also don't think that echo is a command for within sqlplus. Perhaps !echo might do the display or perhaps SELECT $name FROM dual ; would also do it.



Kind regards,
Robin

You can read 5 lines at a time

Code:

while
  read name1 &&
  read name2 &&
  read name3 &&
  read name4 &&
  read name5
do
  echo "do something with $name1 $name2 $name3 $name4 $name5"
done < extract.txt

Oracle does (seems to) have some PARALLEL execution Features ever since version 7.1. Do you want to deploy those or do you want to run five sqlplus *nix processes in background (as rbatte1 proposed)?

Thanks for Quick response

Below code which we are doing manually (if extract.txt contains n record then loop count n/5 times parallel)

Code:

#!/bin/sh 

sqlplus -s connect << EOF
userid/password@database
@1.sql
@2.sql
@3.sql
@4.sql
@5.sql
exit
EOF

sqlplus -s connect << EOF
userid/password@database
@6.sql
@7.sql
@8.sql
@9.sql
@10.sql
exit
EOF

sqlplus -s connect << EOF
userid/password@database
@11.sql
@12.sql
@13.sql
@14.sql
@15.sql
exit
EOF