Oracle存储过程无法生成csv文件-ORA-06502:PL/SQL:数字或值错误：字符串buff

Question

我有一个程序，从表中读取值，生成一个csv文件并将其发送。这个过程一直运行良好，直到我在DB中输入了新条目，之后它无法生成CSV文件。该过程引发异常“ORA-06502:PL/SQL:numeric or value error:character string buff”，我已尝试打印从表中读取的值，以检查它是否正常工作。

通过打印这些值，我可以看到一个新条目没有被正确读取，我相信这可能是问题的原因。

我已经检查了为什么O Brien之后的值被打印在新行中的原因，我可以看到这个值像这样放在表格中，光标在新行上：

日志中打印的错误：

出现错误：ORA-06512:在“UPDATER.mypro”的第50行

• ORA-06502： PL/SQL：数字或值错误：字符串缓冲区太小

这是我的密码：

create or replace PROCEDURE         mypro AS 

O_ErrorCode  Number;
O_ErrorDesc  VARCHAR2(100);
l_clob2  VARCHAR2 (32767);
l_attach_text2 VARCHAR2 (32767);
l_attach_text_h2 VARCHAR2 (32767);
v_From VARCHAR2(280) := ' abc';
v_Recipient VARCHAR2(280) := 'efggg';
v_Subject VARCHAR2(280) := 'Entry & Details';
v_Mail_Host VARCHAR2(230) := 'abcd';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(200) := chr(13)||chr(10);
FC_SV_STATUS_DESC VARCHAR2(100) := 'open';



CURSOR c2 IS 
   select FC_CA_RECORD_ID,to_char(FC_CA_INPUT_DATE ,'DD-MM-YY')as FC_CA_INPUT_DATE,FC_CA_PAYER,FC_CA_AMOUNT,FC_CA_TYPE,FC_CA_PAYEE,FC_CA_ADD_REMARKS,FC_CA_COMMENTS,FC_CA_ACC_NO,FC_CA_POLICY_NO,to_char(FC_CA_BRANCHCONF_DATE ,'DD-MM-YY')as FC_CA_BRANCHCONF_DATE,FC_CA_CONFIRMED_BY,to_char(FC_CA_SHEETUPDATE_DATE ,'DD-MM-YY')as FC_CA_SHEETUPDATE_DATE,to_char(FC_CA_MAILUPDATE_DATE ,'DD-MM-YY')as FC_CA_MAILUPDATE_DATE,to_char(FC_CA_UPLOAD_TIME ,'DD-MM-YY')as FC_CA_UPLOAD_TIME,FC_CA_UPLOAD_ID FROM Abcd where  FC_CA_STATUS =1 ;

BEGIN

  
l_attach_text_h2 :=
'ID ,INPUT DATE ,PAYER ,AMOUNT ,TYPE ,PAYEE - SORT CODE & BANK ACCOUNT NO ,ADDITIONAL REMARKS ,COMMENTS ,ACCOUNT NUMBER ,POLICY NUMBER ,DATE OF BRANCH CONFIRMATION ,CONFIRMED BY ,SHEETUPDATE DATE ,MAILUPDATE DATE ,DATE-TIME ,USER ID ,STATUS ';

FOR employee_rec2 in c2

LOOP

l_attach_text2 :=      '"' || 
employee_rec2.FC_CA_RECORD_ID        || '","' ||
employee_rec2.FC_CA_INPUT_DATE       || '","' ||
employee_rec2.FC_CA_PAYER            || '","' ||
employee_rec2.FC_CA_AMOUNT           || '","' ||
employee_rec2.FC_CA_TYPE             || '","' ||
employee_rec2.FC_CA_PAYEE            || '","' ||
employee_rec2.FC_CA_ADD_REMARKS      || '","' ||
employee_rec2.FC_CA_COMMENTS         || '","' ||
employee_rec2.FC_CA_ACC_NO           || '","' ||
employee_rec2.FC_CA_POLICY_NO        || '","' ||
employee_rec2.FC_CA_BRANCHCONF_DATE  || '","' ||
employee_rec2.FC_CA_CONFIRMED_BY     || '","' ||
employee_rec2.FC_CA_SHEETUPDATE_DATE || '","' ||
employee_rec2.FC_CA_MAILUPDATE_DATE  || '","' ||
employee_rec2.FC_CA_UPLOAD_TIME      || '","' ||
employee_rec2.FC_CA_UPLOAD_ID        || '","' ||
FC_SV_STATUS_DESC                    || '"'   ||chr(13);

l_clob2 := l_clob2||chr(10)||l_attach_text2;

END LOOP;

l_clob2 := l_attach_text_h2 ||chr(13)|| l_clob2;

DBMS_OUTPUT.put_line(' entries processing  completed...');

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||

'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'Please find the following in the attachments :'|| crlf || -- Message body
'Entry details'|| crlf ||
crlf ||

'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="myfile.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="myfile.csv"'|| crlf ||
crlf ||
 l_clob2  || crlf || -- Content of attachment
crlf ||

'-------SECBOUND--' -- End MIME mail
);

utl_smtp.Quit(v_mail_conn);

DBMS_OUTPUT.put_line('mail send  completed...');

EXCEPTION
  WHEN OTHERS THEN
         O_ErrorCode := SQLCODE;
         O_ErrorDesc := SUBSTR(SQLERRM, 1, 64);
     DBMS_OUTPUT.put_line(O_ErrorCode);
     DBMS_OUTPUT.put_line(O_ErrorDesc);
       SYSTEM.intranet_utils.intranet_log_errors
                                   (
                                    'Exception',
                                    SYSTEM.intranet_utils.intranet_get_errmsg
                                   );

        dba_utils.dba_log_batch ( 'Complete', 'Erred');
        system.intranet_utils.INTRANET_LOG_ERRORS('procedure mypro',
        system.intranet_utils.INTRANET_GET_ERRMSG, 'Error in mypro');
END mypro;

有人能告诉我如何在我的代码中修复这个问题，以及如何在日志中正确打印错误吗？？

Answer 1

包“utl_smtp”中过程“data”的第二个参数的类型为VARCHAR2，因此限制为32767字节。

对于大数据，您必须使用open\u data
write\u data
close\u data

阅读文档

Answer 2

它以前工作但停止工作的原因很可能是您达到了dbms_输出的缓冲区值。默认缓冲区大小为20000（检查文档），超过该值时抛出错误ORU-10027。我建议您删除这些调用-出于这个原因，任何生产代码都不应该调用dbms_输出。