Debugging with Oracle's utl_smtp
NickName:Dan Maharry Ask DateTime:2008-09-26T19:01:31

Debugging with Oracle's utl_smtp

A client of mine uses Oracle 9i's utl_smtp to send mails out notifications to managers when their employees have made travel requests and they woul like quite a few changes made to the mailouts done.

We're having a lot of problems getting utl_smtp to talk to any smtp server on our network. We've even tried installing free smtp server on the oracle box but it will not spot the mail server running on port 25. The error code is ORA-29278.

So two questions really.

  1. Does anyone have any experience setting up email using Oracle's utl_smtp utility and have any suggestions as to where we might be going wrong.

  2. Does anyone know if it is possible to get utl_smtp to dump text emails to a directory much as you can do if you're using system.net.mail's specifiedpickupdirectory config setting. This would be by far the preferable option.

Thanks, Dan

Copyright Notice：Content Author:「Dan Maharry」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/138747/debugging-with-oracles-utl-smtp

Answers
cagcowboy 2008-09-26T14:25:38

Looks like the HELO is the problem. Please can we check with a simple testcase...\n\nset serveroutput on\n\ndeclare\n lConnection UTL_SMTP.CONNECTION;\nbegin\n lConnection := UTL_SMTP.OPEN_CONNECTION(your_smtp_server);\n DBMS_OUTPUT.PUT_LINE('Opened ok');\n\n UTL_SMTP.HELO(lConnection, your_client_machine_name);\n DBMS_OUTPUT.PUT_LINE('HELO ok');\n\n UTL_SMTP.MAIL(lConnection, your_email_address);\n UTL_SMTP.RCPT(lConnection, your_email_address);\n DBMS_OUTPUT.PUT_LINE('Addressing ok');\nend;\n/\n",

Dan Maharry 2008-10-07T15:28:47

Looks like we've resolved this.\nTo answer the two questions.\n\n\nDouble check that the schema calling utl_smtp has execute permissions on sys.utl_smtp, sys.utl_tcp and sys.dbms_lob. Also check that at no time the message being sent is > 32Kb.\nNo there is no way to get utl_smtp to dump emails to a directory a la system.net.mail.\n\n\nThanks to cagcowboy for the help.",

Dan Maharry 2008-09-26T12:10:24

Yes, we can telnet to the server.\n\n-- ****** Object: Stored Procedure TRAVELADMIN_DEV.HTML_EMAIL Script Date: 22/08/2008 12:41:02 ******\nCREATE PROCEDURE \"HTML_EMAIL\" (\n p_to in varchar2,\n p_cc in varchar2,\n p_from in varchar2,\n p_subject in varchar2,\n p_text in varchar2 default null,\n p_html in varchar2 default null\n )\nis\n l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';\n l_connection utl_smtp.connection;\n l_body_html clob := empty_clob; --This LOB will be the email message\n l_offset number;\n l_ammount number;\n l_temp varchar2(32767) default null;\n p_smtp_hostname varchar2(30):= 'rockies';\n p_smtp_portnum varchar2(2) := '25';\nbegin\n l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );\n utl_smtp.helo( l_connection, p_smtp_hostname );\n utl_smtp.mail( l_connection, p_from );\n utl_smtp.rcpt( l_connection, p_to );\n l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);\n l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);\n IF ((p_cc <> NULL) OR (LENGTH(p_cc) > 0)) THEN\n l_temp := l_temp || 'Cc: ' || p_cc || chr(13) || chr(10);\n utl_smtp.rcpt( l_connection, p_cc );\n END IF;\n l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);\n l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);\n l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);\n l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||\n chr(34) || l_boundary || chr(34) || chr(13) ||\n chr(10);\n ----------------------------------------------------\n -- Write the headers\n dbms_lob.createtemporary( l_body_html, false, 10 );\n dbms_lob.write(l_body_html,length(l_temp),1,l_temp);\n ----------------------------------------------------\n -- Write the text boundary\n l_offset := dbms_lob.getlength(l_body_html) + 1;\n l_temp := '--' || l_boundary || chr(13)||chr(10);\n l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||\n chr(13) || chr(10) || chr(13) || chr(10);\n dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);\n ----------------------------------------------------\n -- Write the plain text portion of the email\n l_offset := dbms_lob.getlength(l_body_html) + 1;\n dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);\n ----------------------------------------------------\n -- Write the HTML boundary\n l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||\n chr(13) || chr(10);\n l_temp := l_temp || 'content-type: text/html;' ||\n chr(13) || chr(10) || chr(13) || chr(10);\n l_offset := dbms_lob.getlength(l_body_html) + 1;\n dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);\n ----------------------------------------------------\n -- Write the HTML portion of the message\n l_offset := dbms_lob.getlength(l_body_html) + 1;\n dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);\n ----------------------------------------------------\n -- Write the final html boundary\n l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);\n l_offset := dbms_lob.getlength(l_body_html) + 1;\n dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);\n ----------------------------------------------------\n -- Send the email in 1900 byte chunks to UTL_SMTP\n l_offset := 1;\n l_ammount := 1900;\n utl_smtp.open_data(l_connection);\n while l_offset < dbms_lob.getlength(l_body_html) loop\n utl_smtp.write_data(l_connection,\n dbms_lob.substr(l_body_html,l_ammount,l_offset));\n l_offset := l_offset + l_ammount ;\n l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);\n end loop;\n utl_smtp.close_data(l_connection);\n utl_smtp.quit( l_connection );\n dbms_lob.freetemporary(l_body_html);\nend;\n",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “Debugging with Oracle's utl_smtp” related questions
Oracle debugging techniques

