Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2110
  • Last Modified:

Problem in sending mails from PL/SQL procedure using utl_tcp

Hi guys,

I am writing a stored procedure and inside the procedure after doing specific tasks I am notifying mails to the some mailds.
So I have a procedure named SendMail which will send the emails.  I use my unix server name as my mail server and port 25 for sending mails. i am using utl_tcp method.
But I get the following error when I run the procedure.(assume xyz is the valid server and tom is a valid mail id)

SQL> exec send_mail('tom@xyz.net','tom@xyz.net','hi test','mail from db');
BEGIN send_mail('tom@xyz.net','tom@xyz.net','hi test','mail from db');
END;

*
ERROR at line 1:
ORA-20000: Unable to send e-mail message from pl/sql because of: ORA-29260:
network error: TNS:no listener
ORA-06512: at "INV.SEND_MAIL", line 40
ORA-06512: at line 1

Pls tell me what could be the problem? Do we need to specify anything in the initora file for mailing option? or is there anything wrong in using the server or port?
IF YOU HAVE ANY SAMPLE WORKING CODE PLS REPLY/COPY ME THE SAME.
pls treat this as urgent. Anyother solution is also welcome.
0
krs_rama
Asked:
krs_rama
1 Solution
 
ljw87505Commented:
According to metalink, there is a bug in utl_tcp package that give the above symptoms when you use a hard IP address with leading zeros.

What exactly are you doing on line 40?
0
 
compcrazyCommented:
Well, I dont know why u are using the utl_tcp package for sending mails. You can use the utl_smtp, it will be much better. Here is the sample code which i am using to successfully send mails periodically to multiple receipients.

----- Sample Start------

CREATE OR REPLACE PROCEDURE db_send_email(v_email_address IN VARCHAR2, v_sender_address IN VARCHAR2) IS

--

--

MAILHOST VARCHAR2(30) := <ip of the mailserver in single quotes>;

--

-- v_letter_body varchar2(2000) := ' This is a test of the email application <br><br><br>';

--

MAIL_CONN utl_smtp.connection;

crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

v_stpt Number := 0;

v_endpt Number := 0;

mesg varchar2(1000);

 

BEGIN

MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(mailhost, 25);

UTL_SMTP.HELO(mail_conn, mailhost); -- connection to mail host

UTL_SMTP.MAIL(mail_conn, v_sender_address); -- who sends the email

--For n Email Addresses Comma Separated

LOOP

v_stpt := v_stpt + 1;

v_endpt := INSTR (v_email_address, ',', v_stpt, 1);

IF v_endpt = 0 THEN

UTL_SMTP.rcpt (mail_conn, SUBSTR (v_email_address, v_stpt));

EXIT;

ELSE

UTL_SMTP.rcpt (mail_conn, SUBSTR (v_email_address, v_stpt, v_endpt -v_stpt));

END IF;

v_stpt := v_endpt;

END LOOP;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||

      'From:'||v_sender_address|| crlf ||

      'Subject: Report as on date - '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||

      'To:'||v_email_address || crlf ||

     'Report Heading - '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )||crlf||

     '------------------------------------------------------------------------'||crlf||

     'Report Body Text 1 '||crlf||

     'Report Body Text 2'||crlf||      

     'Report Body Text 3';

utl_smtp.data( mail_conn, mesg );

UTL_SMTP.QUIT(mail_conn);

END db_send_email;


------- Sample End --------


Hope this helps. Let me know if u still find problems.
0
 
sujit_kumarCommented:
Also try this,

CREATE OR REPLACE PROCEDURE PR_SEND_MAIL(to_id VARCHAR2, text VARCHAR2) IS
    mailhost    VARCHAR2(64) := '100.62.75.98'; ---use your own  
      --mailhost    VARCHAR2(64) := 'mx2.xyz.com';
    sender      VARCHAR2(64) := 'xyz@xyz.com';
    --recipient   VARCHAR2(64) := 'xyz@xyz.com';
    mail_conn   utl_smtp.connection;
BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    --utl_smtp.rcpt(mail_conn, recipient);
      utl_smtp.rcpt(mail_conn, to_id);
-- If we had the message in a single string, we could collapse    
-- open_data(), write_data(), and close_data() into a single call to data().
    utl_smtp.open_data(mail_conn);
    --utl_smtp.write_data(mail_conn, 'This is a test message.' || chr(13));
    --utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13));
      utl_smtp.write_data(mail_conn, text);
    utl_smtp.close_data(mail_conn);
      
    utl_smtp.quit(mail_conn);
    EXCEPTION
      WHEN utl_smtp.invalid_operation THEN
      RAISE_APPLICATION_ERROR(-20111, 'INVALID OPERATION!!!! ('||SQLERRM||')');
      
      WHEN utl_smtp.transient_error THEN
      RAISE_APPLICATION_ERROR(-20111, 'SYSTEM BUSY TRY AGAIN LATER!!! ('||SQLERRM||')');
      
      WHEN utl_smtp.permanent_error THEN
      RAISE_APPLICATION_ERROR(-20111, 'CONTACT SYSTEM ADMIN!!!! ('||SQLERRM||')');
      
        WHEN OTHERS THEN
           -- Insert error-handling code here
           RAISE_APPLICATION_ERROR(-20111, 'MAIL NOT SENT!! ('||SQLERRM||')');
END;

begin
PR_SEND_MAIL('xyz@xyz.com', 'test message using UTL_SMTP!!!');
end;

begin
PR_SEND_MAIL('maheshkumart@hotmail.com', 'test message using UTL_SMTP!!!');
end;


http://services.msn.com/svcs/hotmail/httpmail.asp


-------alternatively-------------------------------
CREATE OR REPLACE PROCEDURE PR_SEND_TEXT_MAIL (T_TO VARCHAR2, T_SUBJECT VARCHAR2, T_TEXT VARCHAR2) IS
---created by sujit to send test mails using UTL_SMTP.
  c utl_smtp.connection;
 
  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
  BEGIN
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
  END;
 
BEGIN
  c := utl_smtp.open_connection('10.72.15.96');
  utl_smtp.helo(c, '180.62.45.69');
  utl_smtp.mail(c, 'xyz@xyz.com');
  utl_smtp.rcpt(c, 'xyz@xyz.com');
  utl_smtp.open_data(c);
  send_header('From',    '"Sujit" <xyz@xyz.com>');
  --send_header('To',      '"Recipient" <xyz@xyz.com>');  
  send_header('To',      T_TO);
  --send_header('Subject', 'Hello');  
  send_header('Subject', T_SUBJECT);
  --utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');  
  utl_smtp.write_data(c, utl_tcp.CRLF || T_TEXT);
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(c);
    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The quit call will raise an
              -- exception that we can ignore.
    END;
    raise_application_error(-20000,
      'Failed to send mail due to the following error: ' || sqlerrm);
END;

BEGIN
PR_SEND_TEXT_MAIL ('xyz@xyz.com', 'Sujit Test', 'Hi Ashish!!!');
end;
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now