Solved

Stay Alert!

Posted on 2016-08-30
13
84 Views
Last Modified: 2016-09-06
I'm looking for ideas on generating an alert of Oracle 9i based on specific value entered into a specific table.

Any idea.

Thx
0
Comment
Question by:Roberto Madro R.
13 Comments
 
LVL 1

Expert Comment

by:jsaun
ID: 41776932
Put an insert/update trigger on the columns you are watching.  If the value gets entered (or attempted to be entered), then take the appropriate action.
0
 

Author Comment

by:Roberto Madro R.
ID: 41776953
The first part (creating the trigger) is easy, I'm looking for a method that, when the trigger is triggered that I get an email / alert or some other form of notification.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41776955
Do you want it anytime someone attempts to put that value into your table, or only if they commit the change?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Roberto Madro R.
ID: 41776963
Anytime a specific value is entered.
0
 

Author Comment

by:Roberto Madro R.
ID: 41776971
I should have added "every time a value is committed "
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41777018
that's tricky, because if I update a column to your magic value, I can have a trigger catch that and enqueue an email to be sent when you commit.

However, if I later update that column to some other value in the same transaction, that enqueued job won't know it shouldn't still send the email.

And what if I update the column to the magic value, and then you update it to something else?
Your session will wait on mine while I have the row(s) locked.  As soon as I commit I release my lock and your update will then apply immediately.  The email would still be sent but if you commit it won't be seen.


So, tracking committed values seems like it makes the most sense, but it's actually the hardest to track and potentially least reliable, unless you do periodic polling.   Because then you know the value has been committed and nobody has overwritten it yet.  Of course, as soon as you send the email, somebody could come along and update it, but there's no way to avoid that race condition.
0
 

Author Comment

by:Roberto Madro R.
ID: 41777054
I'll explain;

This is meant for an audit table that audits backend jobs, the column "SuccessfullyCompleted" of that table accepts the value "Y" or "N", if a job fails, I get a "N", else it's always "Y", if it's "N",  I'll examine that failed job and flip that value back to "Y" manually indicating that the issue has been addressed, so to answer your question, every single value in that table / column is "Y", except when there's an error.

I hope this helps.
Thx
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41777113
I question the wisdom of depending on an e-mail notification to advise you of a database event.  An e-mail message like this may help in some cases, but as others have pointed out, there are some problems with this approach.  I prefer to use a report (or query) that can be run on demand to check for any/all possible problems that meet the criteria you are interested in.

And, for a "flag" column like this in an Oracle table to indicate success or failure, I much prefer using a null to indicate success, and a non-null value (or values) to indicate: not started, in-process, error, etc.  This allows an index to be set up on this "flag" column that will help you very efficiently find the few exceptions (that have a non-null value) and the index will stay small even if the total number of records in the table is large, when most of them are expected to have the "success" value in this column.  This assumes that new records will have a non-null value in this column, and your process will update this "flag" column to null when the process finishes successfully (you likely have an update of this column at that point in time anyway).

I admit that a Y/N column sounds more intuitive.  But, using a null to indicate success and a non-null to indicate a problem when we expect most records to have the "success" value, is a way to take advantage of the way Oracle indexes work.  This helps you find the exceptions efficiently, without wasting index space to store all of the "success" values, since a single index value that is shared by the majority of records in a table, is not a good use of index space.
0
 

Author Comment

by:Roberto Madro R.
ID: 41777131
I got what I got, it's a proprietary / third-party system, and I'm managing things from the outside,  I can create a trigger or a scheduled sql statement to fetch out the "N" records, but I need a way to send an email / alert to people.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41777209
If it is a proprietary / third-party system, then you are probably going to violate the entire support agreement by creating a trigger! Triggers, especially unexpected ones, can be a disaster when upgrading for example.

Strongly recommend you do use a scheduled sql statement to fetch out the "N" records.
0
 

Author Comment

by:Roberto Madro R.
ID: 41777225
Well, I need to email / notify people, any thoughts on that?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41777246
Sending email isn't hard.

Just create a job that scans whatever table you're interested in monitoring for whatever values you're interested in notifying and email when necessary.

Here's a simple anonymous block to do what you need.
Alter the email header/body as needed.  Probably better to create it as a procedure rather than invoke it as is.  But that's basically just adding "create or replace procedure " to it with a name

DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    v_connection := UTL_SMTP.open_connection('your.email.server.com');
    UTL_SMTP.helo(v_connection, 'your.domain.com');
    UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
    UTL_SMTP.rcpt(v_connection, 'your_recipient@your.domain.com');
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data (v_connection,
                         'From: test from address' || UTL_TCP.crlf
                        );
    UTL_SMTP.write_data (v_connection, 'To: test to address' || UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection,
                         'Subject: test subject' || UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection, 'test body' || UTL_TCP.crlf);
    UTL_SMTP.close_data (v_connection);
    UTL_SMTP.quit (v_connection);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;


You might also want to review extended authentication in this article

http://www.experts-exchange.com/A_5915.html

or how to send attachments in your email
http://www.experts-exchange.com/A_7749.html
0
 

Author Closing Comment

by:Roberto Madro R.
ID: 41786391
The solution was adopted but not fully implemented yet (too many other moving pieces), will revisit if there's any issue.

Many Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question