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

• Kiran Dalvi
• 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

1 2 3 4 5 6

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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>