Send email with attachments in Oracle D2k, Oracle Forms 10g, Oracle Forms 6i

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;

V. Kapoor

January 30, 2013 at 5:59 am

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

6 thoughts on “Send email with attachments in Oracle D2k, Oracle Forms 10g, Oracle Forms 6i”

Neha
January 30, 2013 at 5:50 am
After calling this procedure service not available error is occurring...
V. Kapoor
January 30, 2013 at 5:59 am
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
Neha
January 30, 2013 at 7:28 am
yes... problem resolved... Thank u so much...
V. Kapoor
January 8, 2016 at 1:36 am
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.
Muhammad Khan
January 8, 2016 at 1:32 am
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?
Janaka Ratnayake
December 29, 2016 at 3:28 am
can i have PL/SQL sample code for creating .csv and file and zip and attached in to email.

Comments are closed.