Re: Stored Procedures and Email

I believe the following link may be of some use. I think that it is originally where I came across the code to send email etc. http://www.geocities.com/samoracle/swHowtoSendMail.htm

He gives some tips for setup of the java and some files to run, which is the cause of your error messages.

damorgan wrote:
>
> 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 - 22:19:16 CST