Re: Stored Procedures and Email

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:12:13 CST