stored-procedures - 当通过 pl/sql 过程数据将 csv 文件作为附件发送时，附件中的数据被截断并且缺少对齐

Question

我想每天将查询输出作为 excel 附件发送。如果数据较少（大约 40 行和 10 列）工作正常。但是对于 300 行数据（> 32k 个字符），它不起作用（在几行后截断数据和对齐问题）。

我尝试使用 DBMS_LOB_append 而不是字符串连接。（没用）

create or replace PROCEDURE RPT AS 
   l_clob clob; 
   l_bfile   bfile;
    l_fhandle utl_file.file_type;
   l_buffer  VARCHAR2(8192);
 v_count number;
  a_count number;
    k_count number;
 ka_count number;
  currentdate DATE;
    non_work_days_count number;
   BEGIN
    --preparing header
     dbms_lob.createtemporary (l_clob, TRUE); 
 l_clob := l_clob 
    || 'Column1'|| ','
    || 'Column2' || ','
      || 'Column3' || ','
    || 'Column4' || ','
    || UTL_TCP.crlf;

for crq in (select col1,col2,col3,col4 from table where id=1 ) loop
 /* Prepare Details data using Clob */   
l_clob := l_clob 
  || to_clob(crq.COl1)|| ','
  || to_clob(crq.COL2) || ','
  || to_clob(crq.COL3) || ','
  || to_clob(crq.COL4) || ','
  || UTL_TCP.crlf;
  end loop; 

  for crq in (select col1,col2,col3,col4 from table where id=2 ) loop
/* Prepare Details data using Clob */   
 l_clob := l_clob 
  || to_clob(crq.COl1)|| ','
  || to_clob(crq.COL2) || ','
  || to_clob(crq.COL3) || ','
  || to_clob(crq.COL4) || ','
  || UTL_TCP.crlf;
     end loop; 
dbms_output.put_line('Sending mail with attachment ');
      ATTACHMENT_SEND(p_to=> '[e-mail]',
        p_from=> '[e-mail]',
        p_subject=> ' Report ',
        p_text_msg=>'Hi All',   
         p_attach_name =>'report_'||sysdate||'.csv',
        p_attach_mime =>'text/plain',
        p_attach_clob =>l_clob,
        p_smtp_host=>'host.com');


     dbms_lob.freetemporary(l_clob); 

附件_发送：

    create or replace PROCEDURE ATTACHMENT_SEND (p_to          IN VARCHAR2,
                                   p_from        IN VARCHAR2,
                                   p_subject     IN VARCHAR2,
                                   p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                   p_attach_name IN VARCHAR2 DEFAULT NULL,
                                   p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                   p_attach_clob IN CLOB DEFAULT NULL,
                                   p_smtp_host   IN VARCHAR2,
                                   p_smtp_port   IN NUMBER DEFAULT 25)
 AS
 l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
   l_step        PLS_INTEGER  := 24570; -- make sure you set a multiple of 3 
not higher than 24573
 vLine VARCHAR2(100);
  TO_parties varchar2(2000);

  BEGIN
 l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
 UTL_SMTP.helo(l_mail_conn, p_smtp_host);
 UTL_SMTP.mail(l_mail_conn, p_from);


 --Sending to multile ID
 for i in (SELECT LEVEL AS id, REGEXP_SUBSTR(p_to, '[^;]+', 1, LEVEL) AS 
 to_email_name
       FROM dual
       CONNECT BY REGEXP_SUBSTR(p_to, '[^;]+', 1, LEVEL) IS NOT NULL) loop
TO_parties := TO_parties||';'|| i.to_email_name;
 utl_smtp.Rcpt(l_mail_conn,i.to_email_name);

  end loop;

  UTL_SMTP.open_data(l_mail_conn);



  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY 
  HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; 
  boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN
   UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
   UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso- 
   8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   IF p_attach_name IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; 
    name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; 
     filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) 
   LOOP
    UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, 
  i * l_step + 1));
   END LOOP;

   UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || 
   UTL_TCP.crlf);
   UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
    END;

您能否检查一下它被截断的原因，或者建议任何其他可能的方式来发送带有附件的邮件。

标签： stored-proceduresplsqloracle11gclob