I'm having difficulty debugging triggers in Oracle. Currently I'm using Oracle's Sql Developer tool. To test a trigger I write inserts or deletes in a test window but I can't see what is happening ...

Show Detail
Cannot connect to Oracle database during Visual Studio debugging

I am having an issue connecting to an Oracle database when running an SSIS package in debugging mode in Visual Studio. I am using Visual Studio 2012 with Data Tools installed and an Oracle 10g clie...

Show Detail
Looking for tips on debugging Oracle row-level security functions

I'm looking for tips in debugging some of my row-level security predicates in an Oracle database. These predicates use a few concepts to determine whether the current user can see a record: current

Show Detail
Oracle PL/SQL debugging permission implications

In order to be able to debug a PL/SQL code on Oracle database one must have a DEBUG permission which is not granted by default (GRANT DEBUG CONNECT SESSION TO username). Now I develop some PL/SQL

Show Detail
Debugging 32bit CASSINI on 64bit OS (Oracle 9)

We are debugging a 32bit application, which connects to oracle using 32bit libraries. We are able to debug in IIS by setting our application pools to 32bit and using IIS debugging. We would like t...

Show Detail
Debugging Oracle queries being sent by Oracle

I have a large PHP program that interacts with an Oracle DB. There are obviously view pages and pages that are more for program logic. Some of my logical pages need to be debugged. I have Oracle

Show Detail
Debugging Oracle DDL triggers

Can't understand why Oracle did not fire any DDL trigger. &gt;SELECT * FROM V$VERSION Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Example from https://docs.oracle.com/en/

Show Detail
Cannot "step into" another subprogram while debugging with the Oracle SQL Developer

To debug with the Oracle SQL Developer, I first perform "compile for debug" and then start debugging. However, even if I use another subprogram within my code, which also is a subprogram of the sam...

Show Detail
Debugging Playframework Anorm with Oracle

I just tried to switch a playframework 2.1 scala project from a h2 database to oracle via jdbc. The connection seems to work, but now I get this very generic error (see below). It essentially is: ...

Show Detail
Getting a PopUp while debugging an anonymous block in Oracle SQL Developer Tool

I got an error while debugging an anonymous block in Oracle SQL Developer tool. It pops up a window saying unable to find the source file. But as this is an anonymous block, I don't need a source f...

Show Detail