26. Juni 2009

Webreports in 2 Minuten! Mit APEX und dem SQL Developer

English title: Generate a web report with a simple PL/SQL call

Manche von euch kennen vielleicht die Möglichkeit, im Oracle SQL Developer eine SQL-Abfrage einzugeben und aus der Ergebnismenge direkt eine Application Express-Applikation zu machen. Dazu klickt man, nachdem man im SQL Developer eine SQL-Abfrage ausgeführt hat, auf die Daten (data grid), woraufhin der SQL Developer eine APEX-Anwendung generiert. Diese Anwendung enthält dann einen interaktiven Bericht und die Daten können somit auf einfachem Wege publiziert werden. Im SQL Developer sieht der Dialog dann so aus ...
Some readers might know about the SQL Developer feature to publish a SQL Query as an APEX application. When a SQL query is being issued in SQL Developer one just has to right-click on the data grid and choose Publish to APEX in the context menu. SQL Developer then generates an APEX application containing an interactive report for that particular query and can be easily published to end users. The SQL Developer dialog looks as follows ...
Die dabei entstandene APEX-Anwendung stellt sich dann so dar:
The resulting APEX application looks like this:
Interessant ist, was da im Hintergrund ausgeführt wird - um das herauszufinden, habe ich mal einen SQL-Trace angeworfen und in der Tracedatei dann diesen anonynmen PL/SQL-Block gefunden:
The question is, what SQL Developer does in the background. I've turned on SQL tracing in order to get the calls which are issued by SQL Developer. The trace file then contained the following PL/SQL block:
declare     
  PRAGMA AUTONOMOUS_TRANSACTION;  
  l_app_id number;  
begin
  wwv_flow_api.create_app_from_query (
     p_schema                 => user,
     p_sql                    => :SQL,
     p_page_name              => :PAGE_NAME,
     p_workspace_id           => :SGID,
     p_theme                  => :THEME_ID,
     p_theme_type             => :THEME_TYPE,
     p_application_name       => :APP_NAME ,
     p_application_id         => l_app_id,     
     p_authentication         => 'DATABASE ACCOUNT' ,
     p_group_name             => 'Published From SQL Developer' ,
     p_max_displayed_columns  => 10 );      
  :1 := l_app_id;    
  commit; 
end;
Diesen Call kann man nun auch selbst absetzen; die EXECUTE-Privilegien sind an PUBLIC vergeben. Die einzigen Voraussetzungen sind eine vorhandene APEX-Installation in der Datenbank und der aktuell angemeldete Datenbanknutzer muss mit einem APEX-Workspace verknüpft sein. Man muss also nicht unbedingt den SQL Developer bemühen, wenn man möglichst schnell oder gar automatisiert eine APEX-Anwendung aus einem SQL-Kommando generieren möchte. Natürlich müsst Ihr die Bind-Variablen (Doppelpunkt-Syntax) durch eigene PL/SQL-Variablen ersetzen. Die meisten Parameter sind selbsterklärend, andere (die folgenden) lassen sich leicht herausfinden.
  • p_workspace_id: Die Workspace-ID findet sich in der View APEX_WORKSPACES
    select WORKSPACE_ID, WORKSPACE from APEX_WORKSPACES;
    
                      WORKSPACE_ID WORKSPACE
    ------------------------------ --------------------
                 14682820921316761 TESTWS01
                  1841600194840592 PARTNER
      
  • p_theme und p_theme_type: Diese Informationen findet man in der View APEX_THEMES.
    select THEME_NAME, THEME_TYPE from APEX_THEMES;
    
    THEME_NAME           THEME_TYP
    -------------------- ---------
    Theme - 1            BUILTIN
    Theme - 2            BUILTIN
    Theme - 3            BUILTIN
    Theme - 4            BUILTIN
    :                    :
    
And this call is executable by everyone (PUBLIC). So you don't have to use SQL Developer to do this; it's also possible from SQL*Plus or any other database connection. The only requirements are an active APEX installation in the database and the current database user must be connected to an APEX workspace. Of course: you have to replace the bind variables (colon-syntax) with own (PL/SQL or Java) variables. So it's very easy to publish the results of a SQL query to the web; most of the parameters are self-explaining, others are easy to determine - as follows:
  • p_workspace_id: The apex workspace ID can be found in the dictionary view APEX_WORKSPACES
    select WORKSPACE_ID, WORKSPACE from APEX_WORKSPACES;
    
                      WORKSPACE_ID WORKSPACE
    ------------------------------ --------------------
                 14682820921316761 TESTWS01
                  1841600194840592 PARTNER
      
  • p_theme und p_theme_type: This informationen can be found in the view APEX_THEMES.
    select THEME_NAME, THEME_TYPE from APEX_THEMES;
    
    THEME_NAME           THEME_TYP
    -------------------- ---------
    Theme - 1            BUILTIN
    Theme - 2            BUILTIN
    Theme - 3            BUILTIN
    Theme - 4            BUILTIN
    :                    :
    
Die Application-ID wird automatisch generiert und im OUT-Parameter p_application_id zurückgegeben. Diesen Call könnt Ihr aus jeder beliebigen Umgebung heraus absetzen; und somit kann man jeder Anwendung ein einfaches (sogar dynamisches!) Reporting hinzufügen ... mit jeder beliebigen SQL-Abfrage. Und die Darstellung für den Endanwender übernimmt APEX - man muss nichts weiter tun ...
The application ID is being generated and returned in the OUT parameter p_application_id. This call can be used from within any database session; so APEX can also be used as an easy report generator for an application. The application just has to execute this call and can afterwards redirect the browser to the APEX applications' page. The report rendering and handling is then done completely by APEX.
Ist doch gar nicht schlecht, oder ...?
Nice, isn't it ...?

1 Kommentar:

Andreas Dämmrich hat gesagt…

Danke Carsten für den Artikel! Genial einfache Sache, das :-)

Schöne wäre es jetzt natürlich, wenn man das ganze quasi als "Meta-Feature" innerhalb einer vorhandenen APEX-Anwendung nutzen könnte: dazu dürfte aber der Parameter application_id kein out-Parameter sein, sondern mit einer vorhandenen app-id gefüllt werden können ...
... oder aber eine Funktion create_page_from_query bereitgestellt werden - die habe ich aber leider nicht gefunden.

Man müsste also wohl den Umweg über eine neue Application gehen und dann die frisch erstellte Seite in die gewünschte (vorhandene) Application mittels 'Copy' verfrachten... das ginge ja vielleicht sogar auch automatisiert - leider habe ich dafür zumindest aber keine einzelne (d.h. umfassende) api gefunden. Geht wahrscheinlich über ein normales create_page mit den Daten die vorher eben aus den APEX-Tabellen ausgelesen werden.

Naja, nur so ein paar Gedanken, die mir bei Deinem Artikel gekommen sind.

Vielen Dank nochmal,
Andreas

Beliebte Postings