SOLVED

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

Asked by OracleDba••14 views•oracle

#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
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>
OracleDba•

Post Your Solution