- 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 => '[email protected]', 5 recipients => '[email protected]', 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 => '[email protected]', 5 recipients => '[email protected]', 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>