RE: How to send e-mail from Oracle

I used following script once when we had to monitor a critical application. Credits to asktom for the pieces, errors are on my account..

• connect as user with enough privs to do the below drop table "ABC".ddllog

l
/

create table "ABC".ddllog (

     ora_sysevent            varchar2(30)


   , ora_dict_obj_owner      varchar2(30)

   , ora_dict_obj_name       varchar2(30)

   , ora_dict_obj_type       varchar2(30)

   , timet                   date

   , machine                 VARHAR2(64)

   , terminal                VARCHAR2(16)

   , program                 VARCHAR2(64)

   , osuser                  VARCHAR2(30))


l
/

DROP TRIGGER "ABC".DDL_TRIGGER L
/

CREATE OR REPLACE TRIGGER "ABC".DDL_TRIGGER AFTER         DDL ON "XYZ".SCHEMA DECLARE
  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );   mesg VARCHAR2( 1000 );

--

begin
    insert into ddllog
    select ora_sysevent,ora_dict_obj_owner,
           ora_dict_obj_name,ora_dict_obj_type,
           sysdate,
           machine,terminal,program,osuser
      from v$session
     where audsid = sys_context( 'userenv', 'sessionid' );

    if ( sql%rowcount <> 1 )
    then
        raise_application_error
        ( -20001, 'Unable to id your session' );
    end if;

    conn:= utl_smtp.open_connection( '<your smtp server>', 25 );
    utl_smtp.helo( conn, '<your smtp server>' );
    utl_smtp.mail( conn, 'a_at_b.com' );
   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  'From: Andre <a_at_b.com>' || crlf ||
  'Subject: DDL detected in XYZ !' || crlf ||
  'To: andre <functionaldbaboxa_at_b.com>' || crlf ||
  '' || crlf ||
  ' Pls do SELECT * FROM "ABC".DDLLOG' || crlf ||
  '' || crlf ||
  '****************************************';
  utl_smtp.data( conn, mesg );
  utl_smtp.quit( conn );

end;
/
show error trigger ddl_trigger

Regards,
Andre van Winssen



-----Oorspronkelijk bericht-----
Van: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]Namens Niall Litchfield
Verzonden: dinsdag 8 maart 2005 19:05
Aan: Subbiah, Nagarajan
CC: List
Onderwerp: Re: How to send e-mail from Oracle


On Tue, 8 Mar 2005 12:42:53 -0500, Subbiah, Nagarajan
<Nagarajan.Subbiah_at_aetn.com> wrote:
> We are using both Oracle 9.2.0.5  and Oracle 8.1.7.4 on HP-UX 11.11. We
have
> the sendmail running on the server which I believe communicate to our
> Exchange mail server and  I am using the 'mailx' command on the unix
machine
> to send the e-mail.

Well then, I'd be looking at UTL_SMTP, trying to generalize the
package as much as possible and sending via the exchange mail relay
(never can understand why UNIX folk put a sendmail server everywhere).
I honestly thought that I'd put an example of this up on my site, but
I haven't. I will tomorrow (half-life 2 beckons now) , but in the
meantime google is your friend.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l