0 Replies Latest reply: Jan 19, 2011 2:05 AM by 755711 RSS

    email attachment -- procedure exe without any error..but no mail coming

    755711
      Dear All

      OS -- HP UX --

      directory created as dir_mmail path /tmp/sayeed
      grants given to public for read write

      email procedure ---
      CREATE OR REPLACE procedure DBA_HO.Emailattach1 is
      /*LOB operation related varriables */
      v_src_loc BFILE := BFILENAME('DIR_MMAIL', 'imran.jpeg');
      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;
      v_connection_handle UTL_SMTP.CONNECTION;
      v_from_email_address VARCHAR2(80) := 'itmakropak@makropak.com';
      v_to_email_address VARCHAR2(80) := 'sayeed@makropak.com';
      v_smtp_host VARCHAR2(80) := 'mailhost.makropak.com';
      v_subject VARCHAR2(80) := 'Makro Mail Test Mail';
      l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';

      /* 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
      /*Preparing the LOB from file for attachment. */
      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 related coding. */
      v_connection_handle := UTL_SMTP.OPEN_CONNECTION(v_smtp_host, 25);
      UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
      UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
      UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
      UTL_SMTP.OPEN_DATA(v_connection_handle);
      send_header('From', '"Sender"');
      send_header('To', '"Recipient"');
      send_header('Subject', v_subject);

      --MIME header.
      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= "' || 'DIR_MMAIL.SECBOUND' || '"' ||
      UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

      -- Mail Body
      UTL_SMTP.WRITE_DATA(v_connection_handle,
      '--' || 'DIR_MMAIL.SECBOUND' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(v_connection_handle,
      'Content-Type: text/plain;' || 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);

      -- Mail Attachment
      UTL_SMTP.WRITE_DATA(v_connection_handle,
      '--' || 'DIR_MMAIL.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="' || 'imran.jpeg' || '"' || 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);

      -- Close Email
      UTL_SMTP.WRITE_DATA(v_connection_handle,
      '--' || ' DIR_MMAIL.SECBOUND' || '--' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(v_connection_handle,
      UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

      UTL_SMTP.CLOSE_DATA(v_connection_handle);
      UTL_SMTP.QUIT(v_connection_handle);
      DBMS_LOB.FREETEMPORARY(l_blob);
      DBMS_LOB.FILECLOSE(v_src_loc);

      EXCEPTION
      WHEN OTHERS THEN
      UTL_SMTP.QUIT(v_connection_handle);
      DBMS_LOB.FREETEMPORARY(l_blob);
      DBMS_LOB.FILECLOSE(v_src_loc);
      RAISE;
      END;
      /

      Procedure created.

      SQL> execute emailattach1;

      PL/SQL procedure successfully completed.

      Please let me know how i can find where is problem or why i am not getting email. If i use this procedure simpl (no attachment) then i get email.

      Regards

      Sayeed