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

From: Saurabh Sharma <saurabhs_at_fcsltd.com>
Date: Thu, 24 May 2001 23:19:44 -0700
Message-ID: <F001.0030E903.20010524231031@fatcity.com>

HI,
 i tried creating this procedure. but it results into compilation errors. says..

utl_tcp.connection must be declared, and so does for
utl_tcp.get_line and
utl_tcp.write_line

how do we fefine them. where is this package utl_tcp

pls explore.

thanks.
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 23, 2001 11:05 AM

>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Saurabh Sharma
  INET: saurabhs_at_fcsltd.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 May 25 2001 - 01:19:44 CDT