SQL & PL/SQL

Hello Team,

I have created a below mentioned procedure to send the output of the query in an email attachment periodically and it is working fine.

However, I would like to know how to send the output of the query in an email body not an attachment in the form of table as below:

Sample output of query:

CREATE OR REPLACE

PROCEDURE PR_NAV_MAILING

AS

  V_FROM        VARCHAR2(80) := 'from_test@abc.com';

  v_Recipient_5 VARCHAR2(80) := 'recipient_test@abc.com';

  V_SUBJECT     VARCHAR2(80) := 'Units creation data ';

  v_Mail_Host   VARCHAR2(30) := 'SMTP IP ADDRESS';

  v_Mail_Conn utl_smtp.Connection;

  crlf VARCHAR2(2) := chr(13)||chr(10);

  CURSOR INS_UPD

  IS

    --QUERY to capture UNITS creation data

    SELECT NAV_DATE,

      NAV_VALUE,

      subscription ,

      redemption

    FROM data_nav

    WHERE scheme_code = '974'

    AND NAV_DATE      >ADD_MONTHS(SYSDATE,-1)

    ORDER BY nav_date DESC;

BEGIN

  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_5);

  --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 ||' Auto Generated Mail '||Crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Bcc: ' || v_Recipient_5 ||','|| 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,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,'Dear All, '|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,'Please find the attached data for units creation. '||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,'Regards, '|| crlf);

  UTL_SMTP.WRITE_DATA(V_MAIL_CONN,NULL|| CRLF);

  utl_smtp.write_DATA(v_Mail_Conn,'Operations Team. '|| crlf);

  utl_smtp.write_DATA(v_Mail_Conn,NULL|| crlf);

  utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF ||'This mail is Auto generated.');

  --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="Gold_ETF NAV'||'.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 || 'NAV Date, NAV_VALUE,GOLD PRICE,CASH COMPONENT');

  FOR cur_rec IN INS_UPD

  LOOP

    utl_smtp.write_data(v_Mail_Conn, utl_tcp.CRLF || cur_rec.NAV_Date || ',' || cur_rec.NAV_VALUE || ',' || cur_rec.SUBSCRIPTION || ',' || cur_rec.REDEMPTION );

  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);

  --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;