8 Replies Latest reply: Feb 10, 2010 7:43 AM by 635294 RSS

    Send OS File in email using UTL_MAIL

    635294
      I've been generating reports and delivering them through email using UTL_MAIL.SEND_ATTACH_VARCHAR. I build the entire HTML structure and send it. I started to receive errors regarding missing periods in my digits, so I decided to save this HTML string into a file and I do that superbly.

      Now, that I wish to send this saved HTML file ('rep.html') saved in a local directory in my server I try and use the UTL_MAIL.SEND_ATTACH_RAW and I don't seem to be sending this mail with a html file as an attachment.

      Current Code:

      DECLARE

      os_file                         UTL_FILE.FILE_TYPE;
      virtual_dir                    VARCHAR2(30);
      os_file_name               VARCHAR2(20);

      file_raw_mail                RAW(32727);

      BEGIN

      virtual_dir := 'DIR_VIRT';
      os_file_name := 'rep.html';

      os_file := UTL_FILE.FOPEN(virtual_dir,os_file_name,'R');
      UTL_FILE.GET_RAW(os_file,file_raw_mail,32727);

      BEGIN

      UTL_MAIL.SEND_ATTACH_RAW('somebody@mail.com',
      *'someone@mail.com',*
      NULL,
      NULL,
      *'Report',*
      NULL,
      *'text/plain; charset=iso-8859-1',*
      *3,*
      file_raw_mail,
      TRUE,
      *'text/html',*
      *'report.html');*

      END;

      END;

      In the attach_mime_type I've also used 'application/octet' and still I receive nothing.

      The anonymous block completes itself successfully, and I receive no email.

      HELP PLEASE!!!
        • 1. Re: Send OS File in email using UTL_MAIL
          548849
          Can you put a debugger and see where it's breaking?

          use dbms_output.put_line();
          • 2. Re: Send OS File in email using UTL_MAIL
            635294
            I've placed DBMS_OUTPUT's and still it doesn't show any alerts, warning or errors, it executes this PL/SQL Anonymous Block without a hassle, the problem is I am not receiving this email, and, I don't know if I'm declaring my raw file correctly and sending it correctly. Please help with my statement.
            • 3. Re: Send OS File in email using UTL_MAIL
              548849
              Hi

              Is the issue resolved?

              Regards.
              • 4. Re: Send OS File in email using UTL_MAIL
                635294
                No. It's still not resolved. I have the .html file in a local folder in my server, for which I have created a Directory in my database. I want to send this .html file through an email using UTL_MAIL.
                • 5. Re: Send OS File in email using UTL_MAIL
                  Billy~Verreynne
                  Simplify it - send a plain text e-mail. No attachments. Make sure it works.

                  Change it to html text, no attachments. Repeat test.

                  Now add a text attachment (plain text string created in PL/SQL). Repeat test.

                  Change text attachment to raw attachment (again staying in PL/SQL).

                  When all this works, add the raw processing of an external file as attachment. Also note that raw implies binary data. This cannot be transported as is over a network layer as the binary implementation differ between systems. So to keep it intact, the binary data is usually encoded into 7bit ASCII, transported and decoded back to binary on the other side. Think UTL_MAIL does it.. but make sure of that.

                  PS. I see you want to attach and send html data - that is not raw (binary) data. That is text data (Mime type is text/html). Do not use raw processing on text data.

                  Edited by: Billy Verreynne on Feb 9, 2010 6:41 PM
                  • 6. Re: Send OS File in email using UTL_MAIL
                    635294
                    I've sent this mail message as a html text attachment, as plain text. My problem is with how to read this file and attach it to my utl_mail.send_attach_raw procedure. Could you give me an example of how to do this.
                    • 7. Re: Send OS File in email using UTL_MAIL
                      Billy~Verreynne
                      fperezhn wrote:
                      I've sent this mail message as a html text attachment, as plain text. My problem is with how to read this file and attach it to my utl_mail.send_attach_raw procedure. Could you give me an example of how to do this.
                      Here's a basic example of how load a text file into a CLOB:
                      declare
                              c               clob;
                              warning         varchar2(4000);
                              srcOffset       integer := 1;
                              dstOffset       integer := 1;
                              langContext     integer := 0;
                              charSetID       integer := 0;
                              fh              bfile;
                      begin
                              DBMS_LOB.CreateTemporary( c, true );
                      
                              fh := BFileName( 'LOAD_DIR', 'xdbconfig.xml' );
                              DBMS_LOB.FileOpen( fh, DBMS_LOB.FILE_READONLY );
                      
                              DBMS_LOB.LoadCLOBFromFile(
                                      c,
                                      fh,
                                      DBMS_LOB.LOBMAXSIZE,
                                      dstOffset, srcOffset, charSetID, langContext,
                                      warning
                              );
                      
                              DBMS_LOB.FileClose( fh );
                      
                              .. do interesting stuff with the CLOB..
                      
                              DBMS_LOB.FreeTemporary( c );
                      end;
                      /
                      • 8. Re: Send OS File in email using UTL_MAIL
                        635294
                        Billy  Verreynne  wrote:
                        fperezhn wrote:
                        I've sent this mail message as a html text attachment, as plain text. My problem is with how to read this file and attach it to my utl_mail.send_attach_raw procedure. Could you give me an example of how to do this.
                        Here's a basic example of how load a text file into a CLOB:
                        declare
                        c               clob;
                        warning         varchar2(4000);
                        srcOffset       integer := 1;
                        dstOffset       integer := 1;
                        langContext     integer := 0;
                        charSetID       integer := 0;
                        fh              bfile;
                        begin
                        DBMS_LOB.CreateTemporary( c, true );
                        
                        fh := BFileName( 'LOAD_DIR', 'xdbconfig.xml' );
                        DBMS_LOB.FileOpen( fh, DBMS_LOB.FILE_READONLY );
                        
                        DBMS_LOB.LoadCLOBFromFile(
                        c,
                        fh,
                        DBMS_LOB.LOBMAXSIZE,
                        dstOffset, srcOffset, charSetID, langContext,
                        warning
                        );
                        
                        DBMS_LOB.FileClose( fh );
                        
                        .. do interesting stuff with the CLOB..
                        
                        DBMS_LOB.FreeTemporary( c );
                        end;
                        /
                        Right after you close the CLOB (DBMS_LOB.FileClose( fh );), Can I use this file to send it through UTL_MAIL.send_attach_raw???

                        Ex:

                        UTL_MAIL.SEND_ATTACH_RAW('sender@server.com','recipient@server.com',NULL,NULL,'Mail with file attachment',NULL,'text/plain',3,fh,TRUE,'file/xml','xdbconfig.xml');