Re: How to send email from pl/sql in 806?

From: <OKhalid_at_lmkr.com>
Date: Tue, 22 May 2001 21:56:04 -0700
Message-ID: <F001.0030BAA1.20010522213523@fatcity.com>

try this procedure it works just replace the IP address in the line c := utl_tcp.open_connection('192.168.1.1', 25); with the IP address of u'r mail server

regards

Omar Khalid
Software Engineer
LMKResources
A LANDMARK AFFILIATE
(Formerly Mathtech Pakistan Pvt. Ltd)

18, F-8/3, Main Margalla Road,
Islamabad, Pakistan
Voice: 111-101-101*780
Fax: 92-051-2255989
Email: okhalid_at_lmkr.com
Web: www.lmkr.com

rem -----------------------------------------------------------------------

rem Filename: smtp.sql
rem Purpose: Send e-mail messages from PL/SQL

rem Notes:      From Oracle8i release 8.1.6 one can send e-mail messages
rem       directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem       packages. No pipes or external procedures required.
rem Date:       27-Mar-2000
rem Author:     Frank Naude (frank_at_ibi.co.za)
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (

  msg_from    varchar2 := 'oracle',
  msg_to      varchar2,
  msg_subject varchar2 := 'E-Mail message from your database',
  msg_text    varchar2 := '' )

IS
  c utl_tcp.connection;
  rc integer;
BEGIN
  c := utl_tcp.open_connection('192.168.1.1', 25); -- open the SMTP port 25 on local machine

  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'HELO 192.168.1.1');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));

rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from); dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to); dbms_output.put_line(utl_tcp.get_line(c, TRUE));

  rc := utl_tcp.write_line(c, 'DATA');                 -- Start message

body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));

  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);
  rc := utl_tcp.write_line(c, '.');                    -- End of message

body
dbms_output.put_line(utl_tcp.get_line(c, TRUE)); rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));

  utl_tcp.close_connection(c);                         -- Close the

connection
EXCEPTION
when others then

raise_application_error(-20000,'Unable to send e-mail message from pl/sql');
END;
/
show errors

Examples: set serveroutput on

exec send_mail(msg_to =>'Omar Khalid/IT/LotusCert/Pk'); exec send_mail(msg_to =>'omar_khalid_at_mathtechonline.com');

exec send_mail(msg_to =>'omar_khalid_at_mathtechonline.com',

msg_text=>'Look Ma I can send mail from plsql');

                                                                                       
        
                                                                                       
        
                                                                                       
        


                                                                                       
                   
                                                                                       
                   
                                                                                       
                   


                                                                                       
                            
                    Oracle DBA                                                         
                            
                    <acur8dba_at_yaho       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>       
                    o.com>               cc:                                           
                            
                    Sent by:             Subject:     How to send email from pl/sql in 
806?                        
                    root_at_fatcity.c                                                     
                            
                    om                                                                 
                            
                                                                                       
                            
                                                                                       
                            
                    05/23/2001                                                         
                            
                    11:35 AM                                                           
                            
                    Please respond                                                     
                            
                    to ORACLE-L

Hi,

I am aware that 817 supports UTL_SMTP for this same functionality. But how can one send email from pl/sql in 806?

thanx

Vicky D. Foster,
Senior Oracle DBA:
email: acur8dba_at_yahoo.com

Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Oracle DBA
  INET: acur8dba_at_yahoo.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: OKhalid_at_lmkr.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 Tue May 22 2001 - 23:56:04 CDT