Re: smtp via PL/SQL

From: <Jared.Still_at_radisys.com>
Date: Fri, 18 Jan 2002 13:02:25 -0800
Message-ID: <F001.003F3EDE.20020118095120@fatcity.com>

Steve,

I just happen to have an example script. :)

Here's a wrapper and a test.

First, the wrapper:

create or replace PROCEDURE sendmail (

   sender      IN VARCHAR2,
   recipient   IN VARCHAR2,
   subject     IN VARCHAR2,
   message     IN VARCHAR2)

v_message varchar2(4000);
mailhost VARCHAR2(30) := 'mail.radisys.com'; mail_conn utl_smtp.connection;

lf VARCHAR2( 1 ):= CHR( 10 );

BEGIN v_message := 'Subject: ' || subject || lf || message; mail_conn := utl_smtp.open_connection(mailhost, 25);

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, sender);
   utl_smtp.rcpt(mail_conn, recipient);
   utl_smtp.data(mail_conn, v_message);
   utl_smtp.quit(mail_conn);

--EXCEPTION --WHEN OTHERS THEN

Handle the error
--raise;

END;
/

show errors procedure sendmail

create public synonym sendmail for sys.sendmail;

grant execute on sendmail to public;

And here is the test:

declare

v_database global_name.global_name%type; begin

   select global_name into v_database
   from global_name;
   sendmail(

      sender => 'jkstill_at_radisys.com',


      -- multiple addresses not allowed from mail server

      -- relaying disabled

      --recipient => 'jared.still_at_radisys.com,jkstill_at_cybcon.com',

      recipient => 'jared.still_at_radisys.com',
      subject => 'test  from utl_smtp at radisys',
      message => 'this is a test from utl_smtp@'|| v_database);

end;
/

Please feel free to change the recipient addresses. :)

If you need to install utl_tcp and utl_smtp:

$ORACLE_HOME/rdbms/admin/utltcp.sql
$ORACLE_HOME/rdbms/admin/prvttcp.plb 
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtsmtp.plb

Jared

"Steve McClure" <smcclure_at_usscript.com>
Sent by: root_at_fatcity.com
01/17/02 04:35 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        smtp via PL/SQL

I am digging into the docs I can find on utl_smtp and utl_tcp, but I am really not finding much. I have Oracle's package reference docs, but that doesn't shed all that much light on the subject. I am pretty well a newbie
to tcp and smtp.

Geeze all that talking and no question yet. Can anyone recommend a book or
white paper on implementing 'email' from within an Oracle database? I have
downloaded some sample code from Orafaq, and actually gotten it working on our db. I would just like to actually understand what I am doing, and expand on what we have.

Steve McClure

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve McClure
  INET: smcclure_at_usscript.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 18 2002 - 15:02:25 CST