Sending Mail From Pl Sql Oracle

21 thoughts on “Sending Mail From Pl Sql Oracle”

• Mail Configuration From PL/SQL Oracle
  
  =======================================

  1. Configure UTL_MAIL

  @?/rdbms/admin/utlmail.sql
  
  @?/rdbms/admin/utlsmtp.sql
  
  @?/rdbms/admin/prvtmail.plb

  2. Set Outgoing Server address (In My case it is localhost)

  alter system set smtp_out_server='localhost';

  3. If required , Then configure ACL

  Create ROLE ACL_ADMIN;
  
  grant Create session to ACL_ADMIN;
  
  grant execute on utl_mail to ACL_ADMIN;
  
  grant execute on utl_smtp to ACL_ADMIN;

  Here we are creating ACL
  
  —————————

  BEGIN
  
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  
  acl => 'acl_admin.xml',
  
  description => 'Permissions to access mail',
  
  principal => 'ACL_ADMIN',
  
  is_grant => TRUE,
  
  privilege => 'connect',
  
  start_date => SYSTIMESTAMP,
  
  end_date => NULL);
  
  COMMIT;
  
  END;
  
  /

  begin
  
  DBMS_NETWORK_acl_ADMIN.ADD_PRIVILEGE(
  
  acl => 'acl_admin.xml',
  
  principal => 'ACL_ADMIN',
  
  is_grant => true,
  
  privilege => 'resolve'
  
  );
  
  COMMIT;
  
  END;
  
  /

  BEGIN
  
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  
  acl => 'acl_admin.xml',
  
  host => '*');
  
  COMMIT;
  
  END;
  
  /

  select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
  
  select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

  4. Now , Grant This Role to ANy user who want to send Mail:

  grant ACL_ADMIN to KSH;

  5. Now , try to send a test mail:

  BEGIN
  
  UTL_MAIL.SEND(sender => 'dbmon@local.com',
  
  recipients => 'dbmon@local.com',
  
  subject => 'Test Mail',
  
  message => 'Hi, This is just a test mail');
  
  EXCEPTION
  
  WHEN OTHERS THEN
  
  RAISE_APPLICATION_ERROR(-20001,'The following error has occured:' ||sqlerrm);
  
  END;
  
  /

• Create ROLE ACL_ADMIN
  *
  ERROR at line 1:
  ORA-65096: invalid common user or role name
  GETTING ABOVE ERROR

• hate this kind of videos with stupid music……………………………..

• How to work please help us

• Hi sir is it applicable for which oracle version onwords?

• kindly guide interactive grid records into email

• ORA-20001: The following error has occured:ORA-06508: PL/SQL: could not find program unit being called
  ORA-06512: at line 8

• Dear
  Thank you for the vedio, but I have question
  In your example I can send email using any sender
  where you put the password of sender email

• THANSK Hi, utl_mail procedure compiling but email not arriving, why :(???

• Hi bro nice video this worked as expected. could you please help me establishing email service using utl smtp by using api username and api password.

  basically utl smtp or utl mail using any kind of authentication

• facing smtp transient error 421 service not availabe

• Kindly resolve this error.
  SQL> BEGIN
  
  2 UTL_MAIL.SEND(sender => 'dbmon@local.com',
  
  3 recipients => 'dbmon@local.com',
  
  4 subject => 'Test Mail',
  
  5 message => 'Hi, This is just a test mail');
  
  6 EXCEPTION
  
  7 WHEN OTHERS THEN
  
  8 RAISE_APPLICATION_ERROR(-20001,'The following error has occured:' ||sqlerrm);
  
  9 END;
  
  10 /
  
  BEGIN
  
  *
  
  ERROR at line 1:
  
  ORA-20001: The following error has occured:ORA-29278: SMTP transient error: 421
  
  Service not available
  
  ORA-06512: at line 8

• I already succed for email setup. but i want to ask. how to rollback all the settings include smtp, acl and etc. all configured before?

• How to send a plsql report over the mail using UTL_smtp?

• how can i get the scripts of utlmail.sql and smtp, and prvtmail?

• Hai sir,

  Thanks for the explanation,

  I have tried my end but I have facing below error
  Ora-29278: SMTP transient error: 421 service not available,

  Could you please help me to resolve the issue

• Hello i have completed same as your video
  but when i m creating
  begin DBMS_NETWORK_ACL_ADMIN.CREATE_ACL then sql eror
  must declare
  identifier must be declare

• how I set it for oracle XE, not able to configure , please help me

• Hi mi friend. I'm as client and when I try to configure return this error <SP2-0310: no se ha podido abrir el archivo "some_file.sql">Besisdes I check the file <C:appMI_USERproduct11.2.0client_1rdbms>and it is empty. It must be run from server side? Me I load some files here to try again? Best regards from Peru.

• Error report:
  
  ORA-20001: The following error has occured:ORA-29278: SMTP transient error: 451 Please try again later.
  
  ORA-06512: at line 8

• Hope it helped You , If yes then share , like and subscribe…

Comments are closed.