CODEWITHSUNDEEP
oracle-databasesmtp
Raj
Raj

Reputation: 11

UTL_SMTP.WRITE_DATA - returning double DOTs in Oracle

I am trying to send the below format content as a mail attachment in oracle.

ADV|ESCROW|514838|20130823
CRN|SF|20130727|20130823|20130823|0.00|-25.28|0.00|0.00|0.00|-25.28
CCT|B|0.00|*
CCT|E|0.00|*
CCT|I|0.00|*
CCT|N|0.00|*
CCT|R|0.00|*
CCT|S|0.00|*
CRN|SF|20130726|20130823|20130823|0.00|35638.70|0.00|0.00|0.00|35638.70
CCT|B|0.00|*
CCT|E|0.00|*
CCT|I|0.00|*
CCT|N|0.00|*
CCT|R|0.00|*
CCT|S|0.00|*
PRN|SF|20130725|20130822|0.00
PCT|B|0.00|*
PCT|E|0.00|*
PCT|I|0.00|*
PCT|N|0.00|*
PCT|R|0.00|*
PCT|S|0.00|*
CRN|SF|20130725|20130823|20130822|0.00|1672.95|0.00|0.00|0.00|1672.95
CCT|B|0.00|*
CCT|E|0.00|*
CCT|I|0.00|*
CCT|N|0.00|*
CCT|R|0.00|*
CCT|S|0.00|*

These contents are huge in size. Its almost 109250 characters. So i used the below code to send this text.

l_offset  number := 1;
l_amount number := 1500;    
while l_offset < dbms_lob.getlength(l_in_mail_attach) loop
   utl_smtp.write_data(smtp_connection,
                       dbms_lob.substr(l_in_mail_attach,l_amount,l_offset));
   l_offset  := l_offset + l_amount ;
   l_amount := least(1900,dbms_lob.getlength(l_in_mail_attach) - l_amount);
 end loop;

I have received the attchment in mail succesfully. But the issue is a dot (.) is appended in the text. See below text PCT|R|0..00|* this should be PCT|R|0.00|* .

PRN|R1|20130606|20130819|0.00
PCT|B|0.00|*
PCT|B|0.00|*
PCT|E|0.00|*
PCT|E|0.00|*
PCT|I|0.00|*
PCT|I|0.00|*
PCT|N|0.00|*
PCT|N|0.00|*
PCT|R|0..00|*
PCT|R|0.00|*
PCT|S|0.00|*
PCT|S|0.00|*

Can any one advice how to fix this issue.

Upvotes: 1

Views: 409

Answers (1)

Doug
Doug

Reputation: 31

The dot duplication occurs when you have a leading dot at the beginning of one of your substrings.

According to: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_smtp.htm#ARPLS71529 The application must ensure that the contents of the body parameter conform to the MIME(RFC822) specification.

The DATA routine terminates the message with a . sequence (a single period at the beginning of a line), as required by RFC821. It also translates any sequence of . (single period) in the body to .. (double period). This conversion provides the transparency as described in Section 4.5.2 of RFC821.

Try hiding the dot from the dot duplicator when writing message body and attachments by replacing:

utl_smtp.write_data(smtp_connection,
                       dbms_lob.substr(l_in_mail_attach,l_amount,l_offset));

with:

utl_smtp.write_RAW_data
( smtp_connection
, utl_raw.cast_to_raw( dbms_lob.substr ( l_in_mail_attach, l_amount, l_offset ) )
);

Upvotes: 1

Related Questions