Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Unable to send large excel file through pl sql

user11334489Mar 17 2024

Hi Team,

Able to send small excel file through pl/sql, but unable to send large excel file through pl/sql.

It show below error.

Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 29
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.

Code

DECLARE
p_to VARCHAR2(100):= 'to_test@gmail.com';
p_from VARCHAR2(100):='from_test@gmail.com';
p_smtp_host VARCHAR2(100):='test_smtp_host.com';
v_subject VARCHAR2(2000) := 'Test Mail from PL/SQL';
p_smtp_port NUMBER(10):=25;
l_mail_conn utl_smtp.connection;
crlf varchar2(2) := chr(13)||chr(10);
v_boundary VARCHAR2(255);
v_attachment_name VARCHAR2(255) := 'Large_Excel_File.xlsx'; -- Name of the attachment
v_attachment clob;
v_msg VARCHAR2(100);
BEGIN

v_attachment :=get_clob_file_test;---<<Get the excel File>>---
v_boundary := '=====' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF6') || '=====';
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);
utl_smtp.rcpt(l_mail_conn,p_to);

utl_smtp.data(l_mail_conn,
'From: '|| p_from || crlf ||
'Subject: '|| v_subject || crlf ||
'To: '|| p_to || crlf ||
'MIME-Version: 1.0' || UTL_TCP.CRLF ||
'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF ||
'--' || v_boundary || UTL_TCP.CRLF ||
'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' || UTL_TCP.CRLF ||
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF ||
'Content-Disposition: attachment; filename="' || v_attachment_name || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF ||
v_attachment || UTL_TCP.CRLF || UTL_TCP.CRLF ||
'--' || v_boundary || '--'
);
utl_smtp.quit(l_mail_conn);
DBMS_OUTPUT.PUT_LINE('Mail Send Successfully..');
END;

How to resolve it. I am using Oracle 11g.

Thanks in advance.

Best Regards,

Abu

Comments
Post Details
Added on Mar 17 2024
4 comments
153 views