Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP

RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP

From: Oracle General <OracleGeneral_at_mwd.dst.ca.us>
Date: Thu, 3 Aug 2000 08:29:05 -0700
Message-Id: <10578.113742@fatcity.com>


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.




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('<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;

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_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-L
Received on Thu Aug 03 2000 - 10:29:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US