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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close