run shell script connecting to oracle from crontab


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting run shell script connecting to oracle from crontab
# 1  
Old 09-16-2009
run shell script connecting to oracle from crontab

Hi, I have problems with running shell script connecting to Oracle from the crontab. Here is the script:
Code:
#!/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

Last edited by Franklin52; 09-16-2009 at 06:21 AM.. Reason: Please use code tags!
# 2  
Old 09-16-2009
Just a few questions...

0. environment stuff (i.e. env)
1. what does the working command look like?
2. what error do you get?
3. what does crontab entry look like?
4. same uid for cron entry as command line test?
# 3  
Old 09-16-2009
HI, thanks....let's start step by step:
0.
Code:
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

Last edited by vgersh99; 09-16-2009 at 01:33 PM.. Reason: code tags, PLEASE!
# 4  
Old 09-16-2009
Possibly the program is dropping EVENT* in the current working directory of the cron rather than the directory pointed to by parameter $1 :

Code:
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?

Last edited by methyl; 09-16-2009 at 01:35 PM.. Reason: env
# 5  
Old 09-16-2009
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 [code] and [/code] 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  
Old 09-17-2009
Hi, I found the following erros in the mail:
SQL*Loader-500: Unable to open file (control_file.ctl)
SQL*Loader-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  
Old 09-17-2009
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

2. Shell Programming and Scripting

Connect Oracle using shell script at run time

Hi all, I am a newbie.....am jus trying to connect to oracle thro a script, but not thro giving the username/password@server_name directly like `$ORACLE_HOME/bin/sqlplus username/password@server_name In the above line, once it is connected to Oracle, it shud ask me the username and password... (4 Replies)
Discussion started by: kritibalu
4 Replies

3. Shell Programming and Scripting

Connecting to ORACLE through SHELL

Hi, I am trying to connect to oracle database (9.2.0.7.0) through Sun solaris. But I am unable to do so. The content of my script is as follows: Shell Sript: RESULT=`$ORACLE_HOME/bin/sqlplus schema/'pwd'@db << EOF set echo on; set feedback on; @/home/count/qry.sql` echo... (10 Replies)
Discussion started by: fidelis
10 Replies

4. Shell Programming and Scripting

run oracle query remotly with shell script

hello how to run shell script to excute oracle queries on remote db ? i have tried as following sqlplus -s user/password@remote_server "select query;" but not working also this one sqlplus -s user/password@remote_server `select query;` not working :( i add this line to run another... (4 Replies)
Discussion started by: mogabr
4 Replies

5. Shell Programming and Scripting

Connecting to oracle database from shell script

Hi all, I am satyakiran , i am new to the forum. i never done shell scripts for connecting to the data base (oracle) and fetching the data from the database( thru sql select statements ) i want to know 1. how to connect to the data base(oracle) using shell script 2. how to retrieve data... (8 Replies)
Discussion started by: satyakiran
8 Replies

6. Shell Programming and Scripting

How do i run a shell script without crontab.

Hi Folks, Could you please suggest me how to run a shell script on a solaris env without using crontab. I am actually trying to write a shell script which will grep "WORD" in the logfile andd sends a email.Thanks in advance. Thanks Sandeep. (3 Replies)
Discussion started by: sandeep anand
3 Replies

7. Shell Programming and Scripting

problem running shell script (for oracle export) in crontab

Hello Gurus, I've been tasked with solving a problem at my new job and I'm stumped. We've got a script that dynamically builds an oracle export parameter files and then runs export from the shell. it runs fine when using the shell, but will NOT run (fails in one spot everytime) when entered... (1 Reply)
Discussion started by: jsheehan223
1 Replies

8. Shell Programming and Scripting

Connecting to Oracle from Shell..What Am I doing Wrong

I have the following code in my shell script. . /apps/oracli/xxxxx/xxxx/oraEnv.9.2.0 sqlplus -s $DB_USER/$DB_PWD@$DB_NAME > xx.html <<EOF I am passing DBuser and DBname from a config file and the password from user. Config File : export DB_USER=xxx; export DB_NAME=xxx; I am getting... (1 Reply)
Discussion started by: dzyr4tt
1 Replies

9. Shell Programming and Scripting

run shell script from oracle store procedure

hi, this is urgent..can i run a shell script from store procedure without using java. (8 Replies)
Discussion started by: arnabb4u
8 Replies

10. UNIX for Advanced & Expert Users

Connecting to Oracle through unix shell scripts

Hi, Can some one help me in connecting to oracle through unix shell scripts with examples. Regards Narayana Gupta (1 Reply)
Discussion started by: guptan
1 Replies
Login or Register to Ask a Question