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.