Send a mail with Attachment using Oracle UTL_SMTP and Oracle Wallet Authentication example.
The following is the example of Oracle Wallet configuration information:
- Wallet Path: '
file:/usr1/oracle/wallets/mywallet
' - Wallet Password:
YourPasword
Send Mail with Attachment using Oracle UTL_SMTP and Oracle Wallet
PL/SQL Stored Procedure:
CREATE OR REPLACE PROCEDURE Send_EMail(i_empno IN emp.empno%type, i_Recipient IN VARCHAR2) IS v_Host CONSTANT VARCHAR2(100) := 'smtp.yourhost.com'; v_Port CONSTANT INTEGER := 587; v_Wallet_Path CONSTANT VARCHAR2(100) := 'file:/usr1/oracle/wallets/mywallet'; v_Wallet_Password CONSTANT VARCHAR2(100) := 'YourWalletPassword'; v_Domain CONSTANT VARCHAR2(100) := 'smtp.yourhost.com'; v_Username CONSTANT VARCHAR2(100) := 'UserName@example.com'; v_Password CONSTANT VARCHAR2(100) := 'YourPassword'; v_Sender CONSTANT VARCHAR2(100) := 'ExampleSender@example.com'; v_Recipient CONSTANT VARCHAR2(100) := i_Recipient; v_Subject VARCHAR2(1000) := 'This is subject.'; v_Body VARCHAR2(1000) := 'This is the bodyt text.'; l_Conn Utl_Smtp.Connection; l_Reply Utl_Smtp.Reply; l_Replies Utl_Smtp.Replies; v_Len NUMBER; v_Index NUMBER; l_Boundary VARCHAR2(50) := '----=*#foxinfotech#*='; v_Error_Message VARCHAR2(1000); v_Filename VARCHAR2(100); /* Fetch the PDF (BLOB) from EMP_DOCS table */ CURSOR Cur_empPdf IS SELECT pdf_blob Report FROM emp_docs WHERE empno = i_empno; l_Blob BLOB; BEGIN l_Reply := Utl_Smtp.Open_Connection(Host => v_Host, Port => v_Port, c => l_Conn, Wallet_Path => v_Wallet_Path, Wallet_Password => v_Wallet_Password, Secure_Connection_Before_Smtp => FALSE); l_Replies := Utl_Smtp.Ehlo(l_Conn, v_Domain); Utl_Smtp.Starttls(l_Conn); l_Replies := Utl_Smtp.Ehlo(l_Conn, v_Domain); l_Reply := Utl_Smtp.Auth(l_Conn, v_Username, v_Password, Utl_Smtp.All_Schemes); l_Reply := Utl_Smtp.Mail(l_Conn, v_Sender); l_Reply := Utl_Smtp.Rcpt(l_Conn, v_Recipient); l_Reply := Utl_Smtp.Open_Data(l_Conn); v_Filename := To_Char(SYSDATE, 'YYYYMMDDHH24MISS') || '.pdf'; Utl_Smtp.Write_Data(l_Conn, 'From: ' || v_Sender || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'To: ' || v_Recipient || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Subject: ' || v_Subject || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'MIME-Version: 1.0' || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Content-Type: multipart/mixed; boundary="' || l_Boundary || '"' || Utl_Tcp.Crlf || Utl_Tcp.Crlf); IF v_Body IS NOT NULL THEN Utl_Smtp.Write_Data(l_Conn, '--' || l_Boundary || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Content-Type: text/plain; charset="iso-8859-1"' || Utl_Tcp.Crlf || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, Utl_Tcp.Crlf || v_Body); Utl_Smtp.Write_Data(l_Conn, Utl_Tcp.Crlf || Utl_Tcp.Crlf); END IF; Dbms_Lob.Createtemporary(Lob_Loc => l_Blob, Cache => TRUE, Dur => Dbms_Lob.Call); FOR c IN Cur_empPdf LOOP l_Blob := c.Report; Utl_Smtp.Write_Data(l_Conn, '--' || l_Boundary || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Content-Type: ' || 'application/pdf' || '; name="' || c.Filename || '"' || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Content-Transfer-Encoding: base64' || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, 'Content-Disposition: attachment; filename="' || v_Filename || '"' || Utl_Tcp.Crlf || Utl_Tcp.Crlf); FOR i IN 0 .. Trunc((Dbms_Lob.Getlength(l_Blob) - 1) / 12000) LOOP Utl_Smtp.Write_Data(l_Conn, Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Dbms_Lob.Substr(l_Blob, 12000, i * 12000 + 1)))); END LOOP; Utl_Smtp.Write_Data(l_Conn, Utl_Tcp.Crlf || Utl_Tcp.Crlf); Utl_Smtp.Write_Data(l_Conn, '--' || l_Boundary || '--' || Utl_Tcp.Crlf); EXIT; END LOOP; l_Reply := Utl_Smtp.Close_Data(l_Conn); l_Reply := Utl_Smtp.Quit(l_Conn); EXCEPTION WHEN Utl_Smtp.Transient_Error OR Utl_Smtp.Permanent_Error THEN BEGIN Utl_Smtp.Quit(l_Conn); EXCEPTION WHEN Utl_Smtp.Transient_Error OR Utl_Smtp.Permanent_Error THEN NULL; END; Raise_Application_Error(-20000, 'Failed to send mail due to the following error: ' || SQLERRM); WHEN OTHERS THEN RAISE; END; /
hi, how do i send html instead of text?
v_Username and v_Password are to connect to smtp.yourhost.com?
What is the wallet for and what information does it contain?