Jump to content
jjeffman

Send messages to the user while running plsql blocks

Recommended Posts

Hello,

I wonder if is there any way of sending  to the process requester a message or data while a plsql block is running.


I have a process which is responsible for deleting a very large amount of data and it lasts more time than a nowadays user can wait for, so I would like to show them that the process has already deleted x tousands records until now and is still working on it.

Is that possible? Does tns implements any kind of it ?

Thank you very much.

Best regards.

Jayme Jeffman

Share this post


Link to post
Share on other sites

Hello Jeff,

Im afraid Oracle can't do that. When a block is running, we must wait for it to get completed until our next process.

Why don't you create an email facility using UTL_MAIL or UTL_SMTP to check the count of the table for ever few minutes (using DBMS_SCHEDULERS) and send it to the concerned team/people? 

Also if its a single DELETE, this isn't possible. You can have AFTER DELETE STATEMENT level trigger to send an email stating that the process is over.

 

Thanks,

Boobal Ganesan

Share this post


Link to post
Share on other sites

Hello Boobal Ganesan ,

Thank you very much for answering me.

No, it is not a single delete!

As I might have around 10 million records to delete, and this may cause a temporary tablespace error, I am committing the transaction every 100 thousands deletions.

At this point, the commit action,  I would like to expose to the user that "until now x thousands records were deleted".

I  have already thought on make a function which could return a table of Varchar2, but again, it seems that the table of records is only available after the process is finished.

So, I must to accept that I will not be able to let the user know that the process is ok and is still running fine.

The only way I have is to raise up the process level and letting the interface manage the loop of records to delete, showing a message on the commit action, but the performance of this aproach has less performance than the PL/SQL code.

Best regards.

Jayme Jeffman

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×