ORACLE EMAIL
Error PLS-00201: identifier 'UTL_MAIL' must be declared solution
a. install package
b. grant privilege
sqlplus sys/xxxx as sysdba
--run the script in C:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN
@utlmail.sql
@prvtmail.plb
--change value of parameter
alter system set smtp_out_server='127.0.0.1' scope=spfile;
--restart the database
shutdown immediate;
startup
2. sys user can sent email
begin
utl_mail.send(sender => '<source email>',
recipients => '<target email>',
message => '<content>',
subject => '<title>');
end;
/
3. not sys user send email
a. grant privilege
SQL> grant execute on utl_tcp to <user>;
Grant succeeded
SQL> grant execute on utl_smtp to <user>;
Grant succeeded
SQL> grant execute on utl_mail to <user>;
Grant succeeded
SQL> grant execute on dbms_network_acl_admin to <user>;
Grant succeeded
b. create and set the ACL
Example
--Create ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'ACLPHMAIN.xml',
description => 'Account control list',
principal => 'THOR_PH',
is_grant => TRUE,
privilege => 'connect');
END;
--Assign host and port for ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'ACLPHMAIN.xml',
host => '127.0.0.1',
lower_port => 25,
upper_port => 25);
END;
--Check the privilege
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
--Check the assign
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
--DROP ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => 'ACLPHMAIN.xml');
END;
--UNASSIGN_ACL host and port for ACL
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
acl 'ACLPHMAIN.xml',
host '127.0.0.1',
lower_port 25,
upper_port 25);
--Add privilege
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'ACLPHMAIN.xml',
principal => 'THOR_PH',
is_grant => TRUE,
privilege => 'connect');
END;
--Delete privilege
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
acl => 'ACLPHMAIN.xml',
principal => 'THOR_PH',
is_grant => FALSE,
privilege => 'connect');
Comments
Post a Comment