PL/SQL character buffer overflow in UTL_SMTP

Question

I have a PL/SQL sproc which sends an email, with addressee and such as parameters. We recently migrated it, to a new environment, and switched it from using an internal mail server to using Office 365. In order to do this I needed to upgrade it to use TLS/SSL, which is now working. But it's getting a weird error now.

All of the authentication code works fine, I can transmit the auth, and all the message data, with no issue. But when i call UTL_SMTP.CLOSE_DATA, it throws ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

This section of the code is unchanged from the old environment, where it was working without issue. By this point, I've already concatenated my variables, so I know it's not an issue with my variable sizes. It seems to be something inside the UTL_SMTP package, but that seems to be a compiled package, so I can't even view the stack source to try to figure out what or where the issue is.

Below is our sproc code...

CREATE OR REPLACE PROCEDURE ourschema.SENDMAILTLS
(
    vSENDER  IN VARCHAR2,
    vSENDEE  IN VARCHAR2,
    vSUBJECT IN VARCHAR2,
    vMESSAGE IN VARCHAR2
) AS
    vMAILHOST VARCHAR2(255) := ourschema.GETOPTION('SMTPSRV');
    oSMTP     UTL_SMTP.connection;
    vCRLF     VARCHAR2(2) := chr(13) || chr(10);

    vDATA VARCHAR2(32767);
BEGIN
    vDATA := 'Subject:' || vSUBJECT || vCRLF;
    vDATA := vDATA || 'Date:' || to_char(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || vCRLF;
    vDATA := vDATA || 'From:' || vSENDER || vCRLF;
    vDATA := vDATA || 'Content-Type:text; charset=us-ascii' || vCRLF;
    vDATA := vDATA || 'Reply-To:' || vSENDER || vCRLF;
    vDATA := vDATA || 'Sender:' || vSENDER || vCRLF;
    vDATA := vDATA || vCRLF;
    vDATA := vDATA || vMESSAGE || vCRLF;
    vDATA := vDATA || vCRLF;

    ourschema.LOG('TLS Email sending from ' || vSENDER || ' to ' || vSENDEE || ' via ' || vMAILHOST || '.', vDATA);

    utl_tcp.close_all_connections();

    oSMTP := UTL_SMTP.open_connection(vMAILHOST, TO_NUMBER(ourschema.GETOPTION('SMTPPORT')), wallet_path => 'file:O:\ur\Wallet\Path', wallet_password => 'OurWalletPassword');
    UTL_SMTP.EHLO(oSMTP, vMAILHOST);
    UTL_SMTP.STARTTLS(oSMTP);
    UTL_SMTP.EHLO(oSMTP, vMAILHOST);
    UTL_SMTP.AUTH(oSMTP, 'U******', 'P******', UTL_SMTP.ALL_SCHEMES);

    UTL_SMTP.mail(oSMTP, ourschema.GETOPTION('SMTPADDR'));
    UTL_SMTP.rcpt(oSMTP, vSENDEE);

    UTL_SMTP.open_data(oSMTP);
    UTL_SMTP.write_data(oSMTP, vDATA);
    UTL_SMTP.close_data(oSMTP);
    UTL_SMTP.quit(oSMTP);

    ourschema.LOG('TLS Email sent successfully from ' || vSENDER || ' to ' || vSENDEE || ' via ' || vMAILHOST || '.', vDATA);
END;

The line it is failing on is
UTL_SMTP.close_data(oSMTP);

And this is the test script I'm using. No massive amounts of data that would blow anything out.

begin
  -- Call the procedure
  idsystem.SENDMAILTLS(vSENDER => '[email protected]',
                       vSENDEE => '[email protected]',
                       vSUBJECT => 'Testing Oracle Email',
                       vMESSAGE => 'Did you get this yet?');
end;

And here's the error message... with the stack trace showing it's coming from somewhere deep in the UTL_SMTP package.

But if I say Yes to view the stack source, this is all that comes up for the UTL_SMTP package... so I can't even begin to make heads or tails of how I may be offending it.

Oracle version is 12c Standard, 12.2.0.1.0

Answer 1

Based on the stack trace it looks like the receiver end is sending data greater than 512 characters. This means basically the e-mail that you are sending might have some issue that generates huge data as a response.

The problem might be

1. recepeint email id is nonexistent

2. Email attachment is invalid etc

3. ourschema.GETOPTION('SMTPADDR') should be a valid sender

To understand the issue try sending the same email from your email application and if it success and doesn't generate a large response then please check your configuration.