Using UTL_SMTP

I am trying to create a trigger that will send me an email should there ever be a problem with my database. Here's what I have so far:

CREATE OR REPLACE TRIGGER trg_mail_errors AFTER SERVERERROR ON DATABASE
declare
v_message VARCHAR2(1000);
v_sys_date VARCHAR2(30);
v_db_name VARCHAR2(10);
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mail_conn utl_smtp.connection;

BEGIN
mail_conn :=utl_smtp.open_connection('mail.test.com',25);
v_message:= 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss' )||crlf
||'FROM: [email protected]'||crlf||'Subject: Database Error'||crlf ||'To: [email protected]'
||crlf||''|| crlf;
SELECT name into v_db_name from v$database;
SELECT to_char(sysdate,'MM-DD-YYYY hh24:mi:ss') INTO v_sys_date FROM dual;
SELECT 'Sent from trg_mail_errors script. Database error has occured on... <'||v_db_name||'> on '||v_sys_date INTO v_message FROM DUAL;
utl_smtp.helo(mail_conn,'mail.test.com');
utl_smtp.mail(mail_conn,'[email protected]');
utl_smtp.rcpt(mail_conn,'[email protected]');
utl_smtp.data(mail_conn,v_message);
utl_smtp.quit(mail_conn);
END;

/

I'm having a few issues:
1) the message does show up, but the To: and Subject: are blank

2) in an effort to tell which instance has shutdown, I am using the line: SELECT name into v_db_name from v$database; When I use this line, and try to complile it, I get an error saying the v$database table does not exist.

Any help would be appreciated.

Thanks in advance.

SELECT 'Sent from trg_mail_errors script. Database error has occured on... <'||v_db_name||'> on '||v_sys_date INTO v_message FROM DUAL;

This is over-writing what you had put in v_message before.

(P.S. Why don't you write a simple proc for sending an e-mail, then call that from your trigger. You can the re-use it for other things. It's also easier to test.)

I'm not sure how to do that. Do you have a shell I could use?

Code:

---

PROCEDURE send_email (P_SENDER    in varchar2,
                      P_RECIPIENT in varchar2,
                      P_SUBJECT  in varchar2,
                      P_MESSAGE  in varchar2,
                        status      OUT    INTEGER)

IS

mailhost  varchar2(50);
mail_conn utl_smtp.connection;
reply    utl_smtp.reply;
crlf      varchar2(2):= CHR(13)||CHR(10);
mesg      varchar2(4000);

BEGIN

  status := 999;
  mailhost := RL_GET_CONFIGURATION('EMAIL_HOST');
  reply := utl_smtp.open_connection(mailhost,25,mail_conn);

  mesg:= SUBSTR(
                  'Date: '||to_char(sysdate,'dd Mon yyyy hh24:mi:ss' )||crlf||
                  'FROM: '||P_SENDER||' >'||crlf||
                'Subject: '||P_SUBJECT||crlf ||
                'To: '||P_RECIPIENT||crlf||
                ''||crlf||P_MESSAGE,
                1,4000);

  reply := utl_smtp.helo(mail_conn,mailhost);
  reply := utl_smtp.mail(mail_conn,P_SENDER);
  reply := utl_smtp.rcpt(mail_conn,P_RECIPIENT);
  reply := utl_smtp.data(mail_conn,mesg);
  reply := utl_smtp.quit(mail_conn);

  status := 0;

EXCEPTION
  WHEN OTHERS THEN
  rl_rolegoadm_pkg.errlog_autonomous('rl_send_email_pkg',
                          'rl_send_email',
                          to_char(reply.code),
                          reply.text,
                          SYSDATE);
  status := reply.code;

END send_email;

---

DaPi, just a small remark (because I know you are sensitive about the ability of the code to be seamlesly transporteable between various OS platforms ;) ).

You have declared variable CRLF in your procedure and assign the appropriate ASCII codes to it.

Code:

---

crlf      varchar2(2):= CHR(13)||CHR(10);

---

Now, diferent system have different ASCII characters to mark CRLF code sequence. Two most obvous examples would be UN*X versus Win systems - so if you simply transfer your procedure from Unix to Win (or vice versa) you'll have to make changes to it to work as desired.

To overcome this, Oracle offers a predefined variable for CRLF in package UTL_TCP. The variable name is -guess what- 'CRLF'! So instead of asigning CHR(13)||CHR(10) to your variable you'd better use UTL_TCP.CRLF and you'll never need to vory on what platform your code will run - it will always use the correct newline ascii equence.

Thanks Jurij, I like that!

Can anyone answer my 2nd question? The one about the v$database table not being found?

Steve

V$DATABASE is just a synonym to v_$database. You need permission on that object before you can use it in a procedure.

I can query the table just fine. Wouldn't that then mean that the user I am logged in as has the necessary permissions?

Quote:

---

Originally posted by ssmith001
I can query the table just fine. Wouldn't that then mean that the user I am logged in as has the necessary permissions?

---

Of course not, you're inside a procedure.

connect as sys
grant select on v_$database to the user that will own the procedure

Why even bother with querying something tzhat is not publicly accessible just to find out if the database is up? Why granting extra privileges for that, why not using something that is accessible to everyone? For example, with "select global_name from global_name;" will do just as well, without need for any additional grants and synonys.

Or simple query on DUAL table will do the same.

Quote:

---

Originally posted by ssmith001
2) in an effort to tell which instance has shutdown, I am using the line: SELECT name into v_db_name from v$database;

---

If the instance is down, how can you execute this? How can you send the e-mail from the server? Or have I totally misunderstood?

If you want to monitor db status, take a look at OEM or follow Jeff's lead and use some Perl scripts. (I started with this: http://www.amazon.com/exec/obidos/tg...43038?v=glance - it took a couple of days, but I now have a fine tool kit available.)

Quote:

---

Originally posted by DaPi
If the instance is down, how can you execute this? How can you send the e-mail from the server? Or have I totally misunderstood?

If you want to monitor db status, take a look at OEM or follow Jeff's lead and use some Perl scripts. (I started with this: http://www.amazon.com/exec/obidos/tg...43038?v=glance - it took a couple of days, but I now have a fine tool kit available.)

---

agreed on the EM part, why all this hoopla for something that oracle has already created and delivered. Mucho events in EM like the probe event will tell you if Oracle is accepting connections or not and send you a page/email

Quote:

---

Originally posted by DaPi
If the instance is down, how can you execute this? How can you send the e-mail from the server? Or have I totally misunderstood?

---

I assumed he is "pinging" the remote instance over database link. Say you have a job running on the database DB1 that queries something from the databases DB2, DB3, DB4, .... every x seconds/minutes/hours. And if something goes wrong with any of those remote databases it sends an alert email.

Quote:

---

Originally posted by jmodic
I assumed he is "pinging" the remote instance over database link.

---

I assumed he wasn't! I wonder which of us is right :)

I assumed it was part of a shutdown trigger or something... http://www.dbasupport.com/forums/http://www.dbasupport.com/forums/http://www.dbasupport.com/forums/http://www.dbasupport.com/forums/