Solved

email notification -- table record count -- Oracle 9i

Posted on 2012-04-04
16
583 Views
Last Modified: 2012-06-27
Hi,

We have an application that collects data from , say 50 input points and writes into a Oracle table. This means that every hour/ day at a given time 50 records are writtem into a table says AXIS. Is there a way a notification email be sent, if it misses any of these records?

Ex: today only 48 records got inserted , which means collection from 2 points is missing i.e 2 records.

In the past one of the experts assisted with this on a Oracle 11g database, now i need it on a Oracle 9i database.
I do not see  the following in Oracle 9i..

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

also, i do not see SMTP_OUT_SERVER init parameter in 9i.

Please let me know what other information is required and i will try to provide it.
If there is a way to accomplish this in Oracle 9i, i would need someone to provide the solution.

many thanks!
0
Comment
Question by:crishna1
  • 8
  • 7
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 37808403
utl_mail doesn't exist until 10g.

in 9i you'll have to use utl_smtp

This article explains how to send an email with an attachment of your data.

http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html
0
 

Author Comment

by:crishna1
ID: 37808491
sdstuber,

Thank you very much for the quick responce.
I look at the link you provided, for someone like me that is a lot of information.

In th past , i was suggested

1) to run the above mentioned scripts , two of them
2) change a init.ora paarameter( SMTP_OUT_SERVER ) and
3) make sure to esatblish communication between the Database Server and the Email         server ( network folks took care of this)
4) and a customised Stored Proc has been provided to get the required info from the AXIS table.

On these lines , is there anything you can provide?
Appreciate it! Thanks!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37808509
for 10g you could have run the scripts,  for 9i,  there is no utl_mail at all  and you shouldn't have to install anything.
similarly since utl_mail doesn't exist you don't have to configure an init.ora parameter

the only you thing you need is the pl/sql code.
here's a stripped down version you could put into a stored procedure


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);   --- put your real content here
    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;
0
 

Author Comment

by:crishna1
ID: 37808660
Thank you! How does this procedure know which table to check for data? In this case the table name is AXIS.

Alos, i see a comment in the procedure "---put your real content here", is that a comment for me or was it something not related? If it is , what real content are we talking about?

thanks!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37808737
the procedure doesn't know anything about your table

the comment was for you,  put whatever logic and rules you use on the axis table there and use UTL_SMTP.write_data  to put the results of your code into the email body
0
 

Author Comment

by:crishna1
ID: 37808749
haha...wish i could "put whatever logic and rules" ;-).

basically what is required in the email notification is mentioned in the original question, can you plz re-write/tweak your procedure based on that?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37809074
no, I can't really.  That procedure shows how to do the emailing.  I have no idea what to do with your axis table to generate the content

I guess you could do maybe something like


select count(*) into v_cnt from axis;

if v_cnt < 50 then  

<< do the email stuff above>>

end if;

change the

UTL_SMTP.write_data (v_connection, 'test body' || UTL_TCP.crlf);   --- put your real content here

to

UTL_SMTP.write_data (v_connection, 'Only ' || v_cnt || ' records written to AXIS table.' || UTL_TCP.crlf);  


if you need something more specific than that, you will need to talk to the team that supports your application to see how to extract any other pertinent information and it'll likely be beyond the scope of a Q&A forum
0
 

Author Comment

by:crishna1
ID: 37812270
there is no support team , the original question itself is quite clear , email record count of a table. will see if someone can provide that , hopefully someone must have already used such procedure.

i will keep looking on the net/forums and will post if i find something.

thanks anyways!
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.

 
LVL 20

Expert Comment

by:gatorvip
ID: 37812878
sdstuber already did most of the work on this thread, I only have a slight addition to make.

If you already have an established "procedure" in place on an 11g database, can you not adjust it for a 9i database? The emailing function should be the only difference and sdstuber already provided that in answer 37808509.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37812930
>>> In the past one of the experts assisted with this on a Oracle 11g database,

can you post a link to the relevant question?
as gatorvip noted, if you already have a procedure that does what you want,  it should be a simple matter of replacing the ult_mail call in the 11g code with the utl_smtp code I've posted above.



>>> original question itself is quite clear , email record count of a table

if a count is really all you need, the instructions for that are above.

you'll have to put in your real server, email addresses, subject, etc instead of the stubs above.

if it doesn't work, post what you tried (mask the email addresses first though)
0
 

Author Comment

by:crishna1
ID: 37814327
see the link...

http://www.experts-exchange.com/Database/Oracle/Q_27430570.html


I have not tried it either in 11g nor 9i. The procedure in the link is not complete.
slightwv-- offered to re-write the procedure at that time , but we were side tracked by another issue and did not pursue it.

Thanks again!
0
 

Author Comment

by:crishna1
ID: 37825866
looks like upgrading the database to 10g might be a better option, given that the application will work/supported with 10g. If it does, my guess is the steps i mentioned for 11g will work for 10g.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37826156
I would recommend upgrading to 11g.


But,  that's totally unnecessary.  Use utl_smtp as shown above.  utl_mail is easy for trivial stuff, but in the long run getting used to using utl_smtp is better and, really, it's not that hard.
0
 

Author Comment

by:crishna1
ID: 37828250
ok, i mentioned 10g since i definetely know the application we are using does not support/work with 11g. Does 11g have more email related functionality compred to 10g? or were you suggesting 11g since it is the latest?

if it is not too much to ask, can you please list in steps (like i posted in  37808491) for making this work in 9i.

Thank you!
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37828319
1) modify the code I posted above to use the real addresses, real servers, real subject, putting in the content needed where appropriate

2) run that code

there are no install steps or anything else like you put in your example.  Just the code.


yes,  11g has more email functionality than 10g, but only with respect to utl_smtp.


I'm not recommending 11g solely for utl_smtp though.  10g is nearing the end of its supported lifespan.  If you're going to upgrade, go to the latest 11gR2.
0
 

Author Closing Comment

by:crishna1
ID: 37833822
Thank you, we will be upgrading to 11g and test. will open another question if any questions arise.
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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now