Blog

ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL",

I was getting error while trying to send email from database

SQL> declare
  2  begin
  3  UTL_MAIL.SEND(mime_type => 'text/html; charset=us-ascii',
  4                         sender => 'harvey@example.com',
  5                     recipients => 'harvey@example.com',
  6                        subject => 'Test email Harvey',
  7                        message => 'This is a test email from rac01');
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 3


SQL>

What I have found was that the parameter smtp_out_server was not set up

SQL> show parameter  smtp_out_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server                      string
SQL>

By setting the parameter to mail server it worked all good

SQL> alter system set smtp_out_server='mailhost.example.com' scope=both sid='*';

System altered.

SQL> show parameter  smtp_out_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server                      string      mailhost.example.com
SQL> 

SQL> declare
  2  begin
  3  UTL_MAIL.SEND(mime_type => 'text/html; charset=us-ascii',
  4                         sender => 'harvey@example.com',
  5                     recipients => 'harvey@example.com',
  6                        subject => 'Test email Harvey',
  7                        message => 'This is a test email from rac01'); 
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 

Please note that I had to make sure that ACLs are configured right as well

SQL> col acl format a30
SQL> col host format a30
SQL> col principal format a30
SQL> col end_date format a30
SQL> col start_date format a30
SQL> select * from DBA_NETWORK_ACLS;

HOST                           LOWER_PORT UPPER_PORT ACL                            ACLID
------------------------------ ---------- ---------- ------------------------------ --------------------------------
mailhost.csu.edu.au                    25         25 /sys/acls/mailserver_acl.xml   2023FDE3E03E3B45E0511C60040A3C26

SQL> select * from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            ACLID                            PRINCIPAL                      PRIVILE IS_GRANT             INVER START_DATE                     END_DATE
------------------------------ -------------------------------- ------------------------------ ------- -------------------- ----- ------------------------------ ------------------------------
/sys/acls/mailserver_acl.xml   2023FDE3E03E3B45E0511C60040A3C26 HARV                           connect true                 false

SQL> 
SQL> 

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.