APEX

An autonomous procedure should have a commit at the end of it, especially if you issue DML statements.

Your WHEN OTHERS EXCEPTION inserts to your exception table, but does not commit (and why you are using an EXECUTE IMMEDIATE statement for it I really don't know - what's wrong with a regular INSERT statement?)

Ok i will try to commit at the end and check for next few hours and i am using EXECUTE IMMEDIATE statement because in exception handling if i use normal INSERT statement then i am getting error as sql statement not in scope.

?  Of course you can use SQL statements in an exception block; there's no such thing as an SQL statement not being in scope.  I suspect the error was because you were not including a commit, and it was an autonomous transaction.  Try converting it back to a regular INSERT and include a commit after it to see.

In the exception handling i tried to give normal INSERT statement and then try to commit inside and also tried outside exception:

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

And i am getting error for the line where i have used INSERT statement as:

 PL/SQL: ORA-00984: column not allowed here 

I checked the below forum where there are saying

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm

And the advice is correct.

You also need to move the commit above the raise or it'll never get executed.

using normal INSERT statement i am getting the same error when i move the commit above the raise. Do i need to provide to 2 commit in my procedure, one for the other insert statements and one inside exception like below ?

EXCEPTION WHEN OTHERS THEN
  EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';
  commit;
  RAISE;
Commit;

The word 'also' means you have to do want I said in addition to fixing the insert.

I don't know what error_code and err_msg are. You want to use sqlerrm. And you'll need to assign it to a variable and use the variable in the insert.

I think your problem is in your exception handling.

If send_mail_smtp fails due to a transient or permanent error, it tries to quit the connection and only attempts to write to your log table if the quit attempt fails for a non transient/permanent error. Then it calls raise_application error. Any other type of error goes unhandled.

If execute_send_mail_smtp fails, or it picks up an unhandled exception from send_mail_smtp, you attempt to write a row to the log table. But there's no commit to save that row so you can see it later, and in any case I think the attempt will fail because your EXECUTE IMMEDIATE code includes PL/SQL variables which are out of scope.

I'd try running it without any exception handlers, and then look in the ALL_SCHEDULER_JOB_RUN_DETAILS to see the errors.

If you want to record error information in your own table, create a procedure that does only that:

CREATE OR REPLACE PROCEDURE log_smtp_exception (p_error IN VARCHAR2, p_details IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO send_mail_smtp_exception VALUES (p_error, p_details, sysdate);
   COMMIT;
END;

Then you can call it like this in your exceptions (and your calling procedures do not need to be autonomous transactions or include extra commits):

EXCEPTION WHEN OTHERS THEN
   log_smtp_exception ('MAIL FAILED ERROR',error_code||' '||err_msg);

What your actual problem is will be easier to work out once you're seeing the error messages.

Assign err_msg (you don't need error_code as it's in the message already) to a variable and then use that variable in your INSERT statement.  You shouldn't use err_msg directly in DML.

Yes i did this. I have assigned the err_code and err_msg to a variable:

err_code :=SUBSTR(SQLCODE, 1, 100);
err_msg := SUBSTR(SQLERRM, 1, 200);

And now my exception handling looks like below:

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

But when i execute the procedure now i am getting error as :

ORA-06519: active autonomous transaction detected and rolled back 

this error is coming at the line where i have used commit

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?)

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.

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.

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 ?

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.

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.

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

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.

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 ?

In much the same way you're logging errors. Have an autonomous procedure that inserts into a table. Call it at various places to see what's going on.

I'd start with: before each call to send_mail_smtp, in send_mail_smtp just before the exception handler.

Ok i will try this usecase

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

Not necessarily, it depends if the emailing is just part of a larger process that 'does stuff' which could be transactional.