Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script (with sql queries) not working using cron

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script (with sql queries) not working using cron
# 1  
Old 10-16-2012
Script (with sql queries) not working using cron
Hi all,

I have script, which performing sql queries and put output into file.
When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors...

I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error:

Code:
[root@dsafn1 20121016]#  more NOKRWW_PS_INTERSHO_MNC1_RAW-201210160900.csv 
ERROR:
ORA-12545: Connect failed because target host or object does not exist


SP2-0640: Not connected

I tried to define:
Code:
HOST = scv2db.r01.netact.azerfon.az

but got same error.

Here is part of script:

Code:
[root@dsafn1 ttg_project]# more run.sh 
#!/bin/bash  
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/10.2.0.2/client/lib:{/usr/lib/oracle/10.2.0.2/client/lib:{}:/opt/nokia/oss/lib:/opt/nokia/oss/tao/lib:/usr/lib:/usr/lib/or
acle/10.2.0.2/client/lib}:/opt/nokia/oss/lib:/opt/nokia/oss/tao/lib:/usr/lib:/usr/lib/oracle/10.2.0.2/client/lib
export LD_LIBRARY_PATH
PATH=$PATH:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/opt/nokia/oss/perl/bin:/opt/nokia/oss/tao/bin:/bin:/usr/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nok
ia/oss/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/opt/nokia/oss/perl/bin:/opt/nokia/oss/tao/bin:/bin:/usr/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nok
ia/oss/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/usr/sbin:/opt/nokia/oss/sbin:/root/bin:/usr/sbin:/opt/nokia/oss/sbin
export PATH
ORACLE_HOME=/usr/lib/oracle/10.2.0.2/client
export ORACLE_HOME
ORACLE_SID=oss
export ORACLE_SID
#
#---------- Creating folder in /root/ttg_project/output with current date --------------
#

cd /root/ttg_project/
mkdir ./output/20`date '+%y%m%d'`
chmod 777 ./output/20`date '+%y%m%d'`
#sleep 30
#
#---------- End of creating folder in /root/ttg_project/output with current date --------------
#


#
#---------- Running Queries with output to /root/project_ttg/output/Current_Date_Folder_Name -----------
#

/usr/lib/oracle/10.2.0.2/client/bin/sqlplus -s /nolog <<EOF > ./output/20`date '+%y%m%d'`/NOKRWW_PS_INTERSHO_MNC1_RAW-20`date --date="1 hour ago"  '+%y%m%d%H'`00.csv
connect rdr/rdr
set linesize 10000
SET PAGESIZE 50000
SELECT  
'RNC_ID'||'|'|| 
'WBTS_ID'||'|'||
..............................
..............................

Could you please let me know where the problem is?
Thanks in advance.
# 2  
Old 10-16-2012
Post what your cron job looks like.
# 3  
Old 10-16-2012
Here it is:

Code:
[root@dsafn1 ttg_project]# crontab -l
07,17,27,37,47,57 * * * * bash -lc /opt/nokiaoss/bin/dgrepchk.pl > /dev/null 2>&1 # HOST=scv2db ID=DBSC_101
07 * * * * /root/ttg_project/run.sh
50 * * * * /root/ttg_project/test.sh

/root/ttg_project/run.sh - is the script which I am trying to run.

---------- Post updated at 05:08 PM ---------- Previous update was at 11:09 AM ----------

I added some parameters and now, when trying to connect i am getting this error:

Code:
[root@dsafn1 20121016]# more NOKRWW_PS_INTERSHO_MNC1_RAW-201210161600.csv 
ERROR:
ORA-12545: Connect failed because target host or object does not exist


SP2-0640: Not connected
[root@dsafn1 20121016]#


This is whatI added tomy script:

