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

There's not much to share. Oracle 8.1.6 or later is required. First you need to make sure the Oracle JVM is installed, (it's installed by default with 8.1.6). If it's not installed then follow the instructions on Metalink Doc 105472.1 but you'll also need to follow Jared's suggestion:

"After running @?/javavm/install/initjvm, you will also need to run @?/rdbms/amdin/initplsj ( not documented )"

The UTL_SMTP package should already be installed on your system. Then just follow the instructions from Metalink Doc # 106513.1. BTW, they don't give good instructions on how to implement the plsql.jar. Here's what I did:

$ cd $ORACLE_HOME/plsql/jlib
$ loadjava -u system/password -v -r plsql.jar

The 106513.1. doc gives sample code to help you roll your own PL/SQL email package/procedure. That's all there is to it.

Have fun,
Steve Orr

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Nechama Glasrot
Sent: Wednesday, August 02, 2000 7:18 AM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Email from PL/SQL with Java - UTL_SMTP - UTL_TCP

hi ... so will you share? ... TIA

thank you ...

Nechama Glasrot
DBA
COC Automation Services - Palm Beach County nglasrot_at_co.palm-beach.fl.us
561-355-3467

>>> "Steve Orr" <sorr_at_arzoo.com> 08/01 8:34 PM >>>
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: Nechama Glasrot
  INET: Nglasrot_at_co.palm-beach.fl.us

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