Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP
Looks good.
You know, the only thing missing is the ability to attach files.
Now that all of Oracle's tools(Reports in particular) are designed to run on
the server for scheduling, caching etc., adding the ability to attach such
generated report pdf/html/rtf output to email from the database would
complete the long standing requirement.
-----Original Message-----
From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
Sent: Wednesday, August 02, 2000 10:04 AM
To: Multiple recipients of list ORACLE-L
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.
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('<your smtp server>'); utl_smtp.helo(c, '<your domain>'); 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;
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
Sent: Tuesday, August 01, 2000 5:34 PM
To: Multiple recipients of list ORACLE-L
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_at_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_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).
-- Charlie Mengler Maintenance Warehouse charliem_at_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_at_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_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). -- Author: Steve Orr INET: sorr_at_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_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). -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU 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-LReceived on Thu Aug 03 2000 - 10:29:05 CDT