SQL & PL/SQL

Dear All,
We have oracle 10G R2 on windows.

We have a procedure that sends mail from oracle. we are using outlook 2007.

below is the code of mail Procedure which uses Utl_SMTP package.

CREATE OR REPLACE PROCEDURE TEST_MAIL
(
   v_feed_number IN varchar2
)
AS

   v_From       VARCHAR2(80) := 'chanchal.wankhade@gmail.com';
   v_Recipient  VARCHAR2(80) := 'Chanchal.wankhade@gmail.com';
   


   v_Subject    VARCHAR2(80) := 'Conformation for ';
   v_Mail_Host  VARCHAR2(30) := 'I have Specified IP Address here';
   v_Mail_Conn  utl_smtp.Connection;
   crlf         VARCHAR2(2)  := chr(13)||chr(10);
   SERIAL_NO NUMBER := 1;
   v_mismatch_count NUMBER;
   v_cams_file_count NUMBER;

   CURSOR INS_UPD IS
    SELECT *
    FROM INSERT_UPDATE_RECORDS;

   CURSOR MIS IS
    SELECT A.FOLIONO,A.SCHEME_CODE,nvl(A.TOTAL_UNITS,0) "PROCESSED_TRAN_TOTAL_UNITS",
    nvl(B.TOTALUNITS,0) "SCHEMEWISE_TOTALUNIT",(nvl(A.TOTAL_UNITS,0) - nvl(B.TOTALUNITS,0)) "DIFFERENCE"
    FROM tran_temp_SPLIT A, CAMS_SCHEMEWISE_FOLIO B
    WHERE A.FOLIONO =B.FOLIONO (+)
    AND A.SCHEME_CODE = B.SCHCODE (+)
    AND round((nvl(A.TOTAL_UNITS,0) - nvl(B.TOTALUNITS,0)),3) <> 0;


  CURSOR ERROR_FILE IS
   SELECT FILE_NAME
   FROM INSERT_UPDATE_RECORDS
   WHERE DIFFERENCE <> 0;

  Cursor Error_code is select distinct COLUMN_VALUE,ERROR_MSG,TABLE_NAME from log_errors where error_date>Trunc(sysdate);

BEGIN

  select count(*)
  into v_mismatch_count
  from(
	    SELECT A.FOLIONO,A.SCHEME_CODE,nvl(A.TOTAL_UNITS,0) "PROCESSED_TRAN_TOTAL_UNITS",
            nvl(B.TOTALUNITS,0) "SCHEMEWISE_TOTALUNIT",(nvl(A.TOTAL_UNITS,0) - nvl(B.TOTALUNITS,0)) "DIFFERENCE"
            FROM tran_temp_SPLIT A, CAMS_SCHEMEWISE_FOLIO B
            WHERE A.FOLIONO =B.FOLIONO (+)
            AND A.SCHEME_CODE = B.SCHCODE (+)
            AND round((nvl(A.TOTAL_UNITS,0) - nvl(B.TOTALUNITS,0)),3) <> 0
	);



  SELECT COUNT(*)
  INTO v_cams_file_count
  FROM CAMS_CHECKSUM_DBF;



  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host);


  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

  utl_smtp.Mail(v_Mail_Conn, v_From);

  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

 



 --OPEN DATA CONNNECTION
  UTL_SMTP.OPEN_DATA(v_mail_conn);

--MAIL HEADER

  utl_smtp.write_DATA(v_Mail_Conn,'Date: '   || to_char(sysdate, 'DD-MON-YYYY hh:mi:ss AM') || crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'From: '   || v_From || crlf );
  utl_smtp.write_DATA(v_Mail_Conn,'Subject: '|| v_Subject || v_feed_number|| ' Mismatches:-'||v_mismatch_count||' Auto Generated Mail '||crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'To: '     || v_Recipient || crlf);
  
  


--MAIL BODY
  utl_smtp.write_DATA(v_Mail_Conn,'MIME-Version: 1.0'|| crlf );
  utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: multipart/mixed;'|| crlf );
  utl_smtp.write_DATA(v_Mail_Conn,' boundary="-----SECBOUND"'|| crlf ||crlf );
  utl_smtp.write_DATA(v_Mail_Conn,'-------SECBOUND'|| crlf );
  utl_smtp.write_DATA(v_Mail_Conn,'Content-Type: text/plain;'|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'Content-Transfer_Encoding: 7bit'|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'Please the conformation file for '||v_feed_number||' attached. '|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'There are '||v_mismatch_count||' Mismatches. '|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,null|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,null|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,null|| crlf);
  UTL_SMTP.WRITE_DATA(v_mail_conn, '-------SECBOUND' || crlf);
  UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: text/plain;' || crlf);



