Tricks mit Berichten und dem APEX-Repository

Das APEX ein metadatengetriebenes Werkzeug ist, ist den meisten Entwicklern hinreichend bekannt: Alle Details einer APEX-Anwendung werden im APEX-Repository in Tabellen abgespeichert - zur Laufzeit werden die Anwendungsseiten anhand dieser Metadaten erstellt.

Wie die Datenbank selbst, stellt auch APEX eine große Anzahl Dictionary Views bereit - aus diesen lassen sich alle Informationen zu einer Anwendung - mit SQL - auslesen und verarbeiten. So listet die View APEX_APPLICATIONS alle Anwendungen eines Workspace auf. APEX_APPLICATION_PAGES enthält folgerichtig alle Seiten aller Anwendungen. Eine Übersicht über die vorhandenen Dictionary Views findet sich im Application Builder in den Workspace Utilities und dort unter Application Express Views.

Application Express Dictionary Views

APEX Dictionary Views sind eine sehr gute Grundlage für die Qualitätssicherung - so lassen sich potenzielle Probleme in einer Anwendung durch geschickte SQL-Abfragen schnell herausfinden.

• Welche APEX-Elemente haben noch keinen Hilfetext?
• Welche Anwendungsseiten sind öffentlich?
• Welche Plug-Ins werden in den Anwendungen verwendet?
• Wo besteht potenzielle SQL-Injection Gefahr?
• ...

Der APEX-Advisor, den Sie im Application Builder unter den Utilities finden, basiert auf dem APEX-Repository und bietet fertige Qualitätsprüfungen für Ihre Anwendungen an. Letztlich sind auch diese Checks mehr oder weniger komplexe SQL-Abfragen, die auf den Views des APEX-Dictionary arbeiten. Es ist gute Praxis, den Advisor für alle Anwendungen vor dem Roll-Out einmal laufen zu lassen.

Abbildung 1: APEX Advisor: Qualitätssicherung für APEX-Anwendungen

Auch die Entwicklergemeinde hat sich mit dem Thema Qualitätssicherung mit dem APEX-Repository auseinandergesetzt. Von der Community-Seite apex.world kann das Quality Assurance Plug-In von Oliver Lemm heruntergeladen werden.

APEX Quality Assurance Plugin von Oliver Lemm

In diesem Tipp soll es aber nicht um Qualitätssicherung oder Reporting gehen; vielmehr wird beispielhaft gezeigt, wie das APEX-Dictionary in einer APEX-Anwendung selbst genutzt werden kann. Mit dem APEX-Dictionary lassen sich APEX-Komponenten (wie Berichte oder Diagramme) erstellen, die letztlich auf anderen APEX-Komponenten basieren. Dies sei anhand klassischer Berichte gezeigt. Erstellen Sie also einen klassischen Bericht für die Tabelle EMP (Abbildung 2).

Abbildung 2: Klassischer Bericht für die Tabelle EMP

Metadaten abrufen und in der Anwendung darstellen

Erstellen Sie nun einen neuen Bericht - und verwenden Sie die folgende SQL Abfrage. Achten Sie darauf, dass der in der Abfrage referenzierte Region Name EMP zum Namen Ihrer Berichtsregion passt.

select 
  column_alias,
  heading,
  display_sequence
from apex_application_page_rpt_cols
where page_id = :APP_PAGE_ID 
  and application_id = :APP_ID
  and region_name = 'EMP'

Das Ergebnis sollte dann wie in Abbildung 3 aussehen.

Abbildung 3: Ein Bericht zeigt die Ergebnisspalten eines anderen Berichts

Auch die Berichtsabfrage selbst können Sie abrufen. Das geht wie folgt:

select
  to_char(region_source) as region_source
from apex_application_page_regions
where page_id = :APP_PAGE_ID 
  and application_id = :APP_ID
  and region_name = 'EMP'

Abbildung 4 zeigt alle drei Berichte auf einmal.

Abbildung 4: Berichte zeigen die Konfiguration eines anderen Berichts

Klassische Berichte werden interaktiv: mit dem APEX-Repository

