Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Sending e-mail from the database through PL/SQL?!

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

From: Ezr Vinh <d_a_p_at_my-deja.com>
Date: Tue, 12 Dec 2000 17:19:47 GMT
Message-ID: <915mn9$mns$1@nnrp1.deja.com>

I haven't tried mucking about with the UTL_TCP or UTL_SMTP packages yet, but from what you've written it would appear that this is pretty hairy way to do something that ought to be fairly simple. How about this alternative:

Have your PL/SQL app write the intended email messages to a table, and write a little shell-script (or Pro*C) daemon to periodically check the table and dispatch the emails. A variation on this theme would be to have Oracle write the email message to a text file using the UTL_FILE package and have the daemon pick it up and dispatch it from there. I've  done lots of this sort of thing with KSH scripts with great success. It's quick and efficient, and doesn't involve hacking up your Oracle installation with additional packages.

-Dave

In article <GXaZ5.1797$TQ5.224753_at_dfiatx1-snr1.gtei.net>,   "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;
> /
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 11:19:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US