在oracle数据库中同时运行相关或相同的程序，以避免相互干扰。

I have a software for creating especial reports that run procedures in oracle database but the problem is: when we create a report it uses some procedures and truncate and fill some tables, if we create another report at the same time it caused an error because it needs my table information that are truncated with another procedure. So I am looking for a technique or query to handle this problem and create the second report just after the first report finishing all the procedures that it use in the state that user do not notice anything. In this case the time that the second report is going to be create is more than the first report that it is alright.

我有一个软件创建的报告在oracle数据库运行过程,但问题是:当我们创建一个报告它使用一些程序和截断,填一些表,如果我们创建另一个报告的同时也会产生一个错误,因为它需要我的表信息与另一个截断的过程。因此，我正在寻找一种技术或查询来处理这个问题，并在第一个报告完成后，创建第二个报告，该报告完成了它在状态中使用的所有程序，而用户没有注意到任何东西。在这种情况下，创建第二个报告的时间要比创建第一个报告的时间要长。

1 个解决方案

#1

You can use DBMS_LOCK.ALLOCATE_UNIQUE package in order to synchronize your applications and enforcing sequential processing.

您可以使用DBMS_LOCK。ALLOCATE_UNIQUE包，以便同步应用程序并执行顺序处理。

DECLARE
    l_status number;
    l_lock_handle varchar2(128);
    l_lock_request integer;
BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE ( lockname =>  'NAME_OF_YOUR_LOCK', lockhandle => l_lock_handle);
    l_status := DBMS_LOCK.REQUEST(lockhandle => l_lock_handle, timeout => 15);
    if (l_status = 0) then
      -- Plase your code here
      -- Only one thread can work here
      l_lock_request  := DBMS_LOCK.release(l_lock_handle);
    else
      -- handle other lock statuses...
    end if;
END;

You can read more about DBMS_LOCK here.

您可以在这里阅读更多关于DBMS_LOCK的信息。

#1