Wednesday, February 8, 2012

UTL_MAIL INSTALLATION AND ADDING INTO ACL

cd $ORACLE_HOME
cd rdbms/admin/
sqlplus / as sysdba

SQL> @utlmail.sql
then
SQL> @prvtmail.plb

SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON UTL_MAIL TO system;

SQL>ALTER SYSTEM SET smtp_out_server = 'mail.srv' SCOPE=BOTH;

SQL>shutdown immediate;
SQL>startup;


when database is ready execute the below scripts to create and give privlages;
begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

giving privilage;
begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;
last step ;
assing ACL
begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'smtp server host name or address'
  );
  commit;
end;

Now u can send mail with ult_mail...

begin
  utl_mail.send(
  sender     => 'person@com.tr',
  recipients => 'person2@com.tr',
  message    => 'hi'
  );
  commit;
end;

if u wanna see who can send mail with UTL_MAIL ;

select owner, object_type from all_objects where object_name = 'UTL_FILE' ;

No comments:

Post a Comment