Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2012-04-04
Medium Priority
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
  • 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('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.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);

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

927 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