CREATE OR REPLACE PROCEDURE PR_TEST_MAIL
AS
    CONN             UTL_SMTP.CONNECTION;
    V_PORT           VARCHAR2(100):='25';
    V_HOST           VARCHAR2(100):='172.23.51.29';
    CRLF             VARCHAR2(10):= UTL_TCP.CRLF;
    V_DATA_STR       CLOB;
    MESG             VARCHAR2(4000);
    VC_ERROR_CODE    VARCHAR2(200);
    VC_CC_RCPT       VARCHAR2(4000);
    VC_TO_RCPT       VARCHAR2(4000);
    VC_TAB_DATA      VARCHAR2(4000);
    VC_DYANMIC_BODY  VARCHAR2(4000);
    TYPE T IS REF CURSOR;
    REF_CUR T;
BEGIN


OPEN REF_CUR FOR 'WITH T  (month, saving) AS
(select ''Jan'', 100 from dual union all
select ''Feb'', 500 from dual)
select ''<tr><td>''||month||''</td> <td>''||saving ||''</td></tr>''
   from t';
   LOOP
   FETCH REF_CUR INTO VC_TAB_DATA;
   EXIT WHEN REF_CUR%NOTFOUND;
   VC_DYANMIC_BODY:=VC_DYANMIC_BODY||' '||VC_TAB_DATA||' ';
   END LOOP;
   CLOSE REF_CUR;
   VC_DYANMIC_BODY:='<table>'||VC_DYANMIC_BODY||'</table>';
   DBMS_OUTPUT.PUT_LINE(VC_DYANMIC_BODY);

    MESG :='Hi,'||CRLF;
    CONN := UTL_SMTP.OPEN_CONNECTION(V_HOST, V_PORT);
    UTL_SMTP.HELO(CONN, V_HOST);
    UTL_SMTP.MAIL(CONN, 'prashant.dabral@yourmaildomain.com');
    UTL_SMTP.RCPT(CONN,'prashant.dabral@yourmaildomain.com');
    UTL_SMTP.OPEN_DATA(CONN);
    VC_CC_RCPT:='prashant.dabral@yourmaildomain.com';
    VC_TO_RCPT := 'prashant.dabral@yourmaildomain.com';
    UTL_SMTP.WRITE_DATA(CONN,
                             'Date:    '|| TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || CRLF ||
                             'From:    '|| 'prashant.dabral@yourmaildomain.com'    || CRLF ||
                             'Subject: '|| 'Adhoc Mail test'                       || CRLF ||
                             'To:      '|| VC_TO_RCPT                              || CRLF ||
                             'Cc:      '||''||VC_CC_RCPT||'');

         MESG:='Content-Type: text/html;' ||crlf ||
               'tu codigo y tus variables '||VC_DYANMIC_BODY||'codigo y mas variables';

UTL_SMTP.WRITE_DATA(CONN,UTL_TCP.CRLF||MESG);
UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);
EXCEPTION
    WHEN OTHERS THEN
    VC_ERROR_CODE :=SQLCODE ||'-'|| SQLERRM;
    DBMS_OUTPUT.PUT_LINE(VC_ERROR_CODE);
END;