Stored procedure exception : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Question

The PLSQL procedure reads value from table and mails them as CSV file . Due to some reason its throwing the below error :

Error raised: ORA-06512: at "EMAIL_DUMP", line 73

• ORA-06502: PL/SQL: numeric or value error: character string buffer too small

the line 73 on code :

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

I have looked at the line and thought changing the data type of those variables to clob might fix the issue .But after changing the data type the same error hits at below line :

utl_smtp.WRITE_DATA(v_Mail_Conn,

Here is my code :

 create or replace PROCEDURE       EMAIL_DUMP AS 

l_clob2  VARCHAR2 (32767);
l_attach_text2 VARCHAR2 (32767);
l_attach_text_h2 VARCHAR2 (32767);

v_From VARCHAR2(280) := 'ecd.com';
v_Recipient VARCHAR2(280) := 'abc.com';
v_Subject VARCHAR2(280) := ' Entry  Details';
v_Mail_Host VARCHAR2(230) := 'internal-mail';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2 (32767) := chr(13)||chr(10);
FC_SV_STATUS_DESC VARCHAR2(100) := 'open';

Record_id  Number ;
Input_date varchar2(100);
Payer varchar2(100);
Amount varchar2(100);
Trans_Type varchar2(100);
Payee  varchar2(100);
Remarks  varchar2(500);
Comments varchar2(500);
Acc_no varchar2(100);
Policy_no varchar2(100);
Branch_of date ;
Confirmed varchar2(100);
Sheet_update date;
Mail_update date;
Upload_time  date;
Upload_id varchar2(100);

CURSOR c2 IS 
   select FC_CA_RECORD_ID as Record_id,FC_CA_INPUT_DATE as Input_date,FC_CA_PAYER as Payer,FC_CA_AMOUNT as Amount,FC_CA_TYPE as Trans_Type,FC_CA_PAYEE as Payee,FC_CA_ADD_REMARKS as Remarks,FC_CA_COMMENTS as Comments,FC_CA_ACC_NO as Acc_no,FC_CA_POLICY_NO as Policy_no,FC_CA_BRANCHCONF_DATE as Branch_of,FC_CA_CONFIRMED_BY as Confirmed,FC_CA_SHEETUPDATE_DATE as Sheet_update,FC_CA_MAILUPDATE_DATE as Mail_update,FC_CA_UPLOAD_TIME as Upload_time,FC_CA_UPLOAD_ID as Upload_id into Record_id,Input_date,Payer,Amount,Trans_Type,Payee,Remarks,Comments,Acc_no,Policy_no,Branch_of,Confirmed,Sheet_update,Mail_update,Upload_time,Upload_id FROM ABC where  FC_CA_STATUS =2 ;

BEGIN

l_attach_text_h2 :=
'ID ,INPUT_DATE ,PAYER ,AMOUNT ,TYPE ,PAYEE-SORTCODE_&_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.Record_id        || '","' ||
employee_rec2.Input_date       || '","' ||
employee_rec2.Payer            || '","' ||
employee_rec2.Amount           || '","' ||
employee_rec2.Trans_Type       || '","' ||
employee_rec2.Payee            || '","' ||
employee_rec2.Remarks          || '","' ||
employee_rec2.Comments         || '","' ||
employee_rec2.Acc_no           || '","' ||
employee_rec2.Policy_no        || '","' ||
employee_rec2.Branch_of        || '","' ||
employee_rec2.Confirmed        || '","' ||
employee_rec2.Sheet_update     || '","' ||
employee_rec2.Mail_update      || '","' ||
employee_rec2.Upload_time      || '","' ||
employee_rec2.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;

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.OPEN_DATA(v_Mail_Conn);

utl_smtp.WRITE_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
'CMTL Entry details & Cash 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.CLOSE_DATA(v_mail_conn);
utl_smtp.Quit(v_mail_conn);

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

EXCEPTION
  WHEN OTHERS THEN
        DBMS_OUTPUT.put_line ( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);
        system.intranet_utils.INTRANET_LOG_ERRORS('procedure EmailDump',
        system.intranet_utils.INTRANET_GET_ERRMSG, 'Error in EmailDump');
END EMAIL_DUMP;

Can anyone help me solve this ?

Answer 1

I solved the issue ... problem was my that my content size was larger than 32KB . So I slipt the data like this

  v_len := DBMS_LOB.getlength(l_clob2);
  v_index := 1;

  WHILE v_index <= v_len
  LOOP
    UTL_SMTP.write_data(v_Mail_Conn, DBMS_LOB.SUBSTR(l_clob2, 32000, v_index));
    v_index := v_index + 32000;
  END LOOP;