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

Posted on 2012-04-04
Last Modified: 2012-06-27

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!
Question by:crishna1
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
LVL 74

Expert Comment

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.

Author Comment

ID: 37808491

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

Expert Comment

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

    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);
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

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?

LVL 74

Expert Comment

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

Author Comment

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

Expert Comment

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


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

Author Comment

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

Expert Comment

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

Expert Comment

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)

Author Comment

ID: 37814327
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!

Author Comment

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

Expert Comment

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.

Author Comment

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

Accepted Solution

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.

Author Closing Comment

ID: 37833822
Thank you, we will be upgrading to 11g and test. will open another question if any questions arise.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 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…
Suggested Courses

632 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