Code:
#!/bin/sh  
LD_LIBRARY_PATH="/usr/lib/oracle/10.2.0.2/client/lib:{/usr/lib/oracle/10.2.0.2/client/lib:{}:/opt/nokia/oss/lib:/opt/nokia/oss/tao/lib:/usr/lib:/usr/lib/oracle/10.2.0.2/client/lib}:/opt/nokia/oss/lib:/opt/nokia/oss/tao/lib:/usr/lib:/usr/lib/oracle/10.2.0.2/client/lib"
export LD_LIBRARY_PATH
PATH="/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/opt/nokia/oss/perl/bin:/opt/nokia/oss/tao/bin:/bin:/usr/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/opt/nokia/oss/perl/bin:/opt/nokia/oss/tao/bin:/bin:/usr/bin:/usr/lib/oracle/10.2.0.2/client/bin:/opt/nokia/oss/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/usr/sbin:/opt/nokia/oss/sbin:/root/bin:/usr/sbin:/opt/nokia/oss/sbin"
export PATH
ORACLE_HOME=/usr/lib/oracle/10.2.0.2/client
export ORACLE_HOME
ORACLE_SID=oss
export ORACLE_SID
ORACLE_BASE=/usr/lib/oracle
ORACLE_PATH=/opt/nokia/oss/lib/sql
ORACLE_TERM=xterm
ORAENVFILE=/opt/nokia/oss/bin/omkoraenvmx.sh
SERVICE_NAME=oss
HOST=scv2db.r01.netact.azerfon.az
export SERVICE_NAME
export HOST
export ORACLE_BASE
export ORACLE_PATH
export ORACLE_TERM
export ORACLE_TERM

# 4  
Old 10-17-2012
You shouldn't have to add all the 'export' items to your shell script if you can run sqlplus from the command line.
You should be able to just put the following lines at the top of your shell script:
Code:
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi


Or run your cronjob like the following so that the script is aware of the user environment variables:
Code:
## minute,  hour   , day of the month, month of the year,  day of the week script
## (0-59)  (0-23)          (1-31)            (1-12)         (0-6 0-Sunday)
##--------------------------------------------------------------------------------------------------
00 08 * * 2 . ~/.bashrc; /path/to/script/script.sh


And per the error(ORA-12545: Connect failed because target host or object does not exist) you are getting, check your tnsnames.ora file and look at this link:
ORA-12545: Connect failed because target host ...
This User Gave Thanks to spacebar For This Post:
nypreH
# 5  
Old 10-17-2012
Hi Spacebar,

Its working fine, thank you very much!!! Smilie

All the best!!!
This User Gave Thanks to nypreH For This Post:
Gokulnath_1025
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused: (1 Reply)
Discussion started by: 24ajay
1 Replies

3. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

4. Shell Programming and Scripting

Executing set of sql queries from shell script

Hi All, I tried executing set of queries from shell script but not able to capture the input query in the log file. The code looks something similar to below sqlplus user/pwd@dbname << EOF > output.log $(<inputfile.txt) EOF The above code is capturing the output of queries into... (9 Replies)
Discussion started by: loggedin.ksh
9 Replies

5. Shell Programming and Scripting

Script not working in cron but working fine manually

Help. My script is working fine when executed manually but the cron seems not to catch up the command when registered. The script is as follow: #!/bin/sh for file in file_1.txt file_2.txt file_3.txt do awk '{ print "0" }' $file > tmp.tmp mv tmp.tmp $file done And the cron... (2 Replies)
Discussion started by: jasperux
2 Replies

6. Shell Programming and Scripting

Script is not working from cron while working manually

Hello, I am facing a very strange problem when I run my script manuallu ./Fetchcode which is using to connect with MKS integrity from linux end it workks fine but when I run it from cron it doesn't work.Can someone help me 1) How could I check my script when it is running from cron like... (3 Replies)
Discussion started by: anuragpgtgerman
3 Replies

7. Shell Programming and Scripting

Nested SQL queries within Shell script

Hi, Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows: my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g" connect... (2 Replies)
Discussion started by: shrutihardas
2 Replies

8. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you (1 Reply)
Discussion started by: user71408
1 Replies

9. UNIX for Dummies Questions & Answers

Sh Shell Script executing remote SQL queries

Hi there folks, I am trying to execute remote sql queries on an Oracle server. I would like to save the result of the executed sql queries on a text file, and send that text file as an attachment to an email address. Could anyone give me an idea on how the above could be achieved? Any help... (2 Replies)
Discussion started by: Javed
2 Replies

10. UNIX for Dummies Questions & Answers

SQL queries in background?

I have to query a DB2 database, and sometimes they take a long time to produce results. Can I run these queries in the background, and if so, where will the results appear? (1 Reply)
Discussion started by: jpprial
1 Replies
Login or Register to Ask a Question