run shell script connecting to oracle from crontab

Shell Programming and Scripting
1

Hi, I have problems with running shell script connecting to Oracle from the crontab. Here is the script:

#!/sbin/sh
ORACLE_HOME=/opt/oracle/10g/
export ORACLE_HOME
if [ "$#" -eq 0 ]
then
  echo "Source path is not specified"
  exit
fi
/opt/oracle/10g/bin/sqlplus user/pass@testdb << EOF
truncate table table1;
commit;
EOF
cat $1/EVENT*| grep "Event:"|\
 /opt/oracle/10g/bin/sqlldr user/pass@testdb control=control_file.ctl data=\"-\" bad=bad_rec.bad log=log_file.log discard=discrd_file.discard

When I run the script manually, it works..!? But when I put it in the cron it does not work?? Any ideas please, where the problem can be?

apenkov

2

Just a few questions...

  1. environment stuff (i.e. env)
  2. what does the working command look like?
  3. what error do you get?
  4. what does crontab entry look like?
  5. same uid for cron entry as command line test?
3

HI, thanks....let's start step by step:
0.

PWD=/home/bgw
TZ=Europe/Sofia
LC_MESSAGES=C
HZ=100
HOSTNAME=homesol123
LD_LIBRARY_PATH=/opt/EMCpower/lib:/opt/VRTSvcs/EMC/lib
MANPATH=/usr/man:/opt/EMCpower/man
LD_LIBRARY_PATH_64=/opt/EMCpower/lib/sparcv9
LC_TIME=en_US.ISO8859-1
USER=user
MACHTYPE=sparc-sun-solaris2.9
MAIL=/var/mail/user
EDITOR=vi
LC_NUMERIC=en_US.ISO8859-1
LOGNAME=user
SHLVL=1
LC_CTYPE=en_US.ISO8859-1
SHELL=/bin/csh
HOSTTYPE=sparc
OSTYPE=solaris2.9
HOME=/home/bgw
TERM=ansi
PATH=/etc:/usr/bin:.:/opt/EMCpower/bin/sparcv9:/opt/VRTSvcs/EMC/bin:/etc/emc/bin
LC_MONETARY=en_US.ISO8859-1
LC_COLLATE=en_US.ISO8859-1
RAD_LOG_FACILITY=LOG_LOCAL0
_=/usr/bin/env

1.I'm not sure what I should provide here
2.I don't get any error, it just sleep...I add a simple command at the beginning - ls /home > list.txt and it works, but when it reach line
ORACLE_HOME=/opt/oracle/10g/ it goes to bed
3.crontab - 30 10 * * * /home/scripts.sh /home/dir
4.yes

apenkov

4

Possibly the program is dropping EVENT* in the current working directory of the cron rather than the directory pointed to by parameter $1 :

fi
cd $1
/opt/oracle/10g/bin/sqlplus user/pass@testdb << EOF

Also, do you need to set ORACLE_SID ?

Are there any errors in the unix mail file for the cron account?

5

always worth while searching the forums and looking at the FAQs.

---------- Post updated at 12:34 PM ---------- Previous update was at 12:32 PM ----------

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags

```text
 and 
```

by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums

6

Hi, I found the following erros in the mail:
SQLLoader-500: Unable to open file (control_file.ctl)
SQLLoader-553: file not found
SQL*Loader-509: System error: No such file or directory
It seems something wrong with the control file

BR
apenkov

7

I found that it's a good practise to provide the full paths if working in cron
You can use variables in your script which contain the paths but, you should provide them