如何在Oracle APEX中使用Oracle DBMS_ALERT? [英] How to use Oracle DBMS_ALERT within Oracle APEX?

问题描述

使用Oracle APEX 4.2，我有一个页面进程，该进程使用DBMS_ALERT来运行一个正在运行的进程，该过程可能需要一分钟才能完成或失败.

Using Oracle APEX 4.2, I have a page process that uses DBMS_ALERT for a running process that may take up to a minute to complete or fail.

在运行时，通过使用触发器，有一个DBMS_ALERT.signal发布进度通知.

While this is running, through the use of a trigger, there is a DBMS_ALERT.signal posting notifications of progress.

我的问题是，在Oracle APEX 4.2中，如何在页面上的区域或模式中向用户显示来自DBMS_ALERT的这些通知的运行进度?

My question is, within Oracle APEX 4.2, how can I display to the user, a running progress of these notifications from DBMS_ALERT, within a region or modal on the page?

我需要在作业运行时向用户提供所有这些警报，直到最终完成.

I need to provide the user all these alerts as the job is running until it finally completes.

例如:

Started Job...
Waiting on resources...
Processing job...
Job completed successfully.

显然，通过每个DBMS_ALERTS，可能需要几秒钟的时间才能将每一行呈现给用户.

Obviously through each of these DBMS_ALERTS, there may be a few seconds before each line is presented to the user.

我不确定如何在Oracle APEX中捕获这些警报并向用户显示.

I am unsure how to grab these alerts and show the user, within Oracle APEX.

推荐答案

我将在apex.oracle.com上建立一个演示，但是由于您需要在dbms_alert上执行执行，因此它必须是纯文本的

I'd set up a demo on apex.oracle.com, but since you need an execute grant on dbms_alert, it'll have to be textual-only.

您可以在整个设置上走得很远，所以我认为这是可以建立的基础. 例如，我只使用一个警报.在您的示例中，您可能希望使用多个事件来捕获不同的进度警报.这是出于简单的原因，为了将某些内容返回给客户端(ajax响应)，ajax回调必须关闭".因此，在捕获警报并想要返回警报时，您需要写入缓冲区并需要将其返回.这意味着您还将停止收听事件(阅读:在顶点，您应该！).

You can go quite far with the entire set-up, so I'd consider this as basics to build on. For example, I have only worked with one alert. In your sample you may want to use multiple events to catch the different progress alerts. This is for the simple reason that in order to return something to the client (the ajax response) the ajax callback has to 'closed'. So when catching an alert and wanting to return that, you need to write to the buffer and it needs to be returned. This means that you will also stop listening to the event (read: in apex, you should!).

考虑这样的流程:您将进行ajax调用，并有一个ajax回调过程来注册对事件的兴趣.然后，您等待警报发生.您可以捕获它并通过将其写入http缓冲区(htp.p)来返回它.代码到此结束，顶点将刷新缓冲区，然后ajax调用将获取响应，您将能够管理该返回. 不过，请不要忘记:apex使用连接池，并且数据库会话不会直接链接，而是会一直重复使用.您不想离开"数据库会话.您还必须注销您的警报兴趣.这也为使用唯一的警报ID提供了理由-警报可以在不同的(数据库)会话中注册，因此，如果这是一个页面，多个用户可以使用该页面来跟踪其流程的进度，那么希望他们干扰其他用户的警报.

Consider the flow like this: you will make an ajax call, and have an ajax callback process which registers interest in an event. Then you wait for an alert to occur. You catch it and return it by writing it in the http buffer (htp.p). That's the end of the code and apex will flush the buffer, the ajax call will then pick up the response and you'll be able to manage that return.
Don't forget though: apex uses connection pooling, and database sessions are not linked directly but rather reused all the time. You don't want to 'leave' a database session 'dirtied'. You'll have to unregister your alert-interest too. This also makes a case for using unique IDs for alerts - alerts can be registered to in different (database) sessions, so if this would be a page which multiple users can use to follow the progress of their process' progress, you don't want them to interfere with other users' alerts.

但是，这种短暂的兴趣性质也意味着在进行不同的ajax调用之间会出现中断".当您想收听多个警报时，这些警报可能非常紧密地包装在一起，因此您可能会错过一个警报.假设2个警报间隔1毫秒:第一个警报将被捕获并报告给ajax调用，后者必须立即开始一个新的呼叫以侦听更多警报.但是，由于在短时间内没有活动的侦听器，因此可能错过了下一个警报.现在-这可能只是在同一处理程序下触发多个警报的问题.如果您要使用多个处理程序并同时对所有这些处理程序启动ajax调用，那么它们将全部得到及时处理.当然，两者都有解决方案.我想像一下，仅使用一个处理程序时，您就可以捕获集合中的所有警报，并检查是否已经针对某个警报发送了响应，以及是否继续签入.使用多个处理程序，您可以使用唯一的ID，并在其后缀不同的状态.

