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 Go to next message
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 #386835 is a reply to message #386830] Tue, 17 February 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use UTL_MAIL instead.

Regards
Michel
Re: Sending email through pl/sql procedure [message #386836 is a reply to message #386830] Tue, 17 February 2009 02:38 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
UTL_TCP is a low-level network package, where you would have to imlement the SMTP protocol yourself by reading and following the RFC 821 specifications.

I suggest you use UTL_MAIL instead.
Re: Sending email through pl/sql procedure [message #387351 is a reply to message #386836] Wed, 18 February 2009 23:22 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #387366 is a reply to message #387351] Thu, 19 February 2009 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why using utl_smpt when utl_mail is there and far easier to use?

Regards
Michel
Re: Sending email through pl/sql procedure [message #387426 is a reply to message #387351] Thu, 19 February 2009 03:53 Go to previous messageGo to next message
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 #387431 is a reply to message #387426] Thu, 19 February 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
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 ?

I don't know but "grep -i utl_mail" will give it.

Quote:
And is there any configuration to be done to send mail ?


Documentation is there to answer this question, read the link Thomas provided you.

Regards
Michel
Re: Sending email through pl/sql procedure [message #387433 is a reply to message #387426] Thu, 19 February 2009 04:02 Go to previous message
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

Previous Topic: Dynamic sql
Next Topic: clob data type
Goto Forum:
  


Current Time: Mon Feb 10 03:45:27 CST 2025