-
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: steve.smith@xyz.com'||crlf||'Subject: Database Error'||crlf ||'To: steve.smith@xyz.com'
||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,'steve.smith@xyz.com');
utl_smtp.rcpt(mail_conn,'steve.smith@xyz.com');
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.)
-
reply
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jeff Hunter
-
I can query the table just fine. Wouldn't that then mean that the user I am logged in as has the necessary permissions?
-
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.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|