Shell Programming and Scripting

Hi Guys,

Even though, i have called db2 sql file earlier, this is my first time to call a oracle sql file.

I need to make a database(oracle) connection and then call the sql file in a loop.

Can you please help me out.

Thanks for your help and time.

Regards,
Magesh

Code:

#!/bin/ksh
cnt=0
while :
do
sqlplus -s username/password@dbname << EOF
  spool ${cnt}.txt
  @/path/to/sql/file/filename
EOF
cnt=$(( $cnt + 1 ))
if [ $cnt -gt 1000 ] ; then
  break
fi
done

This just uses the variable cnt inside the sql to create a spool file 0.txt ... 1000.txt
You do not have to use a shell variable inside the here document.

NOTE: see the last EOF (used to delimit the here doc)? It has to be in column 1 (left hand side) in the script.

Some general considerations...

You should do such kind of tasks in SQL SQL+ or PL/SQL.

Doing this under unix is a very bad design and not performance friendly (as well as I/O consumming (mupltiplied by 1000) ).

You should review the overall process design : what are the contents of these files ? what are they supposed to do ?

Can this be done in 1 file within an SQL loop that will be loaded and processed by the DB memory ? ... instead of causing physical read x 1000 at unix level .

You should always take in consideration the performances (I/O, memory & CPU consumption) and make sure the way you've chosen to perform the needed task is an accurate and efficient one.

Plus, sql*plus is not good at moving data like isql, I guess too much designed by Informix and MAINFRAME envy for fiddly formatting. You might use jisql and JDBC, or UNIXodbc isql.

Running 1000 times says it should be calling a stored procedure or PL/SQL loop, where you send the parameters in and the SQL runs with them inside.

As I recall, ORACLE net login is like many others: a host, a port, an instance name, an id and a password. It can also run locally in UNIX on a pipe. Once in, your id is not local to tables unless someone made synonyms or views, as it is much more id = schema-catalog-name than some others where your id is, by request or just by default, associated with a schema-catalog-name.

Hi DGPickett

Ok that was general consideration and i agree :
That was the way i was thinking of ... (Of course it still may depends on what these files
actually contains and what is the final goal to achieve.)