Send email with attachments in Oracle D2k, Oracle Forms 10g, Oracle Forms 6i.
Create a directory object Email_Files in current schema from where you want to send emails.
Create or Replace directory email_files as 'c:\urdir';
PROCEDURE Send_Email(ifrom In varchar2, ito In varchar2, icc In varchar2, ifile In varchar2, ifile2 In varchar2,
isubject In varchar2, imsg In varchar2, isrl_no In number)
Is
/*utl_smtp related variables. */
vfile varchar2(100);
v_connection_handle Utl_Smtp.CONNECTION;
v_from_email_address VARCHAR2(1000) := ifrom;
v_to_email_address VARCHAR2(1000) := ito;
v_smtp_host VARCHAR2(100) := 'mail.urdomain.com'; --your mail server
v_subject VARCHAR2(1000) := isubject;
l_message VARCHAR2(2000) := imsg;
nls_charset varchar2(255);
/* this send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name In VARCHAR2, pi_header In VARCHAR2) As
BEGIN
Utl_Smtp.WRITE_DATA(v_connection_handle,
pi_name || ': ' || pi_header || Utl_Tcp.CRLF);
END;
BEGIN
Select Value
Into nls_charset
From NLS_DATABASE_PARAMETERS
Where parameter = 'NLS_CHARACTERSET';
/*utl_smtp related coding. */
v_connection_handle := Utl_Smtp.OPEN_CONNECTION(v_smtp_host, 25);
Utl_Smtp.EHLO(v_connection_handle, v_smtp_host);
utl_smtp.command(v_connection_handle, 'auth login');
utl_smtp.command(v_connection_handle,utl_encode.text_encode('[email protected]', nls_charset, 1));
utl_smtp.command(v_connection_handle, utl_encode.text_encode('urpassword', nls_charset, 1));
Utl_Smtp.Mail(v_connection_handle, v_from_email_address);
Utl_Smtp.RCPT(v_connection_handle, v_to_email_address);
IF icc Is Not Null Then
Utl_Smtp.RCPT(v_connection_handle, icc);
END IF;
Utl_Smtp.OPEN_DATA(v_connection_handle);
send_header('From', ifrom);
send_header('To', ito);
IF icc Is Not Null Then
send_header('Cc', icc);
END IF;
send_header('Subject', v_subject);
IF IFILE Is Null And IFILE2 Is Null Then
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Type: text/html;' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, l_message || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
END IF;
--mime header.
IF ifile Is Not Null Then
For i In 1..2 LOOP
/*preparing the lob from file for attachment. */
/*lob operation related varriables */
IF i = 1 Then
vfile := ifile;
Else
IF ifile2 Is Null Then
EXIT;
Else
vfile := ifile2;
END IF;
END IF;
DECLARE
v_src_loc BFILE := Bfilename('EMAIL_FILES', Nvl(vfile, 'temp'));
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := Empty_Blob;
l_blob_len INTEGER;
v_amount INTEGER;
BEGIN
Dbms_Lob.OPEN(v_src_loc, Dbms_Lob.LOB_READONLY); --read the file
Dbms_Lob.CREATETEMPORARY(l_blob, TRUE); --create temporary lob to store the file.
v_amount := Dbms_Lob.GETLENGTH(v_src_loc); --amount to store.
Dbms_Lob.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- loading from file into temporary lob
l_blob_len := Dbms_Lob.getlength(l_blob);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/mixed; ' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
' boundary= "' || 'EMAIL_FILES.SECBOUND' || '"' ||
Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
IF i = 1 Then
-- mail body
Utl_Smtp.WRITE_DATA(v_connection_handle,
'--' || 'EMAIL_FILES.SECBOUND' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Type: text/html;' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, l_message || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
END IF;
-- mail attachment
Utl_Smtp.WRITE_DATA(v_connection_handle,
'--' || 'EMAIL_FILES.SECBOUND' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Type: application/octet-stream' ||
Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Disposition: attachment; ' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
' filename="' || vfile || '"' || --my filename
Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
/* writing the blol in chunks */
WHILE l_pos < l_blob_len LOOP
Dbms_Lob.Read(l_blob, l_amount, l_pos, l_buffer);
Utl_Smtp.write_raw_data(v_connection_handle,
Utl_Encode.BASE64_ENCODE(l_buffer));
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
l_buffer := Null;
l_pos := l_pos + l_amount;
END LOOP;
Utl_Smtp.WRITE_DATA(v_connection_handle, Utl_Tcp.CRLF);
Dbms_Lob.FREETEMPORARY(l_blob);
Dbms_Lob.FILECLOSE(v_src_loc);
EXCEPTION
When Others Then
Dbms_Lob.FREETEMPORARY(l_blob);
Dbms_Lob.FILECLOSE(v_src_loc);
END;
END LOOP;
-- close email
Utl_Smtp.WRITE_DATA(v_connection_handle,
'--' || 'EMAIL_FILES.SECBOUND' || '--' || Utl_Tcp.CRLF);
Utl_Smtp.WRITE_DATA(v_connection_handle,
Utl_Tcp.CRLF || '.' || Utl_Tcp.CRLF);
END IF;
Utl_Smtp.CLOSE_DATA(v_connection_handle);
Utl_Smtp.QUIT(v_connection_handle);
EXCEPTION
When Others Then
-- utl_smtp.quit(v_connection_handle);
IF ifile Is Not Null Then
Utl_Smtp.QUIT(v_connection_handle);
END IF;
RAISE;
END;
Vinish Kapoor
Follow @foxinfotechHi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.
After calling this procedure service not available error is occurring…
You need to set smtp_out_server parameter in oracle as described below:
alter system set smtp_out_server = 'yourmailserver yourport' scope=both;
— change the yourmailserver with your mail server and after a space specify port number.
Then run below script in Sys user but before running this script modify the youruser and yourmailserver in script.
–RUN IN SYS USER FOR ACL PRIVILEGE FOR ORACLE 11G
—————————————————————————————–
— "Set define off" turns off substitution variables.
Set define off;
— RUN IN SYS USER —
CREATE OR REPLACE PROCEDURE mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
Is
BEGIN
BEGIN
Dbms_Network_Acl_Admin.DROP_ACL(aacl);
Dbms_Output.put_Line('ACL dropped…..');
EXCEPTION
When Others Then
Dbms_Output.put_Line('Error dropping ACL: '||aacl);
Dbms_Output.put_Line(SQLERRM);
END;
BEGIN
Dbms_Network_Acl_Admin.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
Dbms_Output.put_Line('ACL created…..');
EXCEPTION
When Others Then
Dbms_Output.put_Line('Error creating ACL: '||aacl);
Dbms_Output.put_Line(SQLERRM);
END;
BEGIN
Dbms_Network_Acl_Admin.ASSIGN_ACL(aacl,aserver,aport);
Dbms_Output.put_Line('ACL assigned…..');
EXCEPTION
When Others Then
Dbms_Output.put_Line('Error assigning ACL: '||aacl);
Dbms_Output.put_Line(SQLERRM);
END;
COMMIT;
Dbms_Output.put_Line('ACL commited…..');
END;
/
BEGIN
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'YourUser',
TRUE,
'connect',
'YourMailServer',
YourPort);
END;
/
— FOR ANY OTHER USER
BEGIN
Dbms_Network_Acl_Admin.ADD_PRIVILEGE
('mailserver_acl.xml','YourUser',TRUE,'connect');
COMMIT;
END;
/
this will solve your problem
yes… problem resolved… Thank u so much…
Sir I am New in oracle programming, i want to configure an email sending option in oracle 10-g forms. so any help from initial step please?
Hi Muhammad,
The above procedure is tested for Oracle 11g database, but if you want to send mail through Forms 10g, then you should use some java application and execute it through forms to send mails.
can i have PL/SQL sample code for creating .csv and file and zip and attached in to email.