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