Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement