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


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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
crishna1Author Commented:

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

    v_connection   UTL_SMTP.connection;
    v_connection := UTL_SMTP.open_connection('');
    UTL_SMTP.helo(v_connection, '');
    UTL_SMTP.mail(v_connection, '');
    UTL_SMTP.rcpt(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);
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

crishna1Author Commented:
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?

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
crishna1Author Commented:
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?
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


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
crishna1Author Commented:
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!
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.
>>> 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)
crishna1Author Commented:
see the link...

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!
crishna1Author Commented:
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.
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.
crishna1Author Commented:
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!
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crishna1Author Commented:
Thank you, we will be upgrading to 11g and test. will open another question if any questions arise.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.