UTL_SMTP.Write_Data not sending any text if a colon is included

I have created a Oracle function to send an email using the UTL_SMTP package.

When using the methods Write_Data or Data, if my text does not contain a colon, the sent email will contain the inputted text in the email's body.

However, if the text contains a colon, the text is not included in the email.

Every example of this i've seen online seems to indicate this is no problem. Any idea what could be th开发者_C百科e cause of this?

This works: UTL_SMTP.write_data(l_mail_conn, 'test');

This does not get sent: UTL_SMTP.write_data(l_mail_conn, 'test:');

nor does: UTL_SMTP.write_data(l_mail_conn, 'test' || ':');

It may be getting interpreted as a header

Rather than write your own, look at the mail code included in PLCODEBREW

I had this problem too. Apreciating that you have upgraded to UTL_MAIL - my findings below are for those whom would prefer or have to stay with UTL_SMTP.

If you ensure your SMS body does not match the pattern 'aaa:...' then the utl_smtp.write_data will not interpret it as a header. If your SMS body does match this pattern then prefix your message with a space or you may simply want to replace the colon with a semi-colon etc... Your choice.

You can use the following to intercept and workaround the problem.

.....
/* 999999999 is just an indicitive integer above and beyond the max length of an sms */
     IF (INSTR(p_message,':') < NVL(INSTR(p_message,' '),999999999)
        AND  INSTR(p_message,':') != 0)
     THEN p_message := ' '||p_message;
     END IF;

utl_smtp.write_data(l_mail_conn, p_message);
.....

I wasn't able to get UTL_SMTP working - certainly looks like any colons in the UTL_SMTP body was being interpreted as a header and I could not find a way to escape them.

I was able to use the UTL_MAIL package introduced by Oracle in 10g and it worked very well. Only configuration necessary was setting the smtp_out_server variable in oracle to the mail server (include the port number)of the mail server. Only requires a single method call - much cleaner to implement in PL/SQL as well - and also allows you more control over specific email fields (Subject, for example) and can handle attachments as well.