Q
Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure already running

Could you please give me advice on how I can check whether an Oracle stored procedure is already running to prevent it from running again?

Could you please give me advice on how I can check whether an Oracle stored procedure is already running to prevent it from running again? Thanks in advance.
To determine if a procedure is still running, you can use the package DBMS_APPLICATION_INFO. Then the module name or procedure name can be queried using V$SESSION. For example:
CREATE Or REPLACE PROCEDURE Add_Employee( name       VARCHAR2(20),
                                          salary     NUMBER(7,2),
                                          manager    NUMBER,
                                          title      VARCHAR2(9),
                                          commission NUMBER(7,2),
                                          dept       NUMBER(2))
AS
BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE( module_name => 
                                           'add_employee',
                                           action_name => 'insert into emp');

        INSERT INTO emp (ename,empno,sal,mgr,job,hiredate,comm,deptno)
        VALUES (name,emp_seq.nextval,salary,manager,title,sysdate,commission,
                deptno);

        DBMS_APPLICATION_INFO.SET_MODULE ('','');
END;
For more examples and detailed explanations, go to Metalink and search for "Note:61722.1."
This was last published in July 2006

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close