Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Bypass Message :This page is asking you to confirm that you want to leave?

PrakashCDec 22 2021

Hi,
I have a page where I select a record from Interactive grid. There is a button in that region which has Dynamic Action. The DA has javascript which calls a process to prepare url (apex_util.prepare_url(apex_application.g_x01)
I am doing all this so that I can dynamically go to a specific page based on some logic. The page number is evaluated based on this logic.
All this works, however, I need to get rid of the warning pop up "This page is asking you to confirm that you want to leave......".
I have already set off the "Warn on unsaved changes" flag. Other than the IG, I have search field variables, which also have the "Warn on unsaved changes" set to ignore.
Is there a way I could get rid of the warning message?
Thank you in advance.

Prakash

This post has been answered by Joseph Upshaw on Dec 23 2021
Jump to Answer

Comments

BluShadow

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

user12251389

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.

BluShadow

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

user12251389

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

Cookiemonster76

And the advice is correct.

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

user12251389

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;

Cookiemonster76

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.

Chris Hunt

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.

BluShadow

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.

user12251389

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

BluShadow

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

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

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

Marked as Answer by user12251389 · Sep 27 2020
user12251389

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

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

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

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

Cookiemonster76

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

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 ?

Cookiemonster76

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.

user12251389

Ok i will try this usecase

BluShadow

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.

1 - 23

Post Details

Added on Dec 22 2021
4 comments
1,963 views