How to Know procedure is running

[Ambatim]

Hi

How can I know whether a particular procedure is currently running or not. Thanks in advance
Mallik

 

[51m0n]

you could set a variable in a package to true when the proc starts, and back to false when the proc ends.
from where you need to know if the proc is running, just check that var. --
regards,
Simon

 

[Wonboodoo]

Simon, That will only work if your checking from within the same session that is running the procedure. Which I think it is impossible.

You could set a value in a one-row one-column table.

 

[51m0n]

Wonboodoo, you are right.
though, what you suggest would require a commit.

without commit and not beeing in the same session, this would probably require access to the sys views. then you could use v$session, v$sqltext, v$sqlarea or another view like that. --
regards,
Simon

 

[R1972]

you can use 'pragma autonomous transaction' for commiting a particular transaction

Regards
Raj

 

[sem]

The more preffered way for long-running procedures is to use DBMS_APPLICATION_INFO package. Regards, Dima

 

[jag14612]

In testing you could use DBMS_OUTPUT to send data/comments to the screen. Aulthough this is not always the best solution, it helps with debugging.

 

[carp]

dbms_output will only send messages to the screen when the routine finishes running. So this would only tell you that the routine completed - not that it's actually running.

 

[BJCooperIT]

You could UTL_FILE to create a flat file that indicates the procedure ran. It could include sysdate and variable values. (select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[Advocate]

i have a log table to which i add (and commit) an entry each time a package/procedure/function starts/ends etc ... could be modified to add entry at other points in package if commit will not cause problems - need commit to read entries in log file before provedure/package ends ...
Best of Irish Luck, David.
djwilkes@hotmail.com

 

[carp]

As Raj already indicated - you could set up an autonomous transaction so the commit after inserting into your log table would not have any effect on your main program.

 

[sem]

Again, isn't it much easier to use DBMS_APPLICATION_INFO.SET_MODULE and to view runtime info via v$session? Regards, Dima

 

[Ambatim]

Hi Guys,

Thank you for all your suggestions.

But it is not my procedure to change so that I can know it is running.

There is a procedure X. I do not know what it does. I just want to know whether it is currently running or not.

How can I konw that.

I appreciate your help. Thanks in advance
Mallik