Wer schon mal längerlaufende PL/SQL Prozeduren gebaut hat, kennt das Problem: Wie kann man etwas
über den aktuellen Status seiner Prozedur erfahren? Ist sie schon fast fertig - oder immer noch
am Anfang und es geht nicht voran?
DBMS_OUTPUT hilft nicht wirklich weiter: Denn DBMS_OUTPUT ist ein Puffer, der gefüllt wird, während
die Prozedur läuft - aber erst nach Abschluß derselben liest SQL*Plus diesen mit
den entsprechenden READ_LINE Funktionan aus und stellt die Inhalte auf dem Bildschirm dar.
Und die Inhalte sind nur in der gleichen Datenbanksession sichtbar - mit einer anderen
Sitzung kommt man an die Werte nicht heran.
Man könnte nun noch darüber nachdenken, mit autonomen Transaktionen in Tabellen oder
mit UTL_FILE in eine Datei zu schreiben. Aber eigentlich gibt es ein "Standardwerkzeug" hierfür:
die Dictionary View V$SESSION_LONGOPS. In diese schreibt die Oracle-Datenbank ohnehin schon die
Status langlaufender SQL-Operationen (bspw. bei einem CREATE TABLE AS SELECT oder bei einem CREATE INDEX).
Im PL/SQL Paket DBMS_APPLICATION_INFO befindet sich die Prozedur SET_SESSION_LONGOPS, mit der man
auch selbst einen Eintrag in V$SESSION_LONGOPS vornehmen kann. Allerdings ist die
Schnittstelle "etwas" umständlich. Es gibt
recht viele Parameter, von denen man einige nicht braucht und andere kompliziert "von einem Aufruf
zum nächsten" gereicht werden müssen. Instrumentiert man seinen Code damit, so wird das alles sehr
schnell ... sehr unübersichtlich - und das ist wohl einer der Gründe, warum V$SESSION_LONGOPS von
PL/SQL-Entwicklern kaum genutzt wird.
Ich schlage daher ein Wrapper-Package für DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS vor. Ziel ist es,
einfach nutzbare Funktionsaufrufe bereitzustellen. So könnte es aussehen.
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 Ergebnis ist ein PL/SQL Package PKG_SESSION_LONGOPS:
PROCEDURE DO_INIT Argument Name Typ In/Out Defaultwert? ------------------------------ ----------------------- ------ -------- P_OPNAME VARCHAR2 IN P_TARGET NUMBER IN P_UNITS VARCHAR2 IN PROCEDURE DO_UPDATE Argument Name Typ In/Out Defaultwert? ------------------------------ ----------------------- ------ -------- P_OPNAME VARCHAR2 IN P_STATUS NUMBER IN
Mit DO_INIT initialisieren wir eine Zeile in V$SESSION_LONGOPS, mit DO_UPDATE wird sie
mit einem neuen Status aktualisiert. Eingebaut in eine langlaufende PL/SQL Prozedur, könnte
es so aussehen.
create or replace procedure do_longrun as
begin
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;
Das sieht doch schon sehr einfach aus. Startet man die Prozedur, so kann man den Status während
der Laufzeit (auch von einer anderen Datenbanksession aus) bequem 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.
Eigentlich ganz einfach, oder ...? Und das beste ist, dass man sich in der "Standard-Infrastruktur"
bewegt; man stellt seine Informationen also über die Dictionary-View bereit, in die der Datenbankadministrator
ohnehin hineinsieht, wenn er sich über langlaufende Operationen informieren möchte. In dem Wrapper-Package
habe ich mich bemüht, die Aufrufe möglichst einfach zu gestalten; einige der Parameter von
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS wie bspw. die TARGET_ID, habe ich gar nicht verwendet - wer
möchte, kann sich das Paket ja entsprechend erweitern ...
