Home » SQL & PL/SQL » SQL & PL/SQL » Sending email through pl/sql procedure (Oracle IDS 10g,Windows XP)
Sending email through pl/sql procedure [message #386830] |
Tue, 17 February 2009 02:35  |
varosh81
Messages: 178 Registered: February 2008 Location: CHENNAI
|
Senior Member |
|
|
Hi All !
I wish to email the datas filled in a form through a pl sql procedure created in the database window..
I used the coding
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from abc',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25);
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');
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, '.');
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);
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from p
sqlerrm);
END;
set serveroutput on
/
Procedure created.
On executing, i got
SQL> exec send_mail(msg_to =>'aruns.swaminathan@gmail.com');
220 localhost.localdomain ESMTP Sendmail 8.13.1/8.13.1; Tue, 17 Feb 2009
14:15:33 +0530
250 localhost.localdomain Hello localhost.localdomain [127.0.0.1], pleased to
meet you
553 5.5.4 oracle... Domain name required for sender address oracle
503 5.0.0 Need MAIL before RCPT
503 5.0.0 Need MAIL command
500 5.5.1 Command unrecognized: "Subject: E-Mail message from RAJIV"
500 5.5.1 Command unrecognized: ""
PL/SQL procedure successfully completed.
SQL> exec send_mail(msg_to =>'aruns.swaminathan@gmail.com',-
> msg_text=>'Look I can send mail from plsql' -
> );
220 localhost.localdomain ESMTP Sendmail 8.13.1/8.13.1; Tue, 17 Feb 2009
14:16:13 +0530
250 localhost.localdomain Hello localhost.localdomain [127.0.0.1], pleased to
meet you
553 5.5.4 oracle... Domain name required for sender address oracle
503 5.0.0 Need MAIL before RCPT
503 5.0.0 Need MAIL command
500 5.5.1 Command unrecognized: "Subject: E-Mail message from abc"
500 5.5.1 Command unrecognized: ""
PL/SQL procedure successfully completed.
Pls help to overcome these errors.. And pls let me know how can a plsql procedure be called in forms..
Thanks and Regards
varosh
|
|
|
|
|
Re: Sending email through pl/sql procedure [message #387351 is a reply to message #386836] |
Wed, 18 February 2009 23:22   |
|
Hi,
Use This...
Create or Replace Function sendmail (
pSender VARCHAR2,
pRecipient VARCHAR2,
pSubject VARCHAR2,
pMessage VARCHAR2) return varchar2 IS
mailhost CONSTANT VARCHAR2(30) := 'mail.XYZ.com'; --UR Mail Server URL
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(1000);
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'Date: ' ||
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: <'|| pSender ||'>' || crlf ||
'Subject: '|| pSubject || crlf ||
'To: '||pRecipient || crlf || '' || crlf || pMessage;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, pSender);
utl_smtp.rcpt(mail_conn, pRecipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
return mesg;
EXCEPTION
WHEN UTL_SMTP.invalid_operation
THEN
DBMS_OUTPUT.put_line (' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.transient_error
THEN
DBMS_OUTPUT.put_line
(' Temporary problems with sending email - try again
later.');
WHEN UTL_SMTP.permanent_error
THEN
DBMS_OUTPUT.put_line (' Errors in code for SMTP transaction.');
END;
First Check whether u have utlmail.sql in your oracle server and prvtmail package,
Regards,
Ashoka BL
|
|
|
|
Re: Sending email through pl/sql procedure [message #387426 is a reply to message #387351] |
Thu, 19 February 2009 03:53   |
varosh81
Messages: 178 Registered: February 2008 Location: CHENNAI
|
Senior Member |
|
|
Hi !
The utlmail.sql is present in my oracle server.. what is the extension of the file prvtmail package, is it prvtmail.sql or prvtmail.plb ?
And is there any configuration to be done to send mail ?
Pls let me know about this..
Thanks and Regards
varosh
|
|
|
|
Re: Sending email through pl/sql procedure [message #387433 is a reply to message #387426] |
Thu, 19 February 2009 04:02  |
aviva4500
Messages: 122 Registered: July 2008 Location: bangalore
|
Senior Member |
|
|
@Varosh81,
Hope this could help you out.It worked for me.
SQL> DECLARE
2 l_mailhost VARCHAR2(64) := 'x.y.co.in';
3 l_from VARCHAR2(64) := 'name1@yahoo.co.in';
4 l_to VARCHAR2(64) := 'name2@yahoo.co.in';
5 l_mail_conn UTL_SMTP.connection;
6 BEGIN
7 l_mail_conn := UTL_SMTP.open_connection(('ip address of x.y.co.in'), 25);
8 UTL_SMTP.helo(l_mail_conn, 'ip address of x.y.co.in');
9 UTL_SMTP.mail(l_mail_conn, l_from);
10 UTL_SMTP.rcpt(l_mail_conn, l_to);
11 UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));
12 UTL_SMTP.quit(l_mail_conn);
13 END;
14 /
PL/SQL procedure successfully completed.
Thanks and Regards,
Hammer
[Updated on: Thu, 19 February 2009 04:05] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Feb 10 03:45:27 CST 2025
|