UTL_MAIL quirks

I prefer using using UTL_MAIL over UTL_SMTP as I can use the smtp_out_server from the v$parameter without having code the value of smtp host in my package . 

Few weeks back , I ran it into an issue where my package was failing as the attachment supposedly exceeded 32K. 

Error report -
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 747
ORA-06512: at "DUSER.EMAIL_PKG", line 638
ORA-06512: at line 1
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.



I have been using UTL_MAIL  for years and knew that it had 32K size limitation . What did not I know/realize  was that it was the size of encoded file .  

Here is an example . I have a file test.txt that that is 23609  bytes .  But when it is encoded its size was 32462 bytes . As this file was less than 32K , the process ran fine. 

 Directory of d:\reports


11/11/2016  02:59 PM            23,609 test.TXT
               1 File(s)         23,609 bytes


SQL> SET serveroutput ON
SQL> DECLARE
  2  BEGIN
  3    test_email('test.TXT', 'test.TXT' , 'test@email.com' , 'testre@email.com' , 'test' , 'Test Body', 'REPORTS');
  4  END ;
  5  /
Original Size: 23609 bytes
Encoded Size: 32462 bytes

PL/SQL procedure successfully completed.



Let us look at another file ( test1.txt ) that is 25K  and let us run our script for this. As the encoded file was over 32K , my script erred out.  

11/01/2016  10:25 AM            24,645 test1.TXT
               1 File(s)         24,645 bytes

SQL> SET serveroutput ON

SQL> DECLARE

  2  BEGIN

  3    test_email('test1.TXT', 'test1.TXT' , 'test@email.com' , 'testre@email.com' , 'test' , 'Test Body', 'REPORTS');

  4  END ;

  5  /

Original Size: 24645 bytes

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

ORA-06512: at "SYS.UTL_ENCODE", line 243

ORA-06512: at "DUSER.TEST_EMAIL", line 22

ORA-06512: at line 3

I was able to simulate the error that UTL_EMAIL was producing . 

Here is the script to simulate . BTW , it was documented in metalink  ( Bug 9038705)  

CREATE OR REPLACE PROCEDURE test_email(

    FileName       IN VARCHAR2,

    AttachmentName IN VARCHAR2 ,

    SentFrom       IN VARCHAR2 ,

    SentTo         IN VARCHAR2 ,

    Subj           IN VARCHAR2 ,

    Mesg           IN VARCHAR2,

    FileDir        IN VARCHAR2 )

AS

  FileHandle BFILE;

  Buffer RAW(32767);

  EncodedBuffer RAW(32767);

  OrgSize INTEGER;

  Offset  INTEGER := 1;

BEGIN

  FileHandle := BFILENAME(FileDir, FileName);

  OrgSize    := DBMS_LOB.GETLENGTH(FileHandle);

  DBMS_LOB.FILEOPEN(FileHandle, DBMS_LOB.FILE_READONLY);

  DBMS_LOB.READ(FileHandle, OrgSize, Offset, Buffer);

  DBMS_LOB.FILECLOSE(FileHandle);

  DBMS_OUTPUT.PUT_LINE(' Original Size: ' || OrgSize || ' bytes' );

  EncodedBuffer := UTL_ENCODE.BASE64_ENCODE(Buffer);

  DBMS_OUTPUT.PUT_LINE(' Encoded Size: ' || UTL_RAW.LENGTH(EncodedBuffer) || ' bytes' );

END test_email;

For this use case , I went back to using UTL_SMTP. 

Learning day by day :-) .