--MAIL ATACHMENT
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || '-------SECBOUND');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Type: text/csv; ');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Disposition: attachment; filename="INSERT_UPDATE_P_'||v_feed_number||'.csv"');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Transfer-Encoding: 8bit');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF);


    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'AMC,This AMC');
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'Reference Number of Last Feed Updated,'||v_feed_number);
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'Confirmation Date and Time ,'||to_char(sysdate, 'DD-MON-YYYY hh:mi:ss AM'));
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'Confirmed By,Chanchal Wankhade(By Email)');
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'Details of Upload Confirmation');
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'Exceptions Noticed');
    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'SNo,Feed file Name,No of Records Received from CAMS,Records inserted at AMC,Records updated at AMC,Records rejected by AMC,Records Failed,CAMS_CHECKSUM_RECORDS,dt_update');



  FOR cur_rec IN INS_UPD
    LOOP
	IF  cur_rec.CAMS_CHECKSUM_RECORDS IS NOT NULL AND  cur_rec.DIFFERENCE = 0 THEN

	 utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||   SERIAL_NO                      || ',' ||
					                    cur_rec.FILE_NAME              || ',' ||
                                                            cur_rec.TOTAL_RECORDS          || ',' ||
                                                            cur_rec. ROWS_INSERTED         || ',' ||
                                                            cur_rec.ROWS_UPDATED           || ',' ||
                                                            cur_rec.REJECTED_RECORDS       || ',' ||
					                    cur_rec.DIFFERENCE             || ',' ||
                                                            cur_rec.CAMS_CHECKSUM_RECORDS  || ',' ||
					                    cur_rec.DT_UPDATE);
	 SERIAL_NO := SERIAL_NO+1;
	    END IF;
END LOOP;

   utl_smtp.write_DATA(v_Mail_Conn,null|| crlf);
   utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);
   utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);

--MAIL ATACHMENT

    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || '-------SECBOUND');

    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Type: text/csv;');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Disposition: attachment; filename="'||sysdate||'_P_AFTER'||v_feed_number||'.csv"');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Transfer-Encoding: 8bit');
    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF);


    utl_smtp.write_data(v_Mail_Conn,utl_tcp.CRLF || 'FOLIONO,SCHEME_CODE,PROCESSED_TRAN_TOTAL_UNITS,SCHEMEWISE_TOTALUNIT,DIFFERENCE');

  FOR cur_REC IN MIS
    LOOP
	  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||    CUR_REC.FOLIONO                                || ',' ||
				                              CUR_REC.SCHEME_CODE	                     || ',' ||
					                      CUR_REC."PROCESSED_TRAN_TOTAL_UNITS"           || ',' ||
                                                              CUR_REC."SCHEMEWISE_TOTALUNIT"                 || ',' ||
                                                              CUR_REC. "DIFFERENCE"         );



   END LOOP;

  SERIAL_NO := SERIAL_NO-1;

--MAIL BODY

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || '-------SECBOUND');
  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || '-------SECBOUND');
  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'Content-Type: text/PLAIN;');
  utl_smtp.write_DATA(v_Mail_Conn,'Content-Transfer_Encoding: 7bit'|| crlf || crlf);
  utl_smtp.write_DATA(v_Mail_Conn,null|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,' '|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'The number of files recieved from CAMS is:- '||v_cams_file_count|| crlf);
  utl_smtp.write_DATA(v_Mail_Conn,'The number of files uploaded are:- '||SERIAL_NO|| crlf);

 IF  SERIAL_NO-v_cams_file_count = 0 THEN
 utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'All files uploaded correctly ');
 ELSE

 FOR cur_REC IN ERROR_FILE
    LOOP
	  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||CUR_REC.FILE_NAME||' file not uploaded correctly.');


   END LOOP;

 FOR ERROR_COD IN ERROR_CODE
	LOOP

		utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||ERROR_COD.TABLE_NAME|| ' file not uploaded correctly. '||ERROR_COD.ERROR_MSG|| 'FOR  '||ERROR_COD.COLUMN_VALUE);


END LOOP;
 END IF;
      utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'This mail is auto generated.');

--CLOSE CONNECTION
  UTL_SMTP.CLOSE_DATA(v_mail_conn);
  utl_smtp.Quit(v_mail_conn);
EXCEPTION
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
    raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;

Procedure is running fine. but the body of mail is comming in text attached file.

How can i set it as genuen mail body in outlook 2007?



Regards,
Chanchal wankhade.