1 Reply Latest reply: Mar 26, 2013 5:43 AM by fac586 RSS

    Sending Mail with attachment and preserving the order of attachment

    Tushar Lapani
      Hi Everyone!!!!!!
      My requirement is to send Email with attachment in the same order as they were attached by Sender while composing the mail. I want to preserve order of attachment in the mail and when receiver open a mail, the attached files should be reflected in same order as it was on sender side. In my application,the documents being upload are already in my database. So, user just requires to mark the corresponding check box and enter the Sequence number for each attachment. When he clicks on "SEND" button, the Source file is first written from database to OS level and then files are attached from source available on OS . The attached file name could be anything. Based on the Sequence number file should be attached and send. My current code works fine,but problem it is that the Sequence number is not preserved in my attachment . However, when I traced the output of FOR Loop by inserting it in a table " temp_trace" I found that the Sequence is preserved in the order that I want. So, I think that uploaded files are written on the Operating system in same order but they are not attached in same order. Below is the sample code that is I am using to attach file in email. Here, the user_sequence is the sequence number entered by user.

      for i in (select case when user_sequence is not null
      then trim(to_char(user_sequence ,'09'))||'_'||file_name||'.pdf'
      else ||'_'||file_name||'.pdf' end file_name ,
      file_src,length(file_src) len,cd.doc_id,PRIORITY from
      MIM_CLIENT_DOCS CD,(select DOC_ID ,user_sequence from table P_DOC_AND_user_sequence ) TEMP_TAB
      WHERE CD.DOC_ID = TEMP_TAB.DOC_ID order by case when user_sequence is null then 1 else 0 end, file_name)
      loop

      insert into temp_trace values(i.file_name);
      L_OUT := UTL_FILE.FOPEN(v_oracle_dir,i.file_name,'wb',32760);
      .................................................
      end loop;


      I want my output as
      1_first attachment.txt
      2_second_attachment.docx
      3_abc.sql
      _xxx.txt  ------------------ When sequence is not assigned by user That is null at last.

      Unfortunately, I am not getting attachment in mail in this sequence. Can anyone give me suggestion.

      Sorry for stuff essay and thanks in advance!!!!!!!!!!!
        • 1. Re: Sending Mail with attachment and preserving the order of attachment
          fac586
          >

          Please update your forum profile with a real handle instead of "974850".
          My requirement is to send Email with attachment in the same order as they were attached by Sender while composing the mail. I want to preserve order of attachment in the mail and when receiver open a mail, the attached files should be reflected in same order as it was on sender side. In my application,the documents being upload are already in my database. So, user just requires to mark the corresponding check box and enter the Sequence number for each attachment. When he clicks on "SEND" button, the Source file is first written from database to OS level and then files are attached from source available on OS . The attached file name could be anything. Based on the Sequence number file should be attached and send. My current code works fine,but problem it is that the Sequence number is not preserved in my attachment . However, when I traced the output of FOR Loop by inserting it in a table " temp_trace" I found that the Sequence is preserved in the order that I want. So, I think that uploaded files are written on the Operating system in same order but they are not attached in same order. Below is the sample code that is I am using to attach file in email. Here, the user_sequence is the sequence number entered by user.
          Always post code using <tt>\
          ...\
          </tt> tags as described in the FAQ.
          for i in (select case when  user_sequence is not null
          then trim(to_char(user_sequence ,'09'))||'_'||file_name||'.pdf'
          else  ||'_'||file_name||'.pdf' end file_name ,
          file_src,length(file_src) len,cd.doc_id,PRIORITY from 
          MIM_CLIENT_DOCS CD,(select DOC_ID ,user_sequence from table P_DOC_AND_user_sequence ) TEMP_TAB
          WHERE CD.DOC_ID = TEMP_TAB.DOC_ID order by case when user_sequence is null then 1 else 0 end, file_name)                                
          loop
          
          insert into temp_trace values(i.file_name);
          L_OUT := UTL_FILE.FOPEN(v_oracle_dir,i.file_name,'wb',32760);
          .................................................
          end loop;
          I want my output as
          1_first attachment.txt
          2_second_attachment.docx
          3_abc.sql
          _xxx.txt  ------------------ When sequence is not assigned by user That is null at last.

          Unfortunately, I am not getting attachment in mail in this sequence. Can anyone give me suggestion.
          I see no code that attaches anything to email messages. What code is used to do this? Why are the files being written to the file system?