I have to perform some oracle database task so i have written a test scripts which open the database connection and perform a select . once it is done i want it to come out from data base to unix prompt which is not happening. the below command will be a part of shell scripts and once it is done shell will perform other task which are written after these lines of codes.
Code:
sqlplus -S ERS_SRC/ERS_SRC@ERS11G
select sysdate from dual;
EXIT;
Please suggect what to do
Moderator's Comments:
Please use code tags next time for your code and data.
Last edited by zaxxon; 08-22-2012 at 10:05 AM..
Reason: code tags
You should post the shell script you have troubles with, not some (eventually irrelevant) part of it.
Even in the part you posted there are some errors (hence the initial remark):
Code:
sqlplus -S ERS_SRC/ERS_SRC@ERS11G
select sysdate from dual;
EXIT;
The first line is directed to the shell, because it calls a program ("sqlplus") with some options. The second and third line are not directed against the shell, but against this called program - from where should the shell know that it has to redirect the text to this?
Here is what i suppose to happen: you start the script, it runs up to the line with "sqlplus", "sqlplus" starts and simply stands at the input prompt and the rest of the script is waiting for "sqlplus" to terminate - which never happens.
Do it the following way (it is called a "here-document"):
Code:
sqlplus -S ERS_SRC/ERS_SRC@ERS11G <<EOF
select sysdate from dual;
EXIT;
EOF
cd /ersdg3/ERS/ERS_INPUT_LOGS/RIO/LOGS/$NEWDIR
#test -e *.LOG $1
# if [$? ne 0]; then
if [ -f *.LOG ]
then
for i in *.LOG
do
flnm=$i
#echo "file name is $flnm"
#sed "s:e:$flnm{i}:g" /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/control.ctl > /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/controlnew.ctl
sed "s/FILENAME/$flnm/g" /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rio.ctl > /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rionew.ctl
echo " ...............Connecting......................"
#......Run sql loader to load data in oracle database..................
sqlldr ERS_SRC/ERS_SRC@ERS11G control = /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rionew.ctl data = $flnm
#.....Add the file to .zip file.........
mv $flnm /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR
echo "files have been loaded .... $flnm"
done
echo ".....Please wait ..Zipping the logs file..."
##zip -r /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR . -i *.LOG
else
echo "Files doesnt exist"
fi
if [ -f *.TXT ]
then
sqlldr ERS_SRC/ERS_SRC@ERS11G control = /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rio_rec_cnt.ctl data = RIO_AUDIT_REC.TXT
else
echo "Files doesnt exist"
fi
mv RIO_AUDIT_REC.TXT /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR
# Create oracle partiton for every data once loader load the data
dbpart () {
sqlplus -S ERS_SRC/ERS_SRC@ERS11G << EOF
exec PROC_PARTITION_MGMNT;
EOF
}
dbpart;
But the given script does not have so many lines... How do you expect us to help you if you keep hidding things?
Okay, I have just edited the script. I apologise.
Please see the full script:
Code:
#this shell will loadd all the LOG files data in oracle
# The below line will copy the logs to respective forlder so tha ETL will read the LOGS from there.
mkdir /ersdg3/ERS/ERS_INPUT_LOGS/RIO/LOGS/$NEWDIR
cd /var/opt/ers/logs/rio
touch -t `date +%Y%m%d0000` dummy
find . -newer dummy -type f |
while read fname
do
mv $fname /ersdg3/ERS/ERS_INPUT_LOGS/RIO/LOGS/$NEWDIR
done
cd /ersdg3/ERS/ERS_INPUT_LOGS/RIO/LOGS/$NEWDIR
for i in *.LOG
do
wc -l $i | awk '{ print $1"|"$2 }'
done>RIO_AUDIT_REC.TXT
# SQLLader will read from the below location to populate the data in SOURCE tables.
# Creating directory
NEWDIR=RIO_`date +%d-%b-%Y`
#echo $NEWDIR
mkdir -p /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR
#cd /backup/temp/rajesh/loader
########Setting log directory path ########
cd /ersdg3/ERS/ERS_INPUT_LOGS/RIO/LOGS/$NEWDIR
#test -e *.LOG $1
# if [$? ne 0]; then
if [ -f *.LOG ]
then
for i in *.LOG
do
flnm=$i
#echo "file name is $flnm"
#sed "s:e:$flnm{i}:g" /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/control.ctl > /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/controlnew.ctl
sed "s/FILENAME/$flnm/g" /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rio.ctl > /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rionew.ctl
echo " ...............Connecting......................"
#......Run sql loader to load data in oracle database..................
sqlldr ERS_SRC/ERS_SRC@ERS11G control = /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rionew.ctl data = $flnm
#.....Add the file to .zip file.........
mv $flnm /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR
echo "files have been loaded .... $flnm"
done
echo ".....Please wait ..Zipping the logs file..."
##zip -r /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR . -i *.LOG
else
echo "Files doesnt exist"
fi
if [ -f *.TXT ]
then
sqlldr ERS_SRC/ERS_SRC@ERS11G control = /ersdg3/ERS/ERS_INPUT_LOGS/RIO/control/rio_rec_cnt.ctl data = RIO_AUDIT_REC.TXT
else
echo "Files doesnt exist"
fi
mv RIO_AUDIT_REC.TXT /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive/$NEWDIR
# Create oracle partiton for every data once loader load the data
dbpart () {
sqlplus -S ERS_SRC/ERS_SRC@ERS11G << EOF
exec PROC_PARTITION_MGMNT;
EOF
}
dbpart;
# delete the directory from the RIO directory.
cd /ersdg3/ERS/ERS_INPUT_LOGS/RIO
rm -rf $NEWDIR
# Housekeeping for the files
find /ersdg3/ERS/ERS_INPUT_LOGS/RIO/rio_archive -type d -mtime +47 -exec rm -rf {} +
Last edited by methyl; 08-22-2012 at 11:06 PM..
Reason: correct spelling and grammar of anotation for readability
notice the space character, which may be a problem.
further, you have indented the code, which is not entirely possible with here-documents: the closing "EOF" must be at the beginning of the line:
Code:
# this will work:
command .... <<EOF
text to send to command
EOF
# but this will not, because of the last line:
command .... <<EOF
text to send to command
EOF
You can use indented closing clauses if you prepend the opening clause with a minus sign:
Code:
# this will work:
command .... <<-WORKSTOO
text to send to command
WORKSTOO
but don't know if this is the case in ksh too, i only know for sure it works in bash.
I suggest you go over this script (which has some other design deficits too)
with someone knowledgeable to make it better written.
Heyas,
Since this question (similar) occur every now and then, and given the fact i was thinking about it just recently (1-2 weeks) anyway, i started to write something :p
The last point for motivation was... (17 Replies)
Does anyone know any websites where there are finished scripts, can be for anything
Just want to copy and paste it and manipulate into something I could use.
danke schon (0 Replies)
I'm trying to execute a script from within my java code. The execution of the script is over(it's pid is no more), but java is stuck on waitFor() method of the shell script process!. And yes, I'm reading output and error streams in 2 separate threads. Yes, they are being joined at the end(after... (0 Replies)
Hello gurus,
I have three korn shell script 3.1, 3.2, 3.3. I would like to call three shell script in one shell script.
i m looking for something like this
call 3.1;
If 3.1 = "complete" then
call 3.2;
if 3.2 = ''COMPlete" then
call 3.3;
else
exit
The... (1 Reply)
HI All,
I am running one shell script, in that script i am calling 4 scripts in the background.
abc.ksh &
efg.ksh &
xky.ksh &
mno.ksh &
please let me know, how could i find the success and failure of each script.
i Cannot use $?, because i want to run all the scripts in parellel.
... (2 Replies)
if
then
# mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com
echo "FILE does not exist"
exit 1
fi
echo "FILE EXIST"
size=-1
set $(du /export/home/oracle/nas/scott21.dmp.gz)
while
do
echo "Inside the loop"
size=$1
set $(du... (1 Reply)
could somebody tell me please how to exit a shell script:
if
then
echo "No arguments detected"
exit 1
fi
...
echo "still there" # is displayed .. :-( (4 Replies)
I have a sript which is going to trigger other 3 scripts in background simultaneously
for eg:
Main Script:(main.sh)
-----------
sh a.sh &
sh b.sh &
sh c.sh &
How to catch the exit status and store it in a variable for all those three scripts in main script. Is there any other way of... (4 Replies)