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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: smtp via PL/SQL
SERT INTO user_mailids VALUES( 'Belkis', 'Belkis',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Fabiano', 'Fabiano',
'[EMAIL PROTECTED]', 'group1' );
INSERT INTO user_mailids VALUES( 'Missao', 'Missao',
'[EMAIL PROTECTED]', 'group1' );
select
substr(USER_ALIAS,1,15),
substr(USER_FNAME,1,15),
substr(USER_EMAIL,1,30),
substr(USER_GROUP,1,15)
from user_mailids
/
BEGIN
mailit.mailusers('user1','user2','ALERT: Test being conducted',
'Notice to users: This is a test' );
mailit.mailgroups('group1,group2', NULL,'WARNING: Test being conducted',
'Notice to groups: This is a test' );
END;
/
BEGIN
mailit.mailusers('eca','eca',
'ALERT: Teste de e-mail enviado por package do Oracle',
'Pessoal :
Este é um teste de envio de e-mail através de uma
package do Oracle.
Conforme documentação encontrada no metalink esta
package é nova e roda
na versão 8.1.6 do Oracle.
Sem mais
Eriovaldo' );
END;
/
BEGIN
mailit.mailgroups('group1', NULL,
'ALERT: Teste de e-mail enviado por package do Oracle',
'Pessoal :
Este é um teste de envio de e-mail através de uma
package do Oracle.
Conforme documentação encontrada no metalink esta
package é nova e roda
na versão 8.1.6 do Oracle.
Sem mais
Eriovaldo' );
END;
/
BEGIN
mailit.mailgroups('group1', NULL,'WARNING: Test being conducted',
'Notice to groups: This is a test' );
END;
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - -
- -
Sample Output
-
Email 1:
Date: 25 Apr 00 14:48:00
From: Scott DBA <[EMAIL PROTECTED]>
To: User 1<[EMAIL PROTECTED]>
CC: User 2<[EMAIL PROTECTED]>
Notice to users: This is a test
Email 2:
Date: 25 Apr 00 14:48:05
From: Scott DBA <[EMAIL PROTECTED]>
To: User 1<[EMAIL PROTECTED]>, User 2<[EMAIL PROTECTED]>,
User 3<[EMAIL PROTECTED]>
Notice to groups: This is a test
Additional Search Words
---
using utl_smtp
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use.
end od the text ---
>From: "Steve McClure" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: smtp via PL/SQL
>Date: Thu, 17 Jan 2002 16:35:36 -0800
>
>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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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).
_
O MSN Photos é o jeito mais fácil de compartilhar e imprimir as suas fotos:
http://photos.msn.com.br/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Eca Eca
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: smtp via PL/SQL
Hey, Thanks all. Who would'a figured that Metalink could actually be usefull for researching this topic. When I sent the initial email I was actually thinking I had already searched for help on Metalink. Anyway It is certain that I hadn't, because there is actually a lot of helpfull info there. Thanks again, Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve McClure INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: smtp via PL/SQL
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) IS v_message varchar2(4000); mailhostVARCHAR2(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 => '[EMAIL PROTECTED]', -- multiple addresses not allowed from mail server -- relaying disabled --recipient => '[EMAIL PROTECTED],[EMAIL PROTECTED]', recipient => '[EMAIL PROTECTED]', 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" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/17/02 04:35 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
