Home » SQL & PL/SQL » SQL & PL/SQL » Help with SMTP Mail Code (10g,Application server 10g)
Help with SMTP Mail Code [message #388277] Tue, 24 February 2009 02:06 Go to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Dear all,
I am unable to set directory path and filename in the follwoing SMTP mail with attachment code.

create or replace procedure pr_mail_attachment is
   v_From       VARCHAR2(80) := 'kashif.ali@xyz.com';
   v_Recipient  VARCHAR2(80) := 'kashif.ali@xyz.com';
   v_Subject    VARCHAR2(80) := 'test subject';
   v_Mail_Host  VARCHAR2(30) := '111.111.111.111';
   v_Mail_Conn  utl_smtp.Connection;
   crlf         VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

  utl_smtp.Mail(v_Mail_Conn, v_From);

  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

  utl_smtp.Data(v_Mail_Conn,
    'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: '   || v_From || crlf ||
    'Subject: '|| v_Subject || crlf ||
    'To: '     || v_Recipient || crlf ||

    'MIME-Version: 1.0'|| crlf ||  -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    'some message text'|| crlf ||  -- Message body
    'more message text'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    ' name="milestones.xlsx"'|| crlf ||
    'Content-Transfer_Encoding: 8bit'|| crlf ||
    'Content-Disposition: attachment;'|| crlf ||
    ' filename="milestones.xlsx"'|| crlf ||
    crlf ||
    'XLSX,file,attachement'|| crlf ||  -- Content of attachment
    crlf ||

    '-------SECBOUND--'      -- End MIME mail
  );

  utl_smtp.Quit(v_mail_conn);
EXCEPTION
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
    raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END pr_mail_attachment;


anyhelp will be appreciated.

Regards,
Kashif
Re: Help with SMTP Mail Code [message #388658 is a reply to message #388277] Wed, 25 February 2009 20:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
where is the proof that you cannot do what you say you cannot do?

Kevin
Re: Help with SMTP Mail Code [message #388709 is a reply to message #388658] Thu, 26 February 2009 00:05 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Thanks for reply,
i mean to say i have a directory by the name of temp on c drive and in that directory there is a file with the .xlsx extension so how do i mention in the above code to look in the c:\temp directory for xyz.xlsx.

Regards,
Kashif
Re: Help with SMTP Mail Code [message #388734 is a reply to message #388709] Thu, 26 February 2009 00:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
depends upon your version of the oracle rdbms you are using. Older versions allowed you to reference the directory by operating system name. Newer versions want you to create a directory object in the database and then reference that instead. It is possible to revert back to the default.

In any event, the documentation for text_io and utl_file on the database side give ample examples of this. Just do a google and find them.

Good luck, Kevin
Re: Help with SMTP Mail Code [message #388746 is a reply to message #388277] Thu, 26 February 2009 01:22 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Thanks for your kind reply

I will certainly do that.

Regards,
Kashif
Re: Help with SMTP Mail Code [message #388767 is a reply to message #388709] Thu, 26 February 2009 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Would this C drive, directory and file be on your local computer, rather than the server?
Re: Help with SMTP Mail Code [message #388787 is a reply to message #388277] Thu, 26 February 2009 04:05 Go to previous message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Rite now i am trying this on server but eventualy this will be used by the client to upload attachment from their local hard drives.

Any idea how to do this

Regards,
Kashif
Previous Topic: Performance of cursor
Next Topic: To_number function
Goto Forum:
  


Current Time: Sat Dec 03 18:18:45 CST 2016

Total time taken to generate the page: 0.09231 seconds