Mit diesen Grundlagen können Sie einem klassischen Bericht etwas "Leben" einhauchen: Wie bei einem interaktiven Bericht, sollen die Spalten auch für einen klassischen Bericht ein- und ausschaltbar sein. Und das soll nicht "manuell" für jeden Bericht einzeln, sondern möglichst generisch implementiert werden. Es ist natürlich völlig klar, dass sowas mit einem interaktiven Bericht viel einfacher und schneller umgesetzt werden kann. In diesem Tipp geht es allerdings nicht um das Umsetzen einer Anforderung, sondern um ein Verständnis für die Arbeit mit dem APEX-Repository.

Löschen die die beiden zuletzt erzeugten Berichtsregionen, behalten Sie aber den ursprünglichen Bericht und achten Sie auf dessen Titel EMP. Es ist eine gute Idee, dem Bericht einen anderen Titel zu geben, der auf jeden Fall eindeutig bleibt (EMP_ORIGINAL_HIDDEN). Am Ende wird dieser Bericht ohnehin nicht sichtbar sein, so dass sie den Titel frei wählen können. Natürlich können Sie, aus der View APEX_APPLICATION_PAGE_REGIONS, auch die eindeutige REGION_ID ermitteln und diese in die SQL-Abfragen und den PL/SQL Code einsetzen.

Erzeugen Sie dann eine neue Region Static Content und erzeugen Sie darin mit der Abfrage für die Berichtsspalten ein Shuttle-Item (:P3_COLUMNS). Nehmen Sie als LOV-Query die SQL-Abfrage, mit der Sie die Berichtsspalten abgerufen haben, passen Sie aber den Namen der Region ggfs. nochmals an.

select 
  heading d, 
  column_alias r
from apex_application_page_rpt_cols
where page_id = :APP_PAGE_ID 
  and application_id = :APP_ID
  and region_name = 'EMP_ORIGINAL_HIDDEN'
order by display_sequence

Nun haben Sie ein Element auf Ihrer Seite, mit dem der Endanwender Spalten an- oder abwählen kann. Es werden immer die Spalten des Ursprungsbericht angezeigt (Abbildung 5).

Abbildung 5: Berichtsspalten in einem Shuttle Item

Nun erzeugen Sie den eigentlichen Bericht, den die Endanwender sehen sollen. Nehmen Sie nun einen Bericht, basierend auf einer PL/SQL Funktion. Hinterlegen Sie den folgenden PL/SQL-Code als Berichtsquelle - achten Sie aber auf den Namen der Region mit ursprünglichen Bericht (EMP_ORIGINAL_HIDDEN) und passen Sie diesen ggfs. an.

declare
  l_sql    varchar2(32767);
  l_query  varchar2(32767);
  l_cols   apex_application_global.vc_arr2;
begin
  l_cols := apex_util.string_to_table(:P3_COLUMNS);
  l_sql := 'select ';
  
  select to_char(region_source) into l_query
   from apex_application_page_regions
  where page_id = :APP_PAGE_ID 
    and application_id = :APP_ID
    and region_name = 'EMP_ORIGINAL_HIDDEN';
  
  if l_cols.count = 0 then 
    l_sql := l_sql || '*';
  else 
    for i in 1..l_cols.count loop
       l_sql := l_sql || dbms_assert.enquote_name(l_cols(i));
       if i < l_cols.count then l_sql := l_sql ||', '; end if;
    end loop;
  end if;
  
  l_sql := l_sql ||' from (' || l_query || ')';

  apex_debug.info('SQL Query is %s', l_sql);
  return l_sql;
end;

Hinterlegen Sie das Shuttle-Element P3_COLUMNS danach bei Page Items to Submit, stellen Sie Generic Columns auf Yes und tragen Sie bei Generic Column Count eine Anzahl an, die auf jeden Fall ausreicht (bspw. 60).

Abbildung 6: Klassischen Bericht basierend auf PL/SQL Function erzeugen

