Message-Id: <10577.113612@fatcity.com> From: "MacGregor, Ian A." Date: Wed, 02 Aug 2000 08:50:52 -0700 Subject: RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP I use this facility and have no problems sending more than 4000 characters. I created a package called pl_sql_mail to better communicate with the Oracle delivered packages. This package will not fulfill everyone's needs; I use it to email myself reports. ---------------------------------------------------------------------------- ------------ create or replace package plsql_mail as procedure contact_smtpsrv(sender in varchar2, recipient in varchar2); procedure send_header (name in varchar2, header in varchar2); procedure send_body(mail_text in varchar2); procedure signoff_smtpsrv; end plsql_mail; package body plsql_mail as c utl_smtp.connection; procedure contact_smtpsrv(sender in varchar2, recipient in varchar2) is Begin c := utl_smtp.open_connection(''); utl_smtp.helo(c, ''); utl_smtp.mail(c, sender); utl_smtp.rcpt(c,recipient); utl_smtp.open_data(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN utl_smtp.quit(c); raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); end contact_smtpsrv; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) is BEGIN utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF); end send_header; Procedure send_body(mail_text in varchar2) is Begin utl_smtp.write_data(c, utl_tcp.CRLF || mail_text); end send_body; Procedure signoff_smtpsrv is Begin utl_smtp.close_data(c); utl_smtp.quit(c); end signoff_smtpsrv; end plsql_mail; ---------------------------------------------------------------------------- ----------- You can place a call to plsql_mail.send_body in a loop and send as many lines as necessary. Ian MacGregor Stanford Linear Accelerator Center ian@slac.stanford.edu -----Original Message----- From: Steve Orr [mailto:sorr@arzoo.com] Sent: Tuesday, August 01, 2000 5:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP OK, now I'm becoming an addict 'cause I just sent an email to myself from PL/SQL. Pretty cool. Now many of my UNIX scripts are going to take a back seat to PL/SQL. But I do generate some reports which probably exceed 4000 characters. Oh well... Thanks, Steve Orr -----Original Message----- Mengler Sent: Tuesday, August 01, 2000 1:54 PM To: Multiple recipients of list ORACLE-L Since I'm addicted to email notices, I configured my own instance with the UTL_SMTP package. I wrote some "wrapper" PL/SQL so that I can invoke a procedure & pass in three arguments; sender_name, recipient_list, & message (a text string). On my 8i instances I created DB triggers to send email messages upon startup & shutdown. On the V7 instances within the Unix scripts I invoke SQL*PLUS which runs a PL/SQL procedure that passes the same three arguments to MY V8.1.6 instance. Therefore all my DB's send me a message when they transistion state. All was done in PL/SQL; & no JAVA. In other words, I have a single DB that handles the SMTP interface for all the other DBs I support. My message length is limited to the maximum length of a single VARCHAR2 variable in PL/SQL, but it satisfies my needs so far. Steve Orr wrote: > > I need to implement email from PL/SQL triggers/procedures for an internet > application and I'm studying the different ways to skin this cat... > > I'm looking at the UTL_SMTP Oracle supplied package which is specifically > for emailing from PL/SQL. (Oracle 8.1.6+ only) It also requires the UTL_TCP > package which requires $ORACLE_HOME/plsql/jlib/plsql.jar which means it > requires the Java Virtual Machine on the Oracle Server. > (From thin client to fat server? :-() > > I understand that: it takes over an hour to run the initjvm.sql install > script; that Oracle recommends about 50MB for java_pool_size; and that 4000+ > objects will be created... > > (select count(*) from dba_objects where object_type like 'JAVA%'). > > So now I'm wondering about the overhead of the JVM and whether it's worth it > to go down this path just for email functionality. > > QUESTIONS: > 1) Can anyone share their experience with installing the JVM and give > feedback regarding the overhead. > > 2) Has anyone used UTL_SMTP to email from PL/SQL? > > 3) Are there any other approaches to email from PL/SQL that you would > recommend? > > 4) Should I learn Java? > > TIA! TIA! TIA! TIA! > > Hemorrhaging on the bleeding edge, > > Steve Orr > www.arzoo.com > > -- > Author: Steve Orr > INET: sorr@arzoo.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@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). -- Charlie Mengler Maintenance Warehouse charliem@mwh.com 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 There is a fine line between vision & hallucination. Don't cross it! -- Author: Charlie Mengler INET: charliem@mwh.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@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). -- Author: Steve Orr INET: sorr@arzoo.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@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L