Hi Gurus,
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.
sqlplus -S ERS_SRC/ERS_SRC@ERS11G
select sysdate from dual;
EXIT;
Please suggect what to do
Pls try below ..
myfun () {
sqlplus -S ERS_SRC/ERS_SRC@ERS11G << EOF
select sysdate from dual;
EOF
}
myfun;
Thx!
Shirish
I wonder why this runs at all.
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):
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"):
sqlplus -S ERS_SRC/ERS_SRC@ERS11G <<EOF
select sysdate from dual;
EXIT;
EOF
I hope this helps.
bakunin
Hi I have put the code in my scripts but it gives me error
./load_rio.sh[58]: syntax error at line 59 : `<<' unmatched
dbpart () {
sqlplus -S ERS_SRC/ERS_SRC@ERS11G << EOF
exec PROC_PARTITION_MGMNT;
EOF
}
dbpart;
my actual scripts is
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;
please let me know what is the issue
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:
#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 {} +
The first thing is you have written
command .... << EOF
instead of
command .... <<EOF
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:
# 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:
# 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.
I hope this helps.
bakunin
You should not have a space between << and EOF, The below sample code should run the procedure for you:
exec_sql=`sqlplus -S <<EXECPROC
ERS_SRC/ERS_SRC@ERS11G
set linesize 110 define off heading off feedback off serveroutput on format word_wrapped
whenever sqlerror exit 1
declare
begin
proc_partition_mgmnt;
dbms_output.put_line( '*** Finished procedure: proc_partition_mgmnt' );
exception
when others then
dbms_output.put_line( '*** Error running procedure: proc_partition_mgmnt' );
dbms_output.put_line( to_char( sqlcode ) || ' : ' || sqlerrm );
end;
/
exit 0
EXECPROC`
if [[ $? = 1 ]] then
print `date "+%Y-%m-%d-%H.%M.%S"` "*** Error running procedure: proc_partition_mgmnt."
fi
if [[ $exec_sql = +(*** Error) ]] then
print `date "+%Y-%m-%d-%H.%M.%S"` "*** Error running procedure: proc_partition_mgmnt."
print `date "+%Y-%m-%d-%H.%M.%S"` "$exec_sql"
fi
Hi
Scripts is working , there was a space but it works with the space as well . it doesn't work when trying to indent so i have used command as suggested. I have an issue since this database part is done in the middle of the scripts and it exit finally from the scripts, how can i make sure it does it part in data base and then execute rest of the scripts
hmm, you already know the routine: post an error message and we take a look at it. If there is no error message, start the script with "ksh -x <scriptname>" and show the relevant parts of the output (before you ask why redirection doesn't work: it goes to <stderr>).
In other words: D-E-B-U-G the script by S-E-A-R-C-H-I-N-G for the error instead of sitting there and asking us things we cannot answer (because we do not see what is on your monitor or harddisk).
My guess is that one of the "exit" statements meant for for exiting "sqlplus" is in fact directed at the shell, which would make the shell (and the script) exit. This is only an unproven suspicion, though.
I hope this helps.
bakunin
Hi,
I have just removed the exit and it is working as expected. many thansks
Amazing deduction by @bakunin when the "full script" in post #6 does not contain the word "exit" .
Noticed that $NEWDIR is referenced three times before it has a value. The script as posted cannot be working correctly.