OraFAQ Forum: Server Administration » Re: Sending mail in PL/SQL.

Home » RDBMS Server » Server Administration » Re: Sending mail in PL/SQL.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: Sending mail in PL/SQL. [message #371868]

Fri, 15 December 2000 03:59

sarath
Messages: 5
Registered: December 2000

Junior Member

1) Create a package that will hold the mail function
CREATE OR REPLACE PACKAGE APPL.mail_server as
procedure send_mail(dest in varchar2,subject in varchar2,mesg in varchar2);
procedure get_mail(dest out varchar2,subject out varchar2,mesg out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY APPL.mail_server as
procedure send_mail(dest in varchar2, subject in varchar2,mesg in varchar2) is
call_status integer;
begin
dbms_pipe.pack_message(dest);
dbms_pipe.pack_message(subject);
dbms_pipe.pack_message(mesg);
call_status := dbms_pipe.send_message('mail service');
end;
procedure get_mail(dest out varchar2,subject out varchar2,mesg out varchar2) is
call_status integer;
begin
call_status := dbms_pipe.receive_message('mail service');
dbms_pipe.unpack_message(dest);
dbms_pipe.unpack_message(subject);
dbms_pipe.unpack_message(mesg);
end;
END;
2) Create a PRO*C program that will be running on the server looking
for new mail
#include <stdio.h>
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char sub50;
char dst100;
char msg200;
char user20;
char password20;
char SCmdLine400;
EXEC SQL END DECLARE SECTION;
long SQLCODE;
main()
{
strcpy(user, "SCOOT");
strcpy(password, "TIGER");
EXEC SQL CONNECT :user IDENTIFIED BY :password;
EXEC SQL WHENEVER SQLERROR DO break;
while(1) {
/* Look for new message on the MAIL SERVICE pipe */
EXEC SQL EXECUTE
BEGIN
mail_server.get_mail(:dst,:sub,:msg);
END;
END-EXEC;
msg200='\0';
/* Call the Unix mail system */
sprintf(SCmdLine,"<PUT YOUR MAIL PROGRAM HERE> -s%s -e%s -t%s",sub,dst,msg);
system(SCmdLine);
}
}
3) Now you can send mail from SQL using the following syntax
SQL> exec mail_server.send_mail('f.debrus@intrasoft.be','Mail from Oradb','Hello World');

Report message to a moderator

Re: Sending mail in PL/SQL. [message #371869 is a reply to message #371868]

Fri, 15 December 2000 04:40

Marcus Fernando
Messages: 34
Registered: September 2000

Member

In Oracle 8i you can send mail using the packages DBMS_SMTP or DBMS_TCP. I didn't found how to send an atached file in the message. Do anybody knows ?
Follow the code using the DBMS_TCP package:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(-20000,
'Unable to send e-mail message from pl/sql');
END;
/
show errors

-- Examples:
set serveroutput on

exec send_mail(msg_to =>'orafaq@oraaq.org');

exec send_mail(msg_to =>'orafaq@orafaq.org',
msg_text=>'Look Ma, I can send mail from plsql'
);

Report message to a moderator

Re: Sending mail in PL/SQL. [message #374269 is a reply to message #371869]

Mon, 04 June 2001 02:02

Rajee
Messages: 17
Registered: June 2001

Junior Member

Hi,
Where can i find the details regarding DBMS_TCP and DBMS_SMTP packages. They are not available in Oracle 8i doc. Can u give some website address.
U have mentioned that mails can be sent using these packages. But in the example u have used UTL_TCP package. Please update me.

Regards,
rajee

Report message to a moderator

Re: Sending mail in PL/SQL. [message #374270 is a reply to message #371869]

Mon, 04 June 2001 02:05

Rajee
Messages: 17
Registered: June 2001

Junior Member

Report message to a moderator

Re: Sending mail in PL/SQL. [message #374271 is a reply to message #371869]

Mon, 04 June 2001 02:07

Rajee
Messages: 17
Registered: June 2001

Junior Member

Report message to a moderator

Previous Topic:	Re: What is use of 'raise' command in pl/sql
Next Topic:	Re: What is use of 'raise' command in pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 02:41:28 CDT 2024