CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
user2788258
user2788258

Reputation: 13

Oracle 10g procedure executing no parallel

I have a procedure which should execute one at a time. I mean when the procedure is executing other process/user should not be able to execute the same procedure.

Only after the procedure executes and the the next time it can be executed

Please help

Upvotes: 0

Views: 744

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27261

To serialize execution of a procedure you could use dbms_lock package and specifically allocate_unique(), release() procedures and request() function of that package. Here is an example, in which we create a package which will regulate lock acquiring process:

Specification of the package

create or replace package proc_lock as
  function request_lock( p_lock_name   in varchar2) return varchar2;
  procedure release_lock(p_l_handle in varchar2);
end;

Package body

create or replace package body proc_lock as
  function request_lock( p_lock_name   in varchar2) return varchar2
  is
    l_request     number;
    l_lock_handle varchar2(1000);
  begin
    dbms_lock.allocate_unique(p_lock_name, l_lock_handle);
    -- waits 5 sec trying to acquire a lock
    l_request := dbms_lock.request(l_lock_handle, timeout=>5);
    if l_request <> 0
    then
      raise_application_error(-20001, 'Lock cannot be acquired.');
    end if;
    return l_lock_handle;
  end;

  procedure release_lock(p_l_handle in varchar2)
  is
  begin
    if dbms_lock.release(p_l_handle) > 0
    then
      raise_application_error(-20000, 'Cannot release lock');
    end if;
  end;
end;

Here is a simple procedure, which should be serialized

create or replace procedure test_proc
is
  l_handle       varchar2(1000);   -- lock handle
begin
  -- trying to acquire a lock
  l_handle := proc_lock.request_lock('test');
  -- here goes all usefull work that this procedure 
  -- would do if lock acquired successfully 
  dbms_output.put_line('procedure is working');
  -- mimic large amount of work
  dbms_lock.sleep(10);
  dbms_output.put_line('procedure is done');
  -- release the lock after work is done
  proc_lock.release_lock(l_handle);
end;

Test case:

--Session #1
SQL> exec test_proc;

--Session #2 at the same time
SQL> exec test_proc;

BEGIN test_proc; END;

*
ERROR at line 1:
ORA-20001: Lock cannot be acquired. 
ORA-06512: at "HR.PROC_LOCK", line 11 
ORA-06512: at "HR.TEST_PROC", line 6 
ORA-06512: at line 1 

--Session #1
SQL> exec test_proc;
procedure is working                                                            
procedure is done                                                               

PL/SQL procedure successfully completed.

Find out more about dbms_lock package.

Upvotes: 2

Related Questions