Re: Sending e-mail from the database through PL/SQL?!

In our last gripping episode "Eddie" <edawad_at_hotmail.com> wrote:
> Please help. My requirement is to send email from the database (Oracle
> 8.1.6) through Pl/SQL.
>
> I used both UTL_TCP and UTL_SMTP Oracle supplied packages. The
 procedure is
> getting compiled, but when I try to execute it, I get the following
 error:
> ORA-29540: class oracle/plsql/net/TCPConnection does not exist
>
> What is wrong? How can I add Java classes to already installed oracle?
>
> Thanks in Advance.
>
> Here is my procedure:
>
> create or replace PROCEDURE SEND_MAIL
>
> (sender IN VARCHAR2,
> recipient IN VARCHAR2,
> subj IN VARCHAR2,
> body IN VARCHAR2)
>
> crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
> mesg VARCHAR2(4000);
> mail_conn UTL_SMTP.CONNECTION;
> cc_recipient VARCHAR2(50) default 'name_at_domain.com';
> bcc_recipient VARCHAR2(50) default 'name2_at_domain.com';
>
> BEGIN
>
> mail_conn := utl_smtp.open_connection('mail.domain.com', 25);
>
> utl_smtp.helo(mail_conn, 'mailhost');
> utl_smtp.mail(mail_conn, sender);
> utl_smtp.rcpt(mail_conn, recipient);
> utl_smtp.rcpt(mail_conn, cc_recipient);
> utl_smtp.rcpt(mail_conn, bcc_recipient);
>
> mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf
||
> 'From: ' || sender || crlf ||
> 'To: ' || recipient || crlf ||
> 'Cc: ' || cc_recipient || crlf ||
> 'Bcc: ' || bcc_recipient || crlf ||
> 'Subject: ' || subj || crlf;
> mesg:= mesg || '' || crlf || body;
>
> utl_smtp.data(mail_conn, mesg);
> utl_smtp.quit(mail_conn);
>
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line(sqlerrm);
>
> END;
> /
>
>

You should be able to add the Java classes from the Oracle Universal Installer. Whilst the Installer is working watch closely for errors. I believe, if Java is installed, there may have been an error or two that prevented some of the classes from being installed (this may have been due to space restrictions).

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.