code_junkie

Debugging with Oracle's utl_smtp

Nov 11th, 2011
124
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.70 KB | None | 0 0
  1. set serveroutput on
  2.  
  3. declare
  4. lConnection UTL_SMTP.CONNECTION;
  5. begin
  6. lConnection := UTL_SMTP.OPEN_CONNECTION(your_smtp_server);
  7. DBMS_OUTPUT.PUT_LINE('Opened ok');
  8.  
  9. UTL_SMTP.HELO(lConnection, your_client_machine_name);
  10. DBMS_OUTPUT.PUT_LINE('HELO ok');
  11.  
  12. UTL_SMTP.MAIL(lConnection, your_email_address);
  13. UTL_SMTP.RCPT(lConnection, your_email_address);
  14. DBMS_OUTPUT.PUT_LINE('Addressing ok');
  15. end;
  16. /
  17.  
  18. -- ****** Object: Stored Procedure TRAVELADMIN_DEV.HTML_EMAIL Script Date: 22/08/2008 12:41:02 ******
  19. CREATE PROCEDURE "HTML_EMAIL" (
  20. p_to in varchar2,
  21. p_cc in varchar2,
  22. p_from in varchar2,
  23. p_subject in varchar2,
  24. p_text in varchar2 default null,
  25. p_html in varchar2 default null
  26. )
  27. is
  28. l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
  29. l_connection utl_smtp.connection;
  30. l_body_html clob := empty_clob; --This LOB will be the email message
  31. l_offset number;
  32. l_ammount number;
  33. l_temp varchar2(32767) default null;
  34. p_smtp_hostname varchar2(30):= 'rockies';
  35. p_smtp_portnum varchar2(2) := '25';
  36. begin
  37. l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
  38. utl_smtp.helo( l_connection, p_smtp_hostname );
  39. utl_smtp.mail( l_connection, p_from );
  40. utl_smtp.rcpt( l_connection, p_to );
  41. l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
  42. l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
  43. IF ((p_cc <> NULL) OR (LENGTH(p_cc) > 0)) THEN
  44. l_temp := l_temp || 'Cc: ' || p_cc || chr(13) || chr(10);
  45. utl_smtp.rcpt( l_connection, p_cc );
  46. END IF;
  47. l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
  48. l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
  49. l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
  50. l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
  51. chr(34) || l_boundary || chr(34) || chr(13) ||
  52. chr(10);
  53. ----------------------------------------------------
  54. -- Write the headers
  55. dbms_lob.createtemporary( l_body_html, false, 10 );
  56. dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
  57. ----------------------------------------------------
  58. -- Write the text boundary
  59. l_offset := dbms_lob.getlength(l_body_html) + 1;
  60. l_temp := '--' || l_boundary || chr(13)||chr(10);
  61. l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
  62. chr(13) || chr(10) || chr(13) || chr(10);
  63. dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
  64. ----------------------------------------------------
  65. -- Write the plain text portion of the email
  66. l_offset := dbms_lob.getlength(l_body_html) + 1;
  67. dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
  68. ----------------------------------------------------
  69. -- Write the HTML boundary
  70. l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
  71. chr(13) || chr(10);
  72. l_temp := l_temp || 'content-type: text/html;' ||
  73. chr(13) || chr(10) || chr(13) || chr(10);
  74. l_offset := dbms_lob.getlength(l_body_html) + 1;
  75. dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
  76. ----------------------------------------------------
  77. -- Write the HTML portion of the message
  78. l_offset := dbms_lob.getlength(l_body_html) + 1;
  79. dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
  80. ----------------------------------------------------
  81. -- Write the final html boundary
  82. l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
  83. l_offset := dbms_lob.getlength(l_body_html) + 1;
  84. dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
  85. ----------------------------------------------------
  86. -- Send the email in 1900 byte chunks to UTL_SMTP
  87. l_offset := 1;
  88. l_ammount := 1900;
  89. utl_smtp.open_data(l_connection);
  90. while l_offset < dbms_lob.getlength(l_body_html) loop
  91. utl_smtp.write_data(l_connection,
  92. dbms_lob.substr(l_body_html,l_ammount,l_offset));
  93. l_offset := l_offset + l_ammount ;
  94. l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
  95. end loop;
  96. utl_smtp.close_data(l_connection);
  97. utl_smtp.quit( l_connection );
  98. dbms_lob.freetemporary(l_body_html);
  99. end;
  100.  
  101. An error has occurred:
  102. ORA-29278: SMTP transient error: 421 service not available
  103. ORA-06512: at 'SYS.UTL_SMTP', line 17
  104. ORA-06512: at 'SYS.UTL_SMTP', line 96
  105. ORA-06512: at 'SYS.UTL_SMTP', line 138
  106. ORA-06512: at 'TRAVELADMIN_DEV.HTML_EMAIL', line 20
  107. ORA-06512: at 'TRAVELADMIN_DEV.SEND_MAIL', line 421
  108. ORA-06512: at line 1
Add Comment
Please, Sign In to add comment