this will be stored in the logfile .
Now I have to generate a mail or alert if query output is found and mail the user with which query is failed and the output .
Thanks
Last edited by Scrutinizer; 05-07-2013 at 04:24 PM..
Reason: code tags
Please let us know where you stuck. Are you able to connect to oracle from unix?
How would you get the query? Do you have more than one query? What might change in the output and what is fixed?
Lastly, regarding email, Do you have email setup configured on your box?
Not clear.
I guess the SQL statement is run from within a shell script. Pls post (the relevant parts of) the script.
Is that output the only contents of the log file, or is it intermixed with other info, perhaps from the rest of the script?
Is the "good" output really zero (numeric 0) or is it empty?
Hi,
Please find the attached sample spool file.
I have mail configured in my unix server and my sql job run in unix environment and I am able to generate spool file from sql query.
My requirement is
if in the spool file contains any output of the query
Code:
SELECT *
FROM PH1.ENTITY_TALLY
WHERE period_id = 'M20120801' AND pc_id = 2
AND (sessions > hits
OR sessions = 0)
;
then I have to print/store the above query and output from the spool file.
like
PHP Code:
200>SELECT *
FROM PH1.ENTITY_TALLY
WHERE period_id = 'M20120801' AND pc_id = 2
AND (sessions > hits
OR sessions = 0)
;
M20120701 30 2 61 U 4124817886 mindsecretsexposed.com 2311516391 www.mindsecretsexposed.com null 1.00000000000000006352 1 0.08060000000000000000 3.00000000000000019056 96.00000000000000000000 0E-20 null null
M20120701 100 2 61 U 4124817886 mindsecretsexposed.com 2311516391 www.mindsecretsexposed.com null 1.00000000000000006352 1 0.01840000000000000000 3.00000000000000019056 96.00000000000000000000 0E-20 null null
M20120701 3 2 61 U 4124817886 mindsecretsexposed.com 2311516391 www.mindsecretsexposed.com null 1572.13351970000000000001 1 0.02490000000000000000 4716.40055910000000000003 96.00000000000000000000 0E-20 null null
if you see the other queries are not giving any output so I dont need to bother .
like for the below query count(*) is zero so no need to take any action
Code:
62>SELECT count(*) FROM
(
(SELECT * FROM PH1.instance_lvl_info
MINUS
SELECT * FROM PH1.QA_instance_lvl_info)
UNION ALL
(SELECT * FROM PH1.QA_instance_lvl_info
MINUS
SELECT * FROM PH1.instance_lvl_info)
)a;
0
74>SELECT bk_count, qa_count
FROM (SELECT COUNT (*) bk_count
FROM PH1.instance_lvl_info) a,
(SELECT COUNT (*) qa_count
FROM PH1.QA_lvl_info
) b
WHERE (bk_count = 0
or bk_count <> qa_count);
98>SELECT count(*) FROM
(
SELECT period_id, country_code_id, rn_prefix, instance_id,
gg_cookie_id, imrid, server_timestamp, tag_client_id,
tag_vc_id, tag_media_title, is_dav, tag_ad, count(*)
FROM PH2.lvl_info
WHERE period_id = 'M20130301'
GROUP BY period_id,
country_code_id,
rn_prefix,
instance_id,
gg_cookie_id,
imrid, server_timestamp,
tag_client_id,
tag_vc_id,
tag_media_title,
is_dav, tag_ad
HAVING count(*) > 1
) a;
0
Shell script logic
Hi
I have 2 input files like with file 1 content as (file1)
"BRGTEST-242" a.txt "BRGTEST-240" a.txt "BRGTEST-219" e.txt
File 2 contents as fle(2)
"BRGTEST-244" a.txt "BRGTEST-244" b.txt "BRGTEST-231" c.txt "BRGTEST-231" d.txt "BRGTEST-221" e.txt
I want to get... (22 Replies)
Hi Unix Experts,
Happy Morning to all !! :)
I am new to UNIX Shell Scripting and at my begineer level. To get acquainted to scripting, I am trying to create a script.
The details/requirements of my script was to create a script with month changing logic in it so that on every 6th Working... (3 Replies)
Hello All,
I was wondering if someone can help me to develop restart logic for a Korn Shell program (a master script) that I am developing. The program is design to invoke a series of child processes (Korn Shell Scripts) whose function is to create a series of load files on a specified mount... (0 Replies)
Hi,
I have the following in my shell script
sqlplus << EOF username/password @$SCRIPTS_DIR/refresh.sql > /home/oracle/sql/refreshsql.log
EOF
RETCODE=$?
if ; then
echo "ERROR: sqlplus command finished with error code {RETCODE}"
/usr/bin/mailx -s "REFRESHING FMLA CONTACT... (2 Replies)
I am going to take up a position in Data & Network Security.
I would need to write network shell scripts doing the following task:
Going to around 2000 servers and findout which groups has access to each servers and which ids are there in each group that has access.
I need to implement... (1 Reply)
Hello All,
Currently i am working on a logic in PERL scripting ...
But i am sort of stuck up, can any one please help.
Here goes.
1. Search for a pattern in a file
2. If the pattern matched lets say 10 lines
2.1 Reterive the first line and check for another pattern
2.1.1 if... (1 Reply)
Hello people,
I am new to shell scripting and hoping to get a quick answer to my query.
I am writing a simple script which needs to call an executale which generates an output file. Once the output file is generated I have to do a couple of more task with that file. The executable ideally... (1 Reply)
