daily pastebin goal
69%
SHARE
TWEET

Untitled

a guest Sep 11th, 2018 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. CREATE OR REPLACE PROCEDURE PR_TEST_MAIL
  3. AS
  4.     CONN             UTL_SMTP.CONNECTION;
  5.     V_PORT           VARCHAR2(100):='25';
  6.     V_HOST           VARCHAR2(100):='172.23.51.29';
  7.     CRLF             VARCHAR2(10):= UTL_TCP.CRLF;
  8.     V_DATA_STR       CLOB;
  9.     MESG             VARCHAR2(4000);
  10.     VC_ERROR_CODE    VARCHAR2(200);
  11.     VC_CC_RCPT       VARCHAR2(4000);
  12.     VC_TO_RCPT       VARCHAR2(4000);
  13.     VC_TAB_DATA      VARCHAR2(4000);
  14.     VC_DYANMIC_BODY  VARCHAR2(4000);
  15.     TYPE T IS REF CURSOR;
  16.     REF_CUR T;
  17. BEGIN
  18.  
  19.  
  20. OPEN REF_CUR FOR 'WITH T  (month, saving) AS
  21. (select ''Jan'', 100 from dual union all
  22. select ''Feb'', 500 from dual)
  23. select ''<tr><td>''||month||''</td> <td>''||saving ||''</td></tr>''
  24.    from t';
  25.    LOOP    
  26.    FETCH REF_CUR INTO VC_TAB_DATA;
  27.    EXIT WHEN REF_CUR%NOTFOUND;
  28.    VC_DYANMIC_BODY:=VC_DYANMIC_BODY||' '||VC_TAB_DATA||' ';
  29.    END LOOP;
  30.    CLOSE REF_CUR;
  31.    VC_DYANMIC_BODY:='<table>'||VC_DYANMIC_BODY||'</table>';
  32.    DBMS_OUTPUT.PUT_LINE(VC_DYANMIC_BODY);
  33.      
  34.     MESG :='Hi,'||CRLF;
  35.     CONN := UTL_SMTP.OPEN_CONNECTION(V_HOST, V_PORT);
  36.     UTL_SMTP.HELO(CONN, V_HOST);
  37.     UTL_SMTP.MAIL(CONN, 'prashant.dabral@yourmaildomain.com');  
  38.     UTL_SMTP.RCPT(CONN,'prashant.dabral@yourmaildomain.com');  
  39.     UTL_SMTP.OPEN_DATA(CONN);
  40.     VC_CC_RCPT:='prashant.dabral@yourmaildomain.com';              
  41.     VC_TO_RCPT := 'prashant.dabral@yourmaildomain.com';  
  42.     UTL_SMTP.WRITE_DATA(CONN,
  43.                              'Date:    '|| TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || CRLF ||
  44.                              'From:    '|| 'prashant.dabral@yourmaildomain.com'    || CRLF ||  
  45.                              'Subject: '|| 'Adhoc Mail test'                       || CRLF ||
  46.                              'To:      '|| VC_TO_RCPT                              || CRLF ||
  47.                              'Cc:      '||''||VC_CC_RCPT||'');
  48.            
  49.          MESG:='Content-Type: text/html;' ||crlf ||
  50.                'tu codigo y tus variables '||VC_DYANMIC_BODY||'codigo y mas variables';
  51.                
  52. UTL_SMTP.WRITE_DATA(CONN,UTL_TCP.CRLF||MESG);
  53. UTL_SMTP.CLOSE_DATA(CONN);
  54. UTL_SMTP.QUIT(CONN);
  55. EXCEPTION
  56.     WHEN OTHERS THEN
  57.     VC_ERROR_CODE :=SQLCODE ||'-'|| SQLERRM;
  58.     DBMS_OUTPUT.PUT_LINE(VC_ERROR_CODE);
  59. END;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top