Solved

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

Posted on 2012-04-04
16
602 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 74

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 74

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 74

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 74

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
 
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 74

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 74

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 74

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

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ā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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