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>
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>
