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 ...?

15. Juni 2009

"Magic" in der Datenbank: Ein Beispiel für externe Prozeduren

English title: "magic" - an example for external procedures

Ich wollte schon länger mal ausprobieren, wie man eigentlich externe C-Programme mit PL/SQL verbinden kann ... und nun habe ich auch endlich ein brauchbares Beispiel gefunden. Wenn man in einer Datenbankapplikation einen BLOB hat, hat man ja nicht zwingenderweise auch den Dateinamen - es kann also die Frage aufkommen, was da eigentlich drin ist. Auf UNIX/Linux-Systemen gibt es ja das praktische Kommando file, welches anhand des Inhalts den Dateityp bestimmen kann. Und diese Funktionalität steht auch in einer Bibliothek zur Verfügung: der libmagic.so. Also könnte man ja mal versuchen, diese als PL/SQL-Prozedur bereitzustellen:
Today I'd like to play a little bit with using external native code from PL/SQL programs. I've found a quite useful example: When a database (PL/SQL) application works with RAW or BLOB content it not necessarily has a file name or a mimetype information in order to determine which kind of content this particular BLOB is. On UNIX/Linux systems there is the file utility which can determine the kind of binary content just by examining the bytes. And this functionality is also contained in the library libmagic.so (which the file utility actually uses I'd think). Therefore I'd like to provide this functionality to PL/SQL.
Zunächst ein kleines C-Programm ... zum Testen ...
First a litte C program ... to test ...
#include <magic.h>
#include <stdio.h>

int main(int argsc, char *args[]) {
  magic_t    g_handle;
  const char *text;

  g_handle = magic_open(MAGIC_MIME);
  magic_load(g_handle, NULL);
  text = magic_file(g_handle, args[1]);
  magic_close(g_handle);
  puts(text);
}
Das ist das file-Kommando - nachprogrammiert. Man sieht also sehr schön, dass man zunächst eine Art "Kontext" öffnet (magic_open), dann die "Datenbank" lädt (NULL lädt das Standard-File; siehe dazu auch die Linux-manpage von "libmagic"), dann kommt das eigentliche Bestimmen des Dateityps (magic_file) und schließlich wird der Kontext geschlossen und das Ergebnis ausgegeben.
This is basically the file utility - reinvented. When using the magic library we need to create a "context" first (magic_open). Then we load the "database" (NULL loads the standard file - see the Linux-manpage of "libmagic" for more information) with magic_load and after that we can determine the file content with magic_file. Finally the context has to be closed with magic_close.
Nun, das möchte ich eigentlich nicht alles in PL/SQL machen; dort reichte mir eine einfache Funktion, die wie das file-Kommando arbeitet. Im Gegensatz dazu solle sie aber ein paar Bytes als RAW entgegennehmen und den Typ eben dieser Bytes bestimmen - wir sind ja in der Datenbank. Und auch hierfür bietet die Bibliothek eine Funktion an: magic_buffer.
I don't want to do all these calls from PL/SQL - I just want to have an equivalent to file utility. But unlike the file utility I want to determine the content type of a byte array (as RAW datatype). The magic library provides a function for that: magic_buffer.
Also: nun geht es los: Zunächst wieder ein kleines C-Programm (oramagic.c), welches zwei Funktionen anbietet: Eine bestimmt den Typ einer Datei zum testen (mimetype_file), die andere bestimmt den "Dateityp" eines Byte-Arrays (magic_bytes) - letztere ist das eigentliche Ziel der Übung.
Now we start. First we code a simple C program (oramagic.c) which encapsulated the libmagic functionality in two C functions: mimetype_bytes (which is the lesson target) and mimetype_file (for testing purposes).
#include <magic.h>
#include <stdio.h>

const char *mimetype_file(const char* filename) {
  magic_t g_handle;
  const char*   text;

  g_handle = magic_open(MAGIC_MIME);
  magic_load(g_handle, NULL);
  text = magic_file(g_handle,filename);
  magic_close(g_handle);
  return text;
}

const char *mimetype_bytes(const void *buffer, size_t len) {
  magic_t g_handle;
  const char*   text;

  g_handle = magic_open(MAGIC_MIME);
  magic_load(g_handle, NULL);
  text = magic_buffer(g_handle,buffer,len);
  magic_close(g_handle);
  return text;
}
Dieses Ding wird nun kompiliert und direkt als Shared Library gelinkt.
This code now gets compiled and linked as shared library.
$ gcc -shared -lmagic ora_magic.c -o ora_magic.so 
Die Datei ora_magic.so könnte man nun nach $ORACLE_HOME/lib kopieren und dann als DB-User (Achtung: das CREATE LIBRARY-Privileg wird benötigt) wie folgt einbinden:
Now copy the file ora_magic.so to $ORACLE_HOME/lib and then log in as a database user (you need the CREATE LIBRARY privilege in order to execute the following script).
drop library magic_lib
/

create or replace library magic_lib
as '{$ORACLE_HOME}/lib/ora_magic.so'
/

create or replace package magic 
is
  function magic_file(filename in varchar2) return varchar2;
  function magic_byte(buffer in raw, buffersize in pls_integer) return varchar2;
end magic;
/
sho err

create or replace package body magic is
  function magic_file(filename in varchar2) return varchar2
  is external name "mimetype_file" library magic_lib language c;
  function magic_byte(buffer in raw, buffersize in pls_integer) return varchar2
  is external name "mimetype_bytes" library magic_lib language c;
end magic;
/
sho err
Das war's: Nun kann man das neue Paket MAGIC testen: Zunächst mit einem Dateinamen:
Now we can test the new "PL/SQL package" MAGIC.
select magic.magic_file('{$ORACLE_HOME}/lib/ora_magic.so') file_type from dual;

FILE_TYPE
-------------------------------------------------
application/x-sharedlib, not stripped
Und wenn man die Bytes in einem BLOB hat, könnte man das wie folgt machen (in diesem Beispiel werden die ersten 200 Byte an die Bibliothek übergeben
And if we have the contents in a BLOB variable we can determine the content type as follows. Note that we don't need to pass the whole BLOB to the library - the first few (here: 200) bytes are sufficient.
declare
  v_text        varchar2(2000);
  v_dateiinhalt blob;
  v_bytes       raw(200);
begin
  v_dateiinhalt := -- BLOB hier holen (aus Tabelle o.ä.)
  v_bytes := dbms_lob.substr(v_dateiinhalt, 200, 1) ; 
  v_text := magic.magic_byte(v_bytes, 200);
  dbms_output.put_line('Dateityp: '||v_text);
end;
/

Dateityp: application/x-sharedlib, not stripped
Das ist eigentlich eine ganz nette Sache ... hat aber auch ein paar Nachteile: Der wichtigste ist, dass es plattformabhängig ist. Das Beispiel hier ist auf Linux gemacht worden. Auf einer anderen Plattform muss man zunächst mal die magic-Bibliothek suchen, dann das C-Programm schreiben, eine Shared-Library erzeugen und dann einbinden. Des weiteren liegt der Code außerhalb der Datenbank - das muss bei Backup & Recovery stets in Betracht gezogen werden.
Basically this is pretty nice ... but there are also some serious disadvantages of linking PL/SQL to external native code. The most important one is that this is platform-dependant. This example was created on a linux system and this code runs unchanged only on a linux system. On other platforms we first have to lookup the magic library, then code a c program (maybe slightly different), run the compiler (slightly different parameters) and link to PL/SQL (unchanged). The second disadvantage is that the code is outside the database which has to be taken in to account for the backup & recovery process.
Aus diesen Gründen ist mir beim Einbinden von externem Code der Ansatz mit Java in der Datenbank weit lieber (und dazu gibt es ja auch den einen oder anderen Post). Bevor man nun aber auf die Suche nach eine Java-Implementierung der "magic"-Bibliothek geht, sollte man sich diesen Artikel ansehen, welcher das Problem mit Tabellen und reinem PL/SQL löst.
For those reasons I'd prefer Java in the database as the method for embedding external code. For the actual problem of having the "magic" functionality in the database this article describes another very interesting approach.

Beliebte Postings