However, this fleeting nature of interest also means there will be "interruptions" between different ajax calls made. When you want to listen for multiple alerts, and these alerts may be packed very very closely together, the chance exists you may miss one. Say 2 alerts are spaced 1ms apart: the first will be caught, reported to the ajax call, which would have to start a new call right away in order to listen for more alerts. But since there was no active listener during that short time, that next alert may have been missed. Now - this is likely only to be an issue where you fire off multiple alerts under the same handler. If you'd use multiple handlers and start ajax calls for all those at the same time, they'll all be handled in time. There are solutions for both, of course. I imagine that when using one handler only you could catch all alerts in a collection and check against that if you've already sent a response for a certain alert or not and whether to continue checking in or not. With multiple handlers you could use a unique id and suffix it with different statusses.

这是我在本地POC中使用的一些实际代码.

So here is some actual code which I've used in my local POC.

概述:我有3个按钮:1个生成警报ID，为此我使用了一个序列.另一个按钮开始监听事件，另一个按钮发送警报.

Overview: I have 3 buttons: 1 to generate an alert id, for which I used a sequence. Another button to start listening for an event, and yet another button to send an alert.

NEW_ALERT_ID按钮的JS代码:

JS code for NEW_ALERT_ID button:

apex.server.process("NEW_ALERT").done(function(pdata){
$s("P1_ALERT_ID",pdata.alertId);
})

START_LISTEN按钮的JS代码:

JS code for START_LISTEN button:

apex.server.process("LISTEN_ALERT",{x01:$v("P1_ALERT_ID")},{timeout:(31*1000)})
.done(function(pdata){
  if (pdata.success ){
      alert('Caught alert: ' + pdata.message);
  } else {
      alert("No alerts caught during wait on database. You may want to continue listening in...")
  }
})
.fail(function(jqXHR, textStatus){
    if(textStatus === 'timeout')
    {     
        alert('Call should have returned by now...'); 
        //do something. Try again perhaps?
    }
});

SEND_ALERT按钮的JS代码:

JS code for SEND_ALERT button:

apex.server.process("SEND_ALERT",{x01:$v("P1_ALERT_ID")},{dataType:"text"});

AJAX回调过程:

NEW_ALERT:

NEW_ALERT:

htp.p('{"alertId":'||alert_seq.nextval()||'}');

LISTEN_ALERT:

LISTEN_ALERT:

declare
  alert_id number := apex_application.g_x01;
  msg varchar2(2000);
  stat pls_integer;
  keep_looping boolean := true;
  insurance binary_integer := 0; -- prevent an infinite loop

  onecycle binary_integer := 3; -- one cycle of waiting, in seconds
  maxcycles binary_integer := 10; -- in this session, the max amount of cycles to wait
begin
  dbms_alert.register(alert_id);

  while keep_looping
  loop
    insurance := insurance + 1;

    dbms_alert.waitone(alert_id, msg, stat, onecycle);
    if stat = 1 then
      apex_debug.message('timeout occured, going again');
    else
      apex_debug.message('alert: '||msg);
      keep_looping := false;
    end if;

    exit when insurance = maxcycles;    
  end loop;


  if keep_looping then
    -- we waited a really long time now. It may be a good idea to return this info to the client and let it start a new call
    htp.p('{"success":false,"message":"No alert during wait on database"}');
  else
    htp.p('{"success":true,"message":"'||msg||'"}');
  end if;
end;

SEND_ALERT:

SEND_ALERT:

declare
  alert_id number := apex_application.g_x01;
begin
  dbms_alert.signal(alert_id, 'alert sent at '||to_char(systimestamp, 'HH24:MI:SS FF6'));
end;

因此，我先获得一个警报ID，然后开始监听，然后在某个时候(或不发送)警报.不过，这是一个骨架，在您的实际设置中需要进一步完善.

So, I'd first get an alert ID, then I'd start to listen, and then at some point I'd send an alert (or not). It's a skeleton though and will need further refinement in your actual setup.

这篇关于如何在Oracle APEX中使用Oracle DBMS_ALERT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！