5 Replies Latest reply on Aug 16, 2019 7:35 AM by Stefan Jager

    Date fields displaying in different format while writing into Excel from DB

    Lazar

      Hello All,

       

      Oracle DB - 12C

       

      With reference to the previous thread - UTL_SMTP - Write_data is not proper in excel !!  ---> I can able to trigger the mail now. Special Thanks to Marwim.

       

      But now the Date fields are displayed in different form.

       

      Could you guys please help me on this??

       

      Please follow the steps:--

       

      Step 1 --> Create Table:-

      create table test_emp(p_no varchar2(100),id number, name varchar2(100), dob date);
      /
      insert into test_emp values('PNO98546',1,'LAZAR','22-Dec-87');
      insert into test_emp values('PNO98546',2,'THOMAS','22-Jan-76');
      COMMIT;
      /
      

       

      Step 2--> Execute the package:-

      https://technology.amis.nl/wp-content/uploads/2011/02/as_xlsx18.txt

       

      Step 3:- --> Execute my package

       

      CREATE OR REPLACE PACKAGE PK_SMTP_AUTO_MAIL_TEST IS
      
      
      TYPE rec_attachment IS RECORD (
      fileName   VARCHAR2(200),
      fileBLOB   BLOB);
      
      TYPE tbl_attachments IS TABLE OF rec_attachment INDEX BY BINARY_INTEGER;
      g_attachments   tbl_attachments;
      
      PROCEDURE send_automail
      (
      pa_no               IN VARCHAR2);
      
      PROCEDURE send_mail_attach
          (
      pa_no               IN VARCHAR2
          ,recipient              IN VARCHAR2
          ,cc                     IN VARCHAR2 := NULL
          ,bcc                    IN VARCHAR2 := NULL
          ,sender                 IN VARCHAR2
          ,attachments            IN tbl_attachments := g_attachments
          );
      
      
      
      END PK_SMTP_AUTO_MAIL_TEST;
      /
      SHOW ERRORS;
      
      
      CREATE OR REPLACE PACKAGE BODY PK_SMTP_AUTO_MAIL_TEST IS
      
      
      PROCEDURE send_automail
      (
      pa_no               IN VARCHAR2)
      IS
      
      
      v_sql          VARCHAR2(32000);
      attachments    PK_SMTP_AUTO_MAIL_TEST.tbl_attachments;
      
      
      BEGIN
          v_sql := 'SELECT p_no,id, name, dob 
      FROM test_emp 
      WHERE p_no ='''||PA_NO||'''';
                      
          as_xlsx.query2sheet(v_sql);
      
      
          attachments(1).fileBLOB := as_xlsx.finish();
          attachments(1).fileName := 'Report_'||PA_NO||'.xlsx';
      
          PK_SMTP_AUTO_MAIL_TEST.send_mail_attach(
              PA_NO,
              'abc@gmail.com',-- Recipient
              'abc@gmail.com',-- Recipient of copy
              'abc@gmail.com',-- Recipient of blind copy
              'abc@gmail.com'-- Sender
              ,attachments);
      
      END send_automail;
      
      
      PROCEDURE send_mail_attach
          (
      pa_no               IN VARCHAR2
          ,recipient              IN VARCHAR2
          ,cc                     IN VARCHAR2 := NULL
          ,bcc                    IN VARCHAR2 := NULL
          ,sender                 IN VARCHAR2
          ,attachments            IN tbl_attachments := g_attachments
          )
      IS
      
      
          conn                utl_smtp.connection := NULL;
          existsAttachment    BOOLEAN := attachments.COUNT > 0;
      workLength          PLS_INTEGER := 48;
      g_boundary          VARCHAR2(256) := 'MAM.e3831dfcf821d45d9bf900b4745ff';
      
      BEGIN
      
      
          g_boundary := 'MAM.'||dbms_random.string('X',29);
         
      conn := UTL_SMTP.open_connection('localhost', 25);
      UTL_SMTP.helo(conn, 'localhost');
      UTL_SMTP.mail(conn, 'abc@gmail.com');
      UTL_SMTP.rcpt(conn, 'abc@gmail.com');
      
      
      UTL_SMTP.open_data(conn);
      
      
      UTL_SMTP.write_data(conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'To: ' || 'abc@gmail.com' || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'From: ' || 'abc@gmail.com' || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'Subject: ' || 'Report -'||PA_NO|| UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'Reply-To: ' || 'abc@gmail.com' || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
      
          
          IF existsAttachment THEN
              utl_smtp.write_data(conn,'Content-Type: multipart/mixed; boundary="' || 'mixed_' ||g_boundary || '"' || utl_tcp.crlf);
              utl_smtp.write_data(conn,utl_tcp.crlf);
              utl_smtp.write_data(conn,utl_tcp.crlf);
              utl_smtp.write_data(conn,utl_tcp.crlf);
              utl_smtp.write_data(conn,'--' || 'mixed_' ||g_boundary || utl_tcp.crlf);
          END IF;
      
      UTL_SMTP.write_data(conn, 'Content-Type: multipart/mixed; boundary="' || g_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, '--' || g_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data(conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
      UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || '<HTML>');
      UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || '<BODY>Hello All,<br><br>Please find attached Report for the <b>'||PA_NO||'</b>.'||'<br><br>');
      UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || '<br>'||  'Regards,<br>'||  'Lazar <br><br> ');   
      UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || '</BODY>');
      UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF || '</HTML>');
      UTL_SMTP.write_data(conn, UTL_TCP.crlf || UTL_TCP.crlf);
      
      
         -- IF existsAttachment THEN
              utl_smtp.write_data(conn,utl_tcp.crlf);
              utl_smtp.write_data(conn,utl_tcp.crlf);
      
      
            --  FOR i IN attachments.FIRST..attachments.LAST LOOP
                  utl_smtp.write_data(conn,'--' || 'mixed_' ||g_boundary || utl_tcp.crlf);
                  --appendFile(conn,attachments(i),i);
      
      
      --append file starts
      
      utl_smtp.write_data(conn,'Content-Type: application/octet-stream; name="'|| attachments(1).fileName||'"'|| utl_tcp.crlf);
      utl_smtp.write_data(conn,'Content-Disposition: attachment; filename="'|| attachments(1).fileName||'"'|| utl_tcp.crlf);
      
      
      utl_smtp.write_data(conn,'Content-Transfer-Encoding: base64'|| utl_tcp.crlf);
      utl_smtp.write_data(conn,utl_tcp.crlf);
      
      IF  attachments(1).fileBLOB IS NOT NULL THEN
      FOR i IN 0 .. TRUNC((dbms_lob.getlength( attachments(1).fileBLOB) - 1 )/workLength) LOOP
      utl_smtp.write_data(
      conn
      ,utl_raw.cast_to_varchar2(
      utl_encode.base64_encode(
      dbms_lob.substr(
        attachments(1).fileBLOB
      ,workLength
      ,i * workLength + 1
      )
      )
      )
      );
      END LOOP;
      ELSE
      RAISE_APPLICATION_ERROR(-20001,'Lesser attachment');
      END IF;
      
      --append file ends 
      
      
                  utl_smtp.write_data(conn,utl_tcp.crlf);
             -- END LOOP;
      
      
              utl_smtp.write_data(conn,'--' || 'mixed_' ||g_boundary || '--' || utl_tcp.crlf);
              utl_smtp.write_data(conn,utl_tcp.crlf);
          --END IF;
      
      
          utl_smtp.close_data(conn);
          utl_smtp.quit(conn);
      
      
      END send_mail_attach;
      
      
      END PK_SMTP_AUTO_MAIL_TEST;
      /
      SHOW ERRORS;
      

       

      Step 4:-

      BEGIN
      PK_SMTP_AUTO_MAIL_TEST.send_automail('PNO98546');
      END;
      
      

       

      Result:-

        • 1. Re: Date fields displaying in different format while writing into Excel from DB
          BEDE

          What I did when getting data from the database to excel using ADO was that I fetched not the date data type, but to_char(x_date,'dd.mm.yyyy'). And that's the least of the problems you may have. Even worse can be with decimal separator and group separator. You may have to set the language for Office to agree with what you have in the database.

          • 2. Re: Date fields displaying in different format while writing into Excel from DB
            Gaz in Oz

            As pointed out by me in the "other" thread":

            That's an excel display issue. You need to highlight the column -> Format cells -> date...

            excel is currently displaying the "raw" date, i.e. not formatted, hence the non-recognisable "date" string and why formatting the column cells will rectify it.

            BEDE's workaround might also work for you... as long as your excel locale matches what ever date format you choose to pull.

            • 3. Re: Date fields displaying in different format while writing into Excel from DB
              Stefan Jager

              As bede says: you're selecting a date without any formatting. Which means it depends on the package writing the Excel what ends up in the Excel. Check what happens in there. It also means you are depending on implicit conversions, which you also do in your create table statement:

               

              insert into test_emp values('PNO98546',1,'LAZAR','22-Dec-87'); 

              You are inserting a piece of text into a date. Oracle is forgiving enough that it will allow you to do so (I frequently wish it wouldn't), but it does depend on your NLS setting whether it will work or not (somebody with different NLS setting might run into a problem...).

              Don't ever depend on implicit conversions, so use to_date here and use to_char in the select statement you feed into the excel-package.

              • 4. Re: Date fields displaying in different format while writing into Excel from DB
                Lazar

                Answer was as simple as that..

                 

                Wondering why my Mind didn't go around in this simple way..

                • 5. Re: Date fields displaying in different format while writing into Excel from DB
                  Stefan Jager

                  Because you are making things difficult for yourself. You want to send an email with an excel attachment, and you develop everything in a single procedure in one go. If you had started with small babysteps, things would not have been as complicated. So: Develop something that sends an email, and make sure that works 100% as exepcted. Then add the option to send an email with a simple attachment (just a file from disk or something), and again make sure that works 100%. Then develop something that creates an Excel, and make sure that works as expected, including on the Excel side. Create everything as single-goal, focused procedures and/or functions. And only when all the parts are working, add them together for the full functionality. Modularity is the key word here. Don't try everything in one go, and especially not in one big pile of sourcecode. That's only making things difficult for yourself.

                  1 person found this helpful