Help! Unix newbie ksh problem

hi,
a newbie in unix wants help from the expert guys.
i have to write a ksh script which does the following:
1. open sql plus session.
2. read (say path to the unix directories) from an oracle table. It will return me 2 records (say, path for US files and path for UK files )

the dir structure is:
for US: /usr/us
for UK: /usr/uk

The above dir path is stored in a db table.

now each of these dir's will hav 2 subdirectories: internal and external.

each of the subdir's (internal and external) will have many ".loc" files.


3. My requirement is:
--------------algorithm
1. open the sql plus session

2. select "path" from the db table (returns 2 paths--viz: /usr/us and /usr/uk)

3. for each of these main dirs, check for the "external" subdir.--this should be a loop
if external subdir is there---count the no of .loc files, say c
----loop till count is c for each .loc file
----------process the .loc file
----end loop
if internal subdir is there
---count the no of .loc files, say c
----loop till count is c for each .loc file
----------process the .loc file
----end loop


Guys can u pls provide me some similar code which can help me for the same. i am using korn shell for the scripts

Thanks

This is a start - you get to finish off your requirements




You'll have to ignore some blank lines. Or you can rewrite it to run sqlplus twice.

thnx jim for the headstart

i will take it as the starting point.
i have a query regarding the same.

im getting 2 records from the select query that i will fire
to the db using the sql plus that i fire from the script.
So i will have a recordset output frm the select statement.

How can i assign the recortset which i am getting from the select inside the script?is there any kind of var which i can use for the same(like array or something)?

say if the sql query being fired by me is:

select path from world_table; it returns 2 records : /usr/us and /usr/uk.

i need to set them to a recordset so that i can loop as per the no of records i receive.

In the example I gave you, result contains the output of the qeuries, one query at a time.

thnx jim
will try to apply the same logic.

Hi guys
can u pls let me know if anythings wrong with the below script for my case.

Thanks in advance

#!/bin/ksh
###########################################user name
username='sys'

# directory to pwd file
pswdFile=`/cat /ora0/oracle/passwd/sys`;
#read the pwd file for the pwd

cat PSWD < $pswdFile

# correctly configure SID
SID='mySid'

# utl_out='/app/oracle/admin/mySid/utlout/'

#Run a sql file which contains the sql query
#Save the returning MULTIPLE records into the unix array data type.
set -A results `sqlplus -s $username/$PSWD@$SID WatchSql.sql`
size_of_array=${#results[@]}
let counter=0
while [ $counter -lt $size_of_array ]
do
echo " ${results[$counter]} "
### Loop for each FILE PATH(main directories)
#### eg: /country/canada/
temp= ${results[$counter]}

# path is not a dir, return
if [[-d $temp ]] then
return
fi

# path is indeed a directoty
#check if INSURANCE directory is there

feedDir=$temp/INSURANCE

if [[-d $feedDir && -n $feedDir ]] then
## yes INSURANCE dir exists

# for each file ending in HNN,
# move the file to utl_out directory
# invoke the sql routine passing the original directory(incl INSURANCE)
# and the filename of the file moved.
# moves the resulting output file and error file to the
# originating directory(including INSURANCE)

# get all files ending in .HNN in this directory
#tempFile to store one ascii file name
for filename in ${tempFile:+$feedDir/}*.HNN ; do

#extract the filename from the path for passing to the parser routine.
#file name eg: JUNE.HNN
fileName=${tempFile##/*/}

#move the file to utl_out dir for processing
mv $tempFile $utl_out

#change dir to utl_out
cd $utl_out

# invoke the parsefile routine
sqlplus -s username/$PSWD@$SID<<-ENDSQL
exec PROCEDURE(feedDir,fileName)
exit
ENDSQL

#check if any .LOG files are generated in the current dir(i.e utl_out)
if [[-a $fileName.LOG]]
#move it to original dir(INSURANCE)
mv $fileName.LOG $feedDir
fi

##clean the var
fileName=""
tempFile=""
done
### end processing all the Ascii files
fi

let counter=counter+1
done