Re: Stored Procedures and Email

Good point. There is a report on metalink about needing to run a script if things don't work properly.

On my machines, 8.1.7, I did not encourter this issue. Everything worked good straight off the CD.

Daniel Morgan

Eddie wrote:

> You may want to load the Java VM and enable Java in your Oracle database
> before UTL_SMTP could work.
>
> --
> Eddie
> --------------------------------------------------
> http://www.phoenicia21.com/
> FREE domain registration
> Affordable Cold Fusion/ASP Hosting
> Web design and development
> Oracle Consulting
> Useful Oracle Links at http://www.phoenicia21.com/WebServices/Oracle.htm
> --------------------------------------------------
> "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C6D7F9F.78BA7222_at_ci.seattle.wa.us...
> > I get the distinct impression that you may have the name of your mail
> server
> > incorrect. Check with your sysadmin.
> >
> > Daniel Morgan
> >
> >
> >
> > Adam Gent wrote:
> >
> > > Hi,
> > >
> > > I tried to use the code you supplied after modifying it a but and it
> came up
> > > with these errors:
> > >
> > > SQLWKS> call sendmail('a.l.gent_at_cs.cf.ac.uk','test');
> > > ORA-29540: class oracle/plsql/net/TCPConnection does not exist
> > > ORA-06512: at "SYS.UTL_TCP", line 533
> > > ORA-06512: at "SYS.UTL_TCP", line 199
> > > ORA-06512: at "SYS.UTL_SMTP", line 102
> > > ORA-06512: at "SYS.UTL_SMTP", line 121
> > > ORA-06512: at "SCM1ALG.SENDMAIL", line 11
> > > ORA-06512: at line 1
> > >
> > > Any ideas why these errors should happen?
> > >
> > > Thanks,
> > >
> > > Adam Gent
> > > "Bricklen Anderson" <bricklen13_at_hotmail.com> wrote in message
> > > news:b416ca2d.0202151000.19e08e81_at_posting.google.com...
> > > > I use a the following code (modified a bit, but should still work):
> > > >
> > > > CREATE OR REPLACE PROCEDURE sendmail (sender VARCHAR2, subject
> > > > VARCHAR2) IS
> > > > v_connection UTL_SMTP.CONNECTION;
> > > > v_reply UTL_SMTP.REPLY;
> > > > mesg VARCHAR2(999);
> > > > crlf VARCHAR2(2) := CHR(13)||CHR(10);
> > > > err_msg VARCHAR2(1200);
> > > > mailserver VARCHAR2(40);
> > > > error_type VARCHAR2(60);
> > > > email_address VARCHAR2(40);
> > > > BEGIN
> > > > FOR n IN (SELECT mailserver,email_address FROM contact_table) LOOP
> > > > v_connection := UTL_SMTP.OPEN_CONNECTION(n.mailserver,25);
> > > > v_reply := UTL_SMTP.HELO(v_connection,n.mailserver);
> > > > v_reply := UTL_SMTP.MAIL(v_connection,n.email_address);
> > > > v_reply := UTL_SMTP.RCPT(v_connection,n.email_address);
> > > > mesg :=
> > > > 'Date: '||TO_DATE(sysdate,'DD/MON/RRRR:HH24:MI:SS')||crlf||
> > > > 'From: '||sender||crlf||
> > > > 'Subject: '||subject||crlf||
> > > > 'To: '||n.email_address||crlf;
> > > > v_reply := UTL_SMTP.DATA(v_connection,mesg);
> > > > v_reply := UTL_SMTP.QUIT(v_connection);
> > > > END LOOP;
> > > > EXCEPTION
> > > > WHEN OTHERS THEN
> > > > err_msg := SQLERRM;
> > > > error_type := 'Exception error.';
> > > > INSERT INTO errorlog
> > > > (errorlogid,errorlogtype,errorlogname,errorlogtext,errorlogtimestamp)
> > > > VALUES
> > >
> (errorlog_errorlogid_seq.nextval,error_type,subject,err_msg,TO_DATE(sysdate,
> > > 'DD/MON/RRRR:HH24:MI:SS'));
> > > > COMMIT;
> > > > END sendmail;
> > > > /
> > > > show errors
> > > >
> > > >
> > > >
> > > > "Adam Gent" <a.l.gent_at_cs.cf.ac.uk> wrote in message
> > > news:<1013787834.30041.0_at_iapetus.uk.clara.net>...
> > > > > Hi,
> > > > >
> > > > > Is there anyway to send emails from within oracle stored procedures.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Adam Gent
> > > > > a.l.gent_at_cs.cf.ac.uk
> >
Received on Fri Feb 15 2002 - 17:52:09 CST