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('yourid@urdomain.com', 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;
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...
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.
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?
can i have PL/SQL sample code for creating .csv and file and zip and attached in to email.