Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Issue with SQL UNIX shell script -

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Issue with SQL UNIX shell script -
# 1  
Old 11-22-2013
Issue with SQL UNIX shell script -
Hi all

I am writing a shell script which will run a select query from a table . If the ouput is not 500 - then send a email to the team saying there is a error . But i am not sure how to redirect this output of the select query to the log file -
Code:
#!/bin/ksh
sqlplus /nolog
conect user/pass@host
select * from table;
500
exit
COUNT=`grep  "500" sql log` 
if [ $COUNT = 500 ];
then
echo " up " | mailx -r s@r.com -s "success" a@abc.com
else 
echo "not up" | mailx -r s@r.com -s "fail" a@abc.com
fi

Please suggest how to get the select query output in SQL log ? Above is the whole code of the shell script .
Last edited by vbe; 11-22-2013 at 05:08 AM.. Reason: code tags
# 2  
Old 11-22-2013
You need to set this up with redirection and a 'here' document.

Try something like this:-

Code:
#!/bin/ksh
sqlplus -S <<-EOSQL | read COUNT
user/pass@host
set pagesize 0
select * from table;
EOSQL

if [ $COUNT = 500 ];
then
echo " up " | mailx -r s@r.com -s "success" a@abc.com
else 
echo "not up" | mailx -r s@r.com -s "fail" a@abc.com
fi

The set pagesize 0 will turn off headings and page splitting. I'm assuming that you are sure you have only a single one-column record.

The -S is a silence flag, so you don't get all the version information and other messages from a normal connection.

I'd avoid /nolog as you do want to log on anyway.


I hope that this helps.


Robin
Liverpool/Blackburn
UK
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Call sql script from UNIX shell script

I know this question is out there in many forums, but I tried all the combinations in vain. I'm basically trying to call a sql script from a shell script. Below is my sql script (plsql.sql) DELCARE v_empno NUMBER := '&empno'; BEGIN select ename,sal from emp where empno = v_empno;... (3 Replies)
Discussion started by: FName_LName
3 Replies

2. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

3. 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

4. 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

5. Shell Programming and Scripting

Check SQL through UNIX shell script

I use below command to log in to oracle database sqlplus username/passwordWhen i enter into sql prompt i press quitIf there are any errors it will show just below the output of sqlplus username/password. The errors generally start with keyword ORA can we do this by using a unix shell script.?... (4 Replies)
Discussion started by: Nakul_sh
4 Replies

6. UNIX for Dummies Questions & Answers

How to convert R$Timestamp in Sql*Plus within a UNIX Shell Script?

I need to compare a R$Timestamp field sql within a Unix Shell Script. In straight SQL the following code works fine: Table Name: LL_UNIT_TRANSACTION UT Field: R$Timestamp Where TRUNC(UT.R$Timestamp) >= TRUNC(SYSDATE -7) the following returns no data within the Unix Shell Script... (2 Replies)
Discussion started by: Dapconsult
2 Replies

7. UNIX for Advanced & Expert Users

Use of Oracle pl/sql in UNIX shell script

Hi, I have basic knowledge on how to write pl/sql code inside shell script. I am looking for more advance thing. Is there any book for that which can just talk about how to write more advance plsql code inside shell script. Please help Thanks!!!!!! (1 Reply)
Discussion started by: diehard
1 Replies

8. UNIX for Dummies Questions & Answers

executing SQL query using unix shell script

I want to perform few post-session success tasks like update a status to 'true' in one of the sql database table, update date values to current system date in one of the configuration table in sql. How do i achieve this in a post session command?syntax with example will be helpful. (3 Replies)
Discussion started by: nathanvaithi
3 Replies

9. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

10. UNIX for Dummies Questions & Answers

sql query results in unix shell script

Hi I want to get the a field from a SQL query into unix shell script variable. the whole situation is like this. 1. Opened a cursor to a table in DB2 databse. 2. Fetching individual rows with the help of cursor. 3. Each row has 4 fields. I want each of the field in individual shell... (1 Reply)
Discussion started by: skyineyes
1 Replies
Login or Register to Ask a Question