Als letztes erzeugen Sie eine Dynamic Action, welche die neue Berichtsregion bei einer Änderung am Shuttle-Item P3_COLUMNS aktualisiert (Refresh). Danach können Sie die Seite starten. Wenn im Shuttle-Item keine Spalten ausgewählt sind, zeigt der Bericht alle an. Ansonsten werden nur die Spalten gezeigt, die im Shuttle tatsächlich ausgewählt sind.

Abbildung 7: Der Endanwender kann die Spalten eines klassischen Berichts auswählen

Nun haben Sie aber zwei Berichte auf Ihrer Seite. Das ist natürlich kein Problem; schalten Sie die ursprüngliche Berichtsregion mit dem Titel EMP_ORIGINAL_HIDDEN einfach per Bedingung unsichtbar (Never). Damit Sie nicht durcheinanderkommen, empfiehlt es sich, die Region als Subregion zum angezeigten, dynamischen Bericht einzuordnen. Der Ursprungsbericht ist für diese Anwendung nichts weiter als eine Ablage für die SQL-Abfrage und die Spaltendefinitionen.

Nicht so schön sehen die Spaltenüberschriften aus - aber auch das kann man ändern. Navigieren Sie zu den Attributen des dargestellten Berichts, dort zum Abschnitt Headings und stellen Sie PL/SQL Function Body als Quelle für die Spaltenüberschriften ein.

Abbildung 8: Einstellungen zu den Spaltenüberschriften

Hinterlegen Sie dann folgenden Code.

declare
  l_heading  varchar2(4000);
  l_columns  apex_application_global.vc_arr2;
  l_headings apex_application_global.vc_arr2;
begin
  l_columns := apex_util.string_to_table(:P3_COLUMNS);
  if l_columns.count > 0 then 
    for i in l_columns.first..l_columns.last loop
      begin
        select heading into l_heading
         from apex_application_page_rpt_cols
        where page_id = :APP_PAGE_ID 
          and application_id = :APP_ID
          and region_name = 'EMP_ORIGINAL_HIDDEN'
          and column_alias = l_columns(i);
      exception when NO_DATA_FOUND then 
        l_heading := l_columns(i);
      end;
      l_headings(l_headings.count + 1) := l_heading;
    end loop;
  else
    select heading bulk collect into l_headings
      from apex_application_page_rpt_cols
     where page_id = :APP_PAGE_ID 
       and application_id = :APP_ID
      and region_name = 'EMP_ORIGINAL_HIDDEN'
      order by display_sequence;
  end if;
  apex_debug.info('SQL Query is %s', apex_util.table_to_string(l_headings));
  return apex_util.table_to_string(l_headings);
end;

Nun werden die "richtigen" Spaltenüberschriften verwendet. Sie können diese ändern, indem Sie zum "Originalbericht" EMP_ORIGINAL_HIDDEN navigieren und dort entsprechend anpassen.

Abbildung 9: Dynamischer Bericht - nun mit richtigen Spaltenüberschriften

Sie haben nun einen klassischen Bericht - mit Spaltenauswahl für den Endanwender - erstellt. Das Beste ist, dass der Setup auf Änderungen am Originalbericht reagiert - probieren Sie das aus: Stellen Sie zunächst sicher, dass im Shuttle-Element P3_COLUMNS keine Spalten selektiert sind und navigieren Sie zum ursprünglichen Bericht EMP_ORIGINAL_HIDDEN. Tauschen Sie die SQL-Abfrage aus - bspw. in select * from user_tab_columns. Wichtig ist nur, dass die Abfrage nicht mehr als 60 Spalten zurückliefert.

Wenn Sie die Seite dann neu starten, bemerken Sie, dass alles - aus dem Stand - auch mit der neuen Abfrage funktioniert. Grund ist, dass Sie mit dem APEX-Repository arbeiten.

Abbildung 10: Dynamischer Bericht - nun basierend auf einer anderen SQL-Query

Mit diesem Bericht können Sie nun weitermachen. Bis hierhin werden Formatmasken, links- oder rechtsbündige Darstellung der Berichtsspalten und andere Einstellungen, die Sie am Ursprungsbericht tätigen, im "dynamischen" Bericht nicht übernommen - man kann das nun Stück für Stück ausbauen - oder besser direkt einen interaktiven Bericht hernehmen.

Spaltenstatistiken für APEX-Berichte: Mit dem APEX Repository

Nun zu einem weiteren Beispiel: Diesmal soll, basierend auf den Metadaten eines Berichts, ein weiterer Bericht mit Spaltenstatistiken generiert werden. Erzeugen Sie also nochmals eine Seite mit einem Bericht auf die Tabelle EMP. Diesmal werden wir den Bericht nicht unsichtbar machen, nehmen Sie also einen gut darstellbaren Titel (bspw. Bericht EMP).

Erzeugen Sie dann, wie vorhin, eine weitere Berichtsregion und nehmen Sie als Quelle wiederum PL/SQL Function returning SQL Query. Hinterlegen Sie den folgenden Code.

declare
  l_sql    varchar2(32767);
  l_query  varchar2(32767);
  l_region varchar2(30) := 'Bericht EMP';
  l_first  boolean := true;
begin
  select region_source 
    into l_query
    from apex_application_page_regions 
   where region_name = l_region
     and application_id = :APP_ID 
     and page_id        = :APP_PAGE_ID;

  l_sql := 'with q as ('||l_query||') select ';

  for c in (
       select column_alias, heading
         from apex_application_page_rpt_cols
        where region_name = l_region
          and application_id = :APP_ID 
          and page_id = :APP_PAGE_ID
     order by display_sequence    
  ) loop
    if not l_first then 
      l_sql := l_sql || 'union all (select ';
    end if;
   
    l_sql := l_sql || ''''|| c.heading|| ''' as column_alias, ';
    l_sql := l_sql || 'count(distinct ' || c.column_alias || ')  as cnt_distinct, ';
    l_sql := l_sql || 'to_char(min(' ||    c.column_alias || ')) as minimum, ' ;
    l_sql := l_sql || 'to_char(max(' ||    c.column_alias || ')) as maximum ' ;
    l_sql := l_sql || ' from q ';

    if not l_first then 
      l_sql := l_sql || ')';
    end if;
    l_first := false;
  end loop;
  apex_debug.info('SQL Query is %s', l_sql);
  return l_sql;
end;

Stellen Sie (wie vorhin) Generic Columns auf Yes. Tragen Sie als Generic Column Count nun aber die Zahl 4 ein, denn die SQL-Query, welche von diesem PL/SQL-Code generiert wird, wird stets 4 Spalten zuurückliefern. Tragen Sie dann als Spaltenüberschriften für die Spalten COL01 bis COL04 die Begriffe Column, Distinct Values, Minimum und Maximum ein. Dann können Sie Ihre Seite starten.

Abbildung 11: Dynamischer Bericht mit Spaltenstatistiken für einen anderen Bericht

Die PL/SQL-Funktion generiert anhand der SQL-Quelle und der Spaltendefinitionen des Berichts Bericht EMP eine SQL-Abfrage, welche Spaltenstatistiken berechnet. Und wiederum ist diese Abfrage vollkommen dynamisch - wenn Sie die SQL-Query des Berichts ändern, dass ganz andere Spalten (ganz anderer Tabellen) selektiert werden, so passt sich der dynamische Bericht automatisch an. Natürlich müssen Sie für dieses Statistik-Beispiel bei sehr großen Tabellen aufpassen - es können sich dann längere Laufzeiten ergeben.

Fazit

Das APEX-Repository ist nicht nur das Rückgrat von APEX selbst, auch als Entwickler kann man es nutzen. Am naheliegensten ist hier sicherlich die automatisierte Qualitätssicherung mit SQL-Abfragen - auch große Anwendungen mit vielen Seiten und Komponenten lassen sich so sehr gut prüfen.

Aber auch in der konkreten Anwendungsentwickung können die Dictionary Views des APEX-Repository wertvolle Dienste leisten. Alle Eigenschaften einer APEX-Komponente stehen zur Abfrage bereit. So lassen sich APEX-Komponenten - auf Basis anderer APEX-Komponenten - erstellen. Dynamisierte Hilfeseiten oder Berichte sind nur zwei Beispiele für die Möglichkeiten, die sich so ergeben.

Zurück zur Community-Seite