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=<email content>; V_count:=V_body. Length; IF V_count >= 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 >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;