Home » SQL & PL/SQL » SQL & PL/SQL » Check if current procedure/function is running (Oracle 10g)
Check if current procedure/function is running [message #435390] Wed, 16 December 2009 01:22 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi Experts,

Is there a way or a view where i can check if a current procedure or function is currently running?

I have a requirement that calls a procedure every 3 secs, but if the procedure isn't finished yet, it must not execute another instance of that procedure, wait another 3 secs and check the availability of the procedure again.

I did some research and found something about Tom's whoami and who_called_me procedure, but it seems that the links are not working anymore. Can somebody please provide an updated and working link of those links?

Thanks for your time.

Ever grateful,
Wilbert
Re: Check if current procedure/function is running [message #435393 is a reply to message #435390] Wed, 16 December 2009 01:47 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look at dbms_application_info
Re: Check if current procedure/function is running [message #435395 is a reply to message #435390] Wed, 16 December 2009 01:58 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
first read this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1529405950004#1994227600346041824

Then this
http://laurentschneider.com/wordpress/2007/07/vsession_longops-in-10gr2.html

And this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1529405950004#167569600346800985

sriram Smile
Re: Check if current procedure/function is running [message #435419 is a reply to message #435390] Wed, 16 December 2009 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd probably use the DBMS_LOCK package.
Have the called process establish a named lock in exclusive mode, and release it when it has finished processing.
The calling process can check whether the process has finished by attempting to get the same lock.
Re: Check if current procedure/function is running [message #435583 is a reply to message #435390] Wed, 16 December 2009 23:47 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Thanks for the comments tahpush, ramoradba and JRowbottom.

Anyway I tried the DBMS_LOCK approach, it puzzles me that it works this way:

DECLARE 

v_lock      NUMBER;
v_lockhandle varchar2(200);

-- extra declarations here --

BEGIN

-- LOCK --
 dbms_lock.allocate_unique('lock_proc1', v_lockhandle);
 v_lock := dbms_lock.request(v_lockhandle,dbms_lock.x_mode);

/*** Execute the rest of the 
procedure
here
***/

Commit;

-- RELEASE LOCK -- 
 v_lock := dbms_lock.release(v_lockhandle);

END;



But not with this way..?

CREATE PROCEDURE MY_PROC1 IS
v_lock      NUMBER;
v_lockhandle varchar2(200);

-- extra declarations here --

BEGIN

-- LOCK --
 dbms_lock.allocate_unique('lock_plp', v_lockhandle);
 v_lock := dbms_lock.request(v_lockhandle,dbms_lock.x_mode);

/*** Execute the rest of the 
procedure
here
***/

-- RELEASE LOCK -- 
 v_lock := dbms_lock.release(v_lockhandle);

END;

PROCEDURE MY_SCHEMA.MY_PROC1
On line:  102
PLS-00201: identifier 'DBMS_LOCK' must be declared



SELECT * FROM USER_SYS_PRIVS;

USERNAME	PRIVILEGE	ADMIN_OPTION

MY_SCHEMA	EXECUTE ANY PROCEDURE	NO
MY_SCHEMA	CREATE ANY INDEX	NO
MY_SCHEMA	UNLIMITED TABLESPACE	NO
MY_SCHEMA	SELECT ANY TABLE	NO
MY_SCHEMA	CREATE TABLE	        NO



Regards,
Wilbert

[Updated on: Wed, 16 December 2009 23:50]

Report message to a moderator

Re: Check if current procedure/function is running [message #435586 is a reply to message #435583] Thu, 17 December 2009 00:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

Regards
Michel
Previous Topic: Formatting excel generated from PL/SQL
Next Topic: Is this statement repeating itself (code review) or can the last 2 be combined
Goto Forum:
  


Current Time: Sun Dec 04 12:39:08 CST 2016

Total time taken to generate the page: 0.16070 seconds