PL/SQL-Logik im Hintergrund starten und Fortschrittsbalken anzeigen

PL/SQL Logik in APEX zu platzieren ist etwas völlig Normales und findet nahezu überall täglich statt. Gelegentlich kommt es aber vor, dass aus APEX heraus solche PL/SQL-Logik angestoßen werden soll, die länger laufen wird. Hinterlegt man diese ganz normal als PL/SQL-Prozess im onLoad oder onSubmit-Bereich, so müsste der Anwender mit dem Browser solange warten, bis der Prozess durchgelaufen ist. Das kann für eine bis zwei Minuten noch eben akzeptabel sein (ein Hinweis ist unbedingt nötig), dauert es aber länger, so muss der PL/SQL-Prozess in den Hintergrund gebracht werden. In diesem Community-Tipp erfahren Sie, wie Sie PL/SQL-Code mit dem Paket DBMS_SCHEDULER im Hintergrund ausführen, den Status Ihres Jobs abfragen und dem Endanwender als "Fortschrittsbalken" präsentieren können.

PL/SQL Code "in den Hintergrund" schicken

PL/SQL-Code, der im Hintergrund ausgeführt werden soll, ist aus Sicht der Datenbank ein Scheduler-Job, der sofort ausgeführt wird. Als Beispiel für einen langlaufenden Job soll dieser Code dienen.

create or replace procedure do_longrun as
begin
  apex_util.set_workspace('{my workspace}');
  -- APEX 5.0 and earlier: 
  -- apex_util.set_security_group_id(apex_util.find_security_group_id('{my workspace}'));
  for i in 1..30 loop
    apex_util.pause(10);
  end loop;
end;

Würde man diese PL/SQL-Prozedur DO_LONGRUN nun in einem normalen onSubmit-Prozess aufrufen, so würde der Browser tatsächlich 300 Sekunden (5 Minuten) warten. Daher soll die Prozedur im Hintergrund aufgerufen werden. Das geschieht mit dem PL/SQL-Paket DBMS_SCHEDULER - um es sinnvoll nutzen zu können, benötigt das Parsing Schema Ihrer APEX-Anwendung das Privileg CREATE JOB. Danach können Sie Ihre Prozedur wie folgt im Hintergrund laufen lassen.

declare
  l_jobname  varchar2(255);
begin
  -- Zuerst einen eindeutigen Namen für den Job erzeugen
  l_jobname := dbms_scheduler.generate_job_name('APEXCOMMUNITY_');

  -- Dann den PL/SQL Job starten
  dbms_scheduler.create_job(
    job_name            => l_jobname,
    job_type            => 'stored_procedure',
    job_action          => 'DO_LONGRUN',
    enabled             => true
  );
end;

Wenn Sie diesen Code als PL/SQL-Prozess hinterlegen, läuft die APEX-Anwendung sofort weiter - die PL/SQL-Prozedur DO_LONGRUN läuft im Hintergrund. Setzen Sie das einmal um: Erzeugen Sie eine APEX-Anwendungsseite mit einer Region vom Typ HTML. In diese Region platzieren Sie eine Schaltfläche namens Run Job - bei Klick darauf soll die Seite weitergeleitet werden. Danach kommt der PL/SQL-Prozess, der bei Weiterleitung ausgelöst werden soll und der obigen Code mit dem Aufruf von DBMS_SCHEDULER.CREATE_JOB enthält. Nach Abschluß verzweigen Sie mit der Meldung Job gestartet auf die gleiche Seite zurück. Nach Klick auf die Schaltfläche sollte die APEX-Seite dann wie in Abbildung 1 aussehen.

Abbildung 1: PL/SQL-Code wurde im Hintergrund gestartet

Dass im Hintergrund tatsächlich Jobs laufen, können Sie im SQL Workshop oder mit SQL*Plus einfach nachvollziehen ...

SQL> select job_name, state from user_scheduler_jobs;

JOB_NAME                       STATE
------------------------------ ---------------
APEXCOMMUNITY_687              RUNNING
APEXCOMMUNITY_688              RUNNING
APEXCOMMUNITY_689              RUNNING

Allerdings stellt sich nun ein neues Problem: Wie erhält man Feedback über den Status des Jobs. In unserem Fall könnte man einfach auf die Uhr sehen, denn dieser Job läuft genau fünf Minuten - aber das ist ja nicht immer so. Der PL/SQL-Code des Jobs müsste regelmäßig einen Status abgeben - im folgenden erfahren Sie, wie das geht ...

Exkurs: Desupport des APEX_PLSQL_JOB Package

Wie beim Release von APEX 5.0 angekündigt, wurde alte PL/SQL Paket APEX_PLSQL_JOB mit APEX 5.1 entfernt, da es auf dem veralteten veralteten DBMS_JOB-Paket basiert. APEX_PLSQL_JOB wurde verwendet, um PL/SQL-Logik im Hintergrund auszuführen. Da das Paket DBMS_SCHEDULER schon seit der Oracle Datenbank 10g vorhanden ist - und eine wesentlich bessere Job-Steuerung und viele Informationen in Data Dictionary-Views anbietet, wurde APEX_PLSQL_JOB entfernt.

Wenn Sie es in Ihren Anwendungen noch nutzen, sollte möglichst bald auf DBMS_SCHEDULER umgestellt werden. Dazu ein Beispiel - angenommen, eine der APEX-Anwendungen nutzt folgenden Code, um PL/SQL-Prozeduren im Hintergrund zu starten.

DECLARE
    l_sql  VARCHAR2(4000);
    l_job  NUMBER;
BEGIN
    l_sql := 'begin plsql_to_run_as_job( :P1_ITEM ); end;';
    l_job := APEX_PLSQL_JOB.SUBMIT_PROCESS(
        p_sql    => l_sql,
        p_status => 'Background process submitted' );

    :P1_JOB_ID := l_job;
END;

Der folgende Code tut das gleiche, nutzt aber DBMS_SCHEDULER.

DECLARE
    l_sql varchar2(4000);
    l_job varchar2(4000);
BEGIN
    l_sql := 'begin plsql_to_run_as_job( ' || 
              dbms_assert.enquote_literal( :P1_ITEM ) || 
             ' ); end';
    l_job := dbms_scheduler.generate_job_name ( 
        prefix => 'MY_APEX_JOB' );

    dbms_scheduler.create_job(
        job_name =>   l_job,
        job_type =>   'PLSQL_BLOCK',
        job_action => l_sql,
        comments =>   'Background process submitted',
        enabled =>    true );
END;

Auch wenn Sie das Upgrade auf APEX 5.1 erst etwas später planen, so ist es sehr empfehlenswert, vorhandene Applikationen auf die Verwendung von APEX_PLSQL_JOB hin zu prüfen und - schon in APEX 5.0 - den Umstieg auf DBMS_SCHEDULER vorzunehmen.

Statusinformationen des Jobs abrufen

Hintergrundjobs laufen in einer eigenen Datenbanksession ab - wenn der Job also irgendwelche Werte in globale Package-Variablen schreibt, kommt man aus APEX nicht heran. Das Setzen von APEX-Elementen oder das Schreiben in eine Tabelle ist meist auch nicht angebracht, da man ein COMMIT machen müsste, damit man den Status in der APEX-Seite auch darstellen kann.

Die Oracle-Datenbank bringt allerdings von Haus aus eine Möglichkeit mit, den Status langlaufender Operationen zu verfolgen: Die Data Dictionary View V$SESSION_LONGOPS. Wenn bspw. ein sehr großer Index erzeugt wird oder ein CREATE TABLE AS SELECT abläuft, können Sie den Fortschritt der Operation in dieser View verfolgen (die View ist normalerweise für alle Datenbankuser offen). Sie ist außerdem vielen Datenbankadministratoren bekannt - was ebenfalls dafür spricht, den Status eigener langlaufender Operationen dort zu hinterlegen. Um Informationen in V$SESSION_LONGOPS zu hinterlegen, rufen Sie die Prozedur DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS auf. Die Bedienung dieser Prozedur ist allerdings etwas umständlich - um die Nutzung zu vereinfachen, bauen wir zuerst ein kleines "Wrapper-Package" ...

create or replace package pkg_session_longops is
  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2);
  procedure do_update (p_opname in varchar2, p_status in number);
end pkg_session_longops;
/
sho err

create or replace package body pkg_session_longops is
  type t_array is table of number index by varchar2(255);
  g_arr_rindex t_array;
  g_arr_slno   t_array;
  g_arr_total  t_array;

  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2) is
    l_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
    l_slno   binary_integer;
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => 0,
      totalwork    => p_target,
      target_desc  => 'no target',
      units        => p_units
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
    g_arr_total(p_opname) := p_target;
  end do_init;
      
  procedure do_update (p_opname in varchar2, p_status in number) is
    l_rindex binary_integer := g_arr_rindex(p_opname);
    l_slno   binary_integer := g_arr_slno(p_opname);
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => p_status,
      totalwork    => g_arr_total(p_opname),
      target_desc  => 'no target',
      units        => null
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
  end do_update;
end pkg_session_longops;
/
sho err

Das Paket enthält eine Prozedur DO_INIT, mit dem ein neuer Eintrag in der View V$SESSION_LONGOPS generiert wird. DO_UPDATE dient zur Aktualisierung der Zeile. Diese Operationen sind nicht transaktional - das ist keine Tabelle. Daher ist die Aktualisierung auch ohne COMMIT sofort für andere Sessions sichtbar. Bauen Sie diese Aufrufe nun in Ihre Stored Procedure, die im Hintergrund laufen soll, ein.

create or replace procedure do_longrun as
begin
  apex_util.set_workspace('{my workspace}');
  -- APEX 5.0 and earlier: 
  -- apex_util.set_security_group_id(apex_util.find_security_group_id('{my workspace}'));
  pkg_session_longops.do_init('DO_LONGRUN', 300, 'seconds');
  for i in 1..30 loop
    apex_util.pause(10);
    pkg_session_longops.do_update('DO_LONGRUN', (i * 10));
  end loop;
end;

Wenn Sie den Job nun nochmals (über die APEX-Anwendungsseite) starten, können sie den Verlauf im SQL Workshop oder in SQL*Plus bereits verfolgen ...

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        60        300 seconds

1 Zeile wurde ausgewählt.

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        70        300 seconds

1 Zeile wurde ausgewählt.

Visualisierung

Mit diesem SQL lässt sich nun ein APEX-Bericht erzeugen ...

select 
  opname,
  sofar, 
  totalwork,
  round((sofar / totalwork) * 100) as percent
from v$session_longops 
where opname = 'DO_LONGRUN'
and sofar < totalwork

... für dessen Spalte PERCENT sich der Typ Percent Graph empfiehlt (Abbildung 2).

Abbildung 2: Berichtsspalte mit Prozentwerten als Balken visualisieren

Das Ergebnis sollte dann wie in Abbildung 3 aussehen. Wenn Sie mit diesem Bericht nun den Community-Tipp Berichte automatisch aktualisieren durcharbeiten (den hinteren Teil mit dem Einrichten des APEX-Plugins "Timer"), bekommen Sie einen sich automatisch aktualisierenden Fortschrittsbalken.

Abbildung 3: Fortschrittsbalken in einer APEX-Anwendung

Fazit

In diesem Tipp haben Sie erfahren, wie Sie PL/SQL-Logik mit Hilfe von DBMS_SCHEDULER im Hintergrund ausführen können - der Nutzer muss nicht bis zum Abschluß warten. In diesen Fällen ist es aber öft nötig, Statusinformationen zu liefern; schließlich möchte der Nutzer wissen, wie weit der Job schon ist und wie lange es noch dauert.

Hierfür ist es empfehlenswert, den zunächst etwas umständlich scheinenden Weg über die Dictionary View V$SESSION_LONGOPS zu gehen, denn man schlägt zwei Fliegen mit einer Klappe: Zum einen liegt die Grundlage für die Darstellung eines Fortschrittsbalkens in der APEX-Anwendung, zum anderen nutzt man die Standard-Infrastruktur der Datenbank. Nicht nur der Nutzer der APEX-Anwendung wird informiert, sondern auch der Datenbankadministrator.

zurück zur Community-Seite