ORA-06512: at “SYS.UTL_MAIL”, line 654 ORA-06512: at “SYS.UTL_MAIL”,

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 21 Nov, 2021
  • 0 Comments
  • 1 Min Read

ORA-06512: at “SYS.UTL_MAIL”, line 654 ORA-06512: at “SYS.UTL_MAIL”,

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>