DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Using UTL_SMTP

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.)

  3. #3
    Join Date
    Aug 2003
    Posts
    100

    reply

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

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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;

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Thanks Jurij, I like that!

  7. #7
    Join Date
    Aug 2003
    Posts
    100
    Can anyone answer my 2nd question? The one about the v$database table not being found?

    Steve

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  9. #9
    Join Date
    Aug 2003
    Posts
    100
    I can query the table just fine. Wouldn't that then mean that the user I am logged in as has the necessary permissions?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
  •  


Click Here to Expand Forum to Full Width