Re: Stored Procedures and Email

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 - 15:37:30 CST