Forum Stats

  • 3,873,311 Users
  • 2,266,534 Discussions
  • 7,911,502 Comments

Discussions

emails not sending in regular interval from oracle

2

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,551 Red Diamond
    edited Feb 28, 2017 5:11AM

    a) how are you executing this?

    b) ensure you include a commit before the exception (i.e. at the end of the execution block) to deal with any other DML taking place (does any of the code in that procedure call any other things that do DML?)

  • user12251389
    user12251389 Member Posts: 335 Blue Ribbon
    edited Feb 28, 2017 5:20AM

    I am executing the procedure in the normal way :

    EXECUTE EXECUTE_SEND_MAIL_SMTP

    I tried to include commit before the exception and also commit in the exception handing and now it works. The procedure  does not call any other things that do DML.

    commit;---------------------------------------------------------------------------------------------------------------------------------------------------------------EXCEPTION WHEN OTHERS THEN  INSERT INTO SEND_MAIL_SMTP_EXCEPTION values('MAIL FAILED ERROR',err_code|| ' ' || err_msg,SYSDATE);  commit;  RAISE;

    But just want to know will it cause any transaction issue in future as i am using the commit before the exception and also commit in the exception handing. The other code looks same what i have mentioned in the question.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 28, 2017 5:26AM

    Only one of those two commits will be executed for any given run of the procedure.

    The only thing really requires committing is the insert, but because the procedure is autonomous it must finish with commit or you'll get an error.

    What you really should do is move the insert into a separate procedure, make that autonomous and remove autonomous from your existing procedure.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,551 Red Diamond
    edited Feb 28, 2017 5:39AM Answer ✓

    Why do you want it as an autonomous transaction anyway?

    It seems odd that you would be sending emails as part of a transaction, as emails cannot be rolled back if anything prevents the main transaction from completing.

    A more stable way is to have you transaction queue up emails to be sent with the appropriate details on a table, and then have a scheduled job running periodically to process the queue and send emails that are visible on that table (which they will be once the main transaction has been committed).  Then you know that the data is committed and you're in a good position for sending the emails.  (You can also look at Oracle Advanced Queuing (AQ) as another method of queuing)

    Ahmed Haroon
  • user12251389
    user12251389 Member Posts: 335 Blue Ribbon
    edited Feb 28, 2017 5:50AM

    I have created separate procedure which has insert statement that i have used in exception handling and also i am using PRAGMA AUTONOMOUS_TRANSACTION in that procedure. I have removed autonomous transaction from EXECUTE_SEND_MAIL_SMTP procedure now. But this we did only for exception handling part. In my question i was not using any exception handling still i was not able to get the mails regularly. Also i dont understand what you want to say regarding : transaction queue up emails to be sent with the appropriate details on a table ? Do you have any link for information for this ?

  • BluShadow
    BluShadow Member, Moderator Posts: 42,551 Red Diamond
    edited Feb 28, 2017 5:54AM

    What I'm saying is, you are doing some process and somewhere along that process you are deciding you need to send an email.

    If for any reason you need to roll back that process, you cannot roll back emails as they happen externally to the database (once they are sent, they are sent).

    If, as part of your process, you have a table, and for each "record" (or part of the process) for which you want to send an email, you put details instead on to a table (your email queue), then those details form part of the transaction, so if the process has to roll back for any reason, the details you've put on that table also get rolled back.

    You then have a separate scheduled process that runs periodically, picks up the details from that table, which will only include committed "records", and sends out the emails.

    This means that emails can only be sent for those things which you know are committed and prevents you sending out emails for things which get rolled back.

    user12251389
  • user12251389
    user12251389 Member Posts: 335 Blue Ribbon
    edited Feb 28, 2017 6:07AM

    Yes i understood now and i am doing in the same way. I have table called Table_Records and i have created another procedure i.e Track_Records_Table. I am  performing sceduler job for this procedure. This procedure will insert records in the Table_Records table. And now whatever the records i get from this table i am sending it via. email using the procedure which i have mentioned in my question. But i dont know for what reason the emails are sending from TEST_SERVER regularly but its skipping some emails from Verify and Production database.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 28, 2017 6:16AM

    In this case apparently the process is a job, not anything with direct user interaction. In which case rollback is probably irrelevant.

    user12251389user12251389
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 28, 2017 6:23AM

    So - your procedure should always send 3 emails every time it's run. 1 for each of the three DBs.

    And all three have the same sender/recipient but different subjects and message bodies.

    So are you saying that sometimes you just get 1 of the three emails?

    Or do you always get all three but sometimes two of them don't contain the data you expect?

    If it's the former then probably it's erroring out (or the email is getting blocked / treated as junk).

    You need to instrument the code so that you can see what emails it tries to send.

  • user12251389
    user12251389 Member Posts: 335 Blue Ribbon
    edited Feb 28, 2017 6:34AM

    Yes my procedure sends 3 emails every time its run. Even if the message does not have body then also it sends me an email and it has same sender/recipient but different subjects and message bodies. And sometimes i am getting 1 of the three emails specially from TEST_SERVER database which is my local database i am getting emails regularly. Its happeneing only with the Verify and Production database where the emails not sending regularly and i am using DB link to get the records from TRACK_RECORD table for this database. It seems the problem is not with the data for sure. How do i instrument the code ?

This discussion has been closed.