CODEWITHSUNDEEP
oracle-databaseplsql
abbott
abbott

Reputation: 11

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 the 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' || ':');

Upvotes: 1

Views: 3755

Answers (3)

joe mcglynn
joe mcglynn

Reputation: 11

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);
.....

Upvotes: 1

abbott
abbott

Reputation: 11

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.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

It may be getting interpreted as a header

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

Upvotes: 1

Related Questions