How to Email large file (more than 32KB) using Oracle?

This document briefly explains about how to send an email with Email with large file more than 32KB of data using oracle. Emails can be sent using built in UTL_SMTP package in Oracle. The mail recipient, subject of the mail and the contents has to be provided. This can be done by the following methods using UTL_SMTP in Oracle.

Opening a new connection for an SMTP server.
UTL_SMTP.OPEN_CONNECTION ();
E.g. v_conn:= UTL_SMTP.OPEN_CONNECTION (‘smtpconnection’);

Initializing the connection.
UTL_SMTP.helo :=();
E.g. UTL_SMTP.helo :=( v_conn,’jharaphula.com’);

Initiates the mail transaction within the SMTP server.
UTL_SMTP.mail :=();
E.g. UTL_SMTP.mail :=( v_conn,’test@jharaphula.com’);

Specifies the recipient of an email message.
UTL_SMTP.rcpt ();
E.g. UTL_SMTP.rcpt (‘to@jharaphula.com’);

Creating a new session to write an email.
UTL_SMTP.open_data ();
E.g. UTL_SMTP.open_data (v_conn);

Writing an e-mail message using the above session.
UTL_SMTP.write_data ();
E.g. UTL_SMTP.write_data (v_conn, ’body’);

Closing the session once the email message has finished writing.
UTL_SMTP.close_data ();
E.g. UTL_SMTP.close_data (v_conn);

Closes the SMTP Connection.
UTL_SMTP.quit ();
E.g. UTL_SMTP.quit (v_conn);

During Email large file Problem Faced

The content of the email is stored in a variable with VARCHAR data type. The length of varchar is 32kb. If the content exceeds more than 32Kb the following error occurs “ORA-06502: PL/SQL: numeric or value error: character string buffer too small” error.

We can use CLOB data type which can hold data up to 4 GB. Using CLOB, we will be able to write the email with the content of 4 GB without any errors. However while sending an email using UTL_SMTP.write method, the same error occurs. (“ORA-06502: PL/SQL: numeric or value error: character string buffer too small”). This is because, UTL_SMTP.write method does not allow to write the content of the size more than 32kb at a time.

Resolution

The below steps will help us to avoid the issue.

When an email body content exceeds 32kb, it can be split and stored in an array variable of varchar data type. Then the array count can be used to loop the same and data can be written into the email using UTL_SMTP.write method.

The UTL_SMTP.write method allows to write 32kb of data at a time and it will concatenate the content of the email before sending.

// declaring array variable
DECLARE  
TYPE arr IS VARRAY (5) of VARCHAR (32000);
V_body                 VARCHAR (32000);
V_body_array        arr: = arr (5);
V_count                NUMBER;
V_counter             NUMBER: =1;
V_conn                 UTL_SMTP.CONNECTION;
V_arrlen               NUMBER;

// Split the mail content to store it in an array variable when the limit exceeds 32KB.
FOR CONDITION 
LOOP
V_body=&lt;email content&gt;;
V_count:=V_body. Length;
IF V_count &gt;= 30000 THEN
V_body_array. Extend;
V_body_array (v_counter):= v_body;
V_counter:= v_counter +1;
V_count:=0;
V_body:= null;
END IF;
END LOOP;

//initializing the counter
V_counter:=1;
//get array count to assign the variable
V_arrlen:= V_body_array. Count;

//Using while loop to write the data to the UTL_SMTP based on the array count.
WHILE V_arrlen &gt;0  
LOOP
UTL_SMTP.write_data (V_conn, V_body_array (V_counter));
V_counter:= V_counter +1;
V_arrlen:= V_arrlen -1;
END LOOP;

Updates

During Email large file Problem Faced

Resolution