13. Dezember 2012

Welche SQL-Funktionen gibt es? Die Antwort hat eine Dictionary View!

Which SQL functions are there? The answer is in a dictionary view
Vor einiger Zeit; ich weiss nicht mehr genau, wann das war, erreichte mich die Frage, ob man im Dictionary der Datenbank irgendwo die verfügbaren SQL-Funktionen finden könnte. Für in PL/SQL geschriebene Prozeduren und Funktionen gibt es ja ALL_OBJECTS und für die Funktionen und Prozeduren in einem Package gibt es ALL_PROCEDURES. Aber wo findet man die Information, dass es eine Funktion namens NVL oder LISTAGG gibt?
Und erst kürzlich habe ich den Abschnitt Metadata for SQL Operators and Functions im Advanced Application Developer's Guide gefunden. Demnach sind die SQL-Funktionen in der View V$SQLFN_METADATA Informationen über die Argumente in V$SQLFN_ARG_METADATA enthalten. Da gucken wir doch gleich mal rein ...
SQL> select func_id, name, ANALYTIC, AGGREGATE, version, datatype  from v$sqlfn_metadata where name='LISTAGG';

   FUNC_ID NAME                           ANA AGG VERSION      DATATYPE
---------- ------------------------------ --- --- ------------ --------
       890 LISTAGG                        NO  YES V11R1 Oracle ARG 1
       891 LISTAGG                        YES NO  V11R1 Oracle ARG 1

2 Zeilen ausgewählt.
Aha: Die LISTAGG-Funktion gibt es also als Aggregatsfunktion (zur Verwendung mit GROUP BY) und als analytische Variante. Und es gibt sie seit 11g Release 1. Schauen wir in die Argumente ...
SQL> select * from v$sqlfn_arg_metadata where func_id=890;

   FUNC_ID     ARGNUM DATATYPE DESCR
---------- ---------- -------- ------------------------------
       890          1 EXPR
       890          2 EXPR

2 Zeilen ausgewählt.
Die Aggregatsfunktion von LISTAGG nimmt also zwei Parameter vom Typ "Expression" entgegen. Zurückgegeben wird ein Wert vom Datentyp des ersten Arguments (siehe oben). Diese Beschreibungen sind zwar etwas rudimentär, aber man bekommt was raus. Die nun naheliegende Frage ist natürlich: Was ist da noch alles drin ...?
SQL> select func_id, name, version from v$sqlfn_metadata where version like 'V11%';

   FUNC_ID NAME                           VERSION
---------- ------------------------------ ------------
       112 XMLCAST                        V11R1 Oracle
       756 XMLEXISTS2                     V11R1 Oracle
         : :                              : 
       780 REGEXP_COUNT                   V11R1 Oracle
       781 OPTLRXCOUNT                    V11R1 Oracle
       782 XMLDIFF                        V11R1 Oracle
       783 XMLPATCH                       V11R1 Oracle
       784 SYS_OP_VERSION                 V11R1 Oracle
         : :                              :
Man findet viele, viele Funktionen. Manche erkennt man gut wieder, denn sie sind dokumentiert. Und viele andere sind eben nicht dokumentiert. Das Verwenden nicht dokumentierter Funktionen ist nur wenig sinnvoll, wie das folgende Beispiel zeigt:
SQL> select func_id, name, version, datatype from v$sqlfn_metadata where name ='LOBNVL';

   FUNC_ID NAME                           VERSION      DATATYPE
---------- ------------------------------ ------------ --------
       755 LOBNVL                         V10R2 Oracle UNKNOWN

1 Zeile wurde ausgewählt.

SQL> select * from v$sqlfn_arg_metadata where func_id=755;

Es wurden keine Zeilen ausgewählt

SQL> select lobnvl(null, empty_blob()) from dual;
ERROR:
ORA-03001: Funktion nicht implementiert

Es wurden keine Zeilen ausgewählt
Die Hoffnung, in dieser View neue, coole SQL-Funktionen zu finden, dürfte sich kaum erfüllen - denn es gilt natürlich auch weiterhin: Was nicht dokumentiert ist, kann auch wieder verschwinden oder sich in der Funktionalität komplett ändern. Viele der SQL-Funktionen verwendet die Datenbank nur intern - und man selbst sollte die Finger davon lassen.
Interessant sind die Views dennoch: Denn zum einen kann man nachsehen, ab welcher Datenbankversion eine SQL-Funktion verfügbar ist (wirklich nützlich) und zum anderen kann man nachsehen, welche Namen man nicht selbst verwenden sollte. Man sieht, dass Oracle die Namen seiner internen SQL-Funktionen gerne mit SYS_ oder OP_ beginnen lässt. Und trotz alledem darf man mal nachsehen, welche (zum Beispiel) "analytischen" Funktionen es überhaupt gibt ...
SQL> select name, version, descr from v$sqlfn_metadata where analytic='YES'

NAME                           VERSION      DESCR
------------------------------ ------------ ----------------------------------------
SUM                            V816 Oracle  SUM
AVG                            V816 Oracle  AVG
COUNT                          V816 Oracle  COUNT
VARIANCE                       V816 Oracle  VARIANCE
STDDEV                         V816 Oracle  STDDEV
MIN                            V816 Oracle  MIN
MAX                            V816 Oracle  MAX
FIRST_VALUE                    V816 Oracle  FIRST_VALUE
LAST_VALUE                     V816 Oracle  LAST_VALUE
LAG                            V816 Oracle  LAG
LEAD                           V816 Oracle  LEAD
RANK                           V816 Oracle  RANK
DENSE_RANK                     V816 Oracle  DENSE_RANK
CUME_DIST                      V816 Oracle  Window Percentile
:
Some time ago (I can't remember exactly, when this was), I got the question whether there is a Dictionary View where one could query the existing SQL functions. For procedures and functions written in PL/SQL there is ALL_OBJECTS, and for procedures and functions within a package there is ALL_PROCEDURES. But where is the information, that there is a function named NVL or LISTAGG?
Recently, I found a documentation section titled Metadata for SQL Operators and Functions within the Advanced Application Developer's Guide. Therefore these functions are listed in V$SQLFN_METADATA. Information about the arguments is contained in V$SQLFN_ARG_METADATA. I instantly started SQL*Plus and looked into this ...
SQL> select func_id, name, ANALYTIC, AGGREGATE, version, datatype  from v$sqlfn_metadata where name='LISTAGG';

   FUNC_ID NAME                           ANA AGG VERSION      DATATYPE
---------- ------------------------------ --- --- ------------ --------
       890 LISTAGG                        NO  YES V11R1 Oracle ARG 1
       891 LISTAGG                        YES NO  V11R1 Oracle ARG 1

2 rows selected.
There it is: The LISTAGG function is available as aggregate (GROUP BY) as well as analytic (OVER clause) function. And its available since database version 11g Release 1.
SQL> select * from v$sqlfn_arg_metadata where func_id=890;

   FUNC_ID     ARGNUM DATATYPE DESCR
---------- ---------- -------- ------------------------------
       890          1 EXPR
       890          2 EXPR

2 rows selected.
The aggregate variant of LISTAGG takes two arguments which are "expressions". It returns the same datatype is "argument 1". This information is not very comprehensive, but these two views contain interesting information. The next question is (of course): Which SQL functions are there ...?
SQL> select func_id, name, version from v$sqlfn_metadata where version like 'V11%';

   FUNC_ID NAME                           VERSION
---------- ------------------------------ ------------
       112 XMLCAST                        V11R1 Oracle
       756 XMLEXISTS2                     V11R1 Oracle
         : :                              : 
       780 REGEXP_COUNT                   V11R1 Oracle
       781 OPTLRXCOUNT                    V11R1 Oracle
       782 XMLDIFF                        V11R1 Oracle
       783 XMLPATCH                       V11R1 Oracle
       784 SYS_OP_VERSION                 V11R1 Oracle
         : :                              :
You'll see many, many functions. Some of them are well-known, most of the others are totally unknown. That's because Oracle uses them only internally and the functions are therefore not documented. Just for fun: We try to use the function LOBNVL (sounds cool).
SQL> select func_id, name, version, datatype from v$sqlfn_metadata where name ='LOBNVL';

   FUNC_ID NAME                           VERSION      DATATYPE
---------- ------------------------------ ------------ --------
       755 LOBNVL                         V10R2 Oracle UNKNOWN

1 row selected.

SQL> select * from v$sqlfn_arg_metadata where func_id=755;

no rows selected.

SQL> select lobnvl(null, empty_blob()) from dual;
ERROR:
ORA-03001: function not implemented.

no rows selected.
So ... we'll most probably not find a bunch of cool, new functionality we always were looking for. Direct use of the undocumented functions does not make sense: we don't know what they do and how they behave. And as always with undocumented functions: They might vanish or change their behaviour in the next release or even patchset.
But the views are interesting, though. We can look up, since when (and that is really useful) a SQL function is available. And beyond that we could look up which SQL function names we should better not use for own applications. So we can see that Oracle likes to use names starting with SYS_ or OP_. And, of couse, we can just have an overview on, for instance, die existing analytic functions.
SQL> select name, version, descr from v$sqlfn_metadata where analytic='YES'

NAME                           VERSION      DESCR
------------------------------ ------------ ----------------------------------------
SUM                            V816 Oracle  SUM
AVG                            V816 Oracle  AVG
COUNT                          V816 Oracle  COUNT
VARIANCE                       V816 Oracle  VARIANCE
STDDEV                         V816 Oracle  STDDEV
MIN                            V816 Oracle  MIN
MAX                            V816 Oracle  MAX
FIRST_VALUE                    V816 Oracle  FIRST_VALUE
LAST_VALUE                     V816 Oracle  LAST_VALUE
LAG                            V816 Oracle  LAG
LEAD                           V816 Oracle  LEAD
RANK                           V816 Oracle  RANK
DENSE_RANK                     V816 Oracle  DENSE_RANK
CUME_DIST                      V816 Oracle  Window Percentile
:

4. Dezember 2012

Event im Januar 2013: Was steckt in den Oracle-Datenbankeditionen (XE,SE1,SE,EE) ...?

This post is about an event in german language which takes place in Germany in January 2013 - and therefore in german only.
Im Januar 2013 veranstalten meine Kollegen und ich mal wieder eine Veranstaltung (Oracle Developer Day) zum Thema Datenbank - und zwar Datenbank "pur". Es geht um die verschiedenen Editionen (SE,SE1,EE), was drinsteckt und wann man was braucht.
Termine, Orte und Anmeldelinks findet Ihr hier - die Teilnahme ist kostenlos - also am besten gleich anmelden.

Die Oracle Datenbank in der Praxis
Was steckt in den Editionen? Einsatzgebiete, Tipps und Tricks zum Mitnehmen, inkl. Ausblick auf neue Funktionen
Oracle Developer Day "Die Oracle Datenbank in der Praxis" im Januar 2013 Die Einsatzgebiete für die Oracle Datenbank sind vielfältig, und so bietet Oracle seine marktführende Datenbank in unterschiedlichen Editionen an. Über 30 Jahre Erfahrung in der Weiterentwicklung haben zu einer Fülle von nützlichen Features geführt, welche in den verschiedenen Ausführungen sinnvoll aufgeteilt sind. Ein Ausblick auf die Funktionen der für 2013 geplanten neuen Datenbank-Version rundet den Workshop ab.
In dieser speziell von der BU DB zusammengestellten Veranstaltung werden wir Sie neben vielen Tipps und Tricks zu folgenden Themen auf den neuesten Stand bringen:
  • Die Unterschiede der Editionen und ihre Geheimnisse
  • Umfangreiche Basisausstattung auch ohne Option
  • Performance und Skalierbarkeit in den einzelnen Editionen
  • Kosten- und Ressourceneinsparung leicht gemacht
  • Sicherheit in der Datenbank
  • Steigerung der Verfügbarkeit mit einfachen Mitteln
  • Der Umgang mit großen Datenmengen
  • Cloud Technologien in der Oracle Datenbank

19. November 2012

Externe Tabellen für eine entfernte(!) Datei erzeugen: How To

Create an external tables - based on a remote(!) file
In diesem Blog-Posting möchte ich mich dem Thema "Externe Tabellen" widmen und ein in Oracle11g neu eingeführtes Feature nochmals vorstellen: Den Präprozessor für externe Tabellen. Dieser ist - kurz gesagt - ein Executable, welches die Daten der externen Tabelle (also der Datei) quasi "vorbehandelt" - die externe Tabelle selbst basiert dann auf der Ausgabe des Präprozessors. Das "klassische" Beispiel ist somit eine externe Tabelle auf einer .gz-Datei - der Präprozessor wäre dann das gunzip Werkzeug.
So weit - so gut. Das Feature lässt sich aber auch anders nutzen - denn es kann ja ein beliebiges Executable hergenommen werden. Also könnte man sich ja auch das Kopieren der Datei vom entfernten Server zur Datenbankmaschine sparen und diese direkt vom Remote-Server lesen - und genau das möchte ich hier vorstellen. Wir wollen mit der Oracle-Datenbank eine externe Tabelle für die Datei error_log des Apache-Webservers auf einem entfernten Rechner erstellen. Basis für diess Blog-Posting ist der Tipp Externe Tabellen: Die Apache-Datei "error_log" als APEX-Bericht der deutschsprachigen APEX-Community.

Vorbereitungen

Zunächst müssen wir in die Lage kommen, auf Betriebssystem-Ebene des Datenbankservers die entfernte Datei anzeigen zu lassen. Heutzutage machen wir dazu kein rlogin oder telnet mehr, sondern wir verwenden ssh. Und damit keine Passwörter in die Skriptdateien geschrieben werden müssen, ist der erste Schritt das Austauschen der Schlüssel. Auf dem Datenbankserver wird ein Schlüsselpaar wie folgt generiert:
[oracle@databaseserver]$ ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa

Generating public/private dsa key pair.
Created directory '/home/oracle/.ssh'.
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
32:b1:02:a2:55:94:d5:49:01:7b:51:db:b5:29:d8:d3 oracle@databaseserver.mydomain.com
In der Datei $HOME/.ssh/id_dsa.pub befindet sich nun der öffentliche Schlüssel des Users oracle auf dem Server databaseserver. Dieser muss nun in die Datei $HOME/.ssh/authorized_keys auf dem entfernten Recher (apacheserver.mydomain.com) kopiert werden. Diese Datei sollte dann in etwa so aussehen.
[oracle@apacheserver]$ cat ~/.ssh/authorized_keys
ssh-dss AAAAB3NzaC1kc3MAAACBAJrsXSk8zxAiGiAoEjbxYTCKoHgZCXT9+pfSgSnNAxHVaD1ND5UkOii2a0ySopS3arxHNiHXT/rU9sgstUtn+vkMPDFOHtoLOP5hJ6adaq7ed3xILeljgj21388432kjdnsakjfdsajfcbO1K5PYJ8oXhasIOX/lebKiRUfkMtAAAAFQDjsMdyn3dkIC7rZfmU5DdA3XfwBQAAAIAKCfCjqy4sQ/JlOHVoHw83amB4XzCcUTTKpa7X+f69zNxNa6Q9arG4VO0iODtiHiotzTlWaMsTPyrSgmhGPsYnaVLNyBR7o6a7YAWt1EvvxC8IM64RCG9U8owN2o5OXTsahd872909123ß219kjdsalk0943jHrPmECvdpwzo47jd9rFkgfqQAAAIAROytFKMsuhldyF7tPfcWywqDqzQDLjiyhm05eYUYsjerR6+LrAayRGbHzPPBjK5KAGBqBE2EYUtWQZrmqk226EFIT+KAxjHFxWtl3Rb6bfJSmWEQGcG3uD5gcNJ1yowHuvnLvfy1eUyTErGylAzCUO7pMbz4ZW3QXkg1Qzdfgkw== oracle@databaseserver.mydomain.com
Danach kann ein erster Test erfolgen: Öffnet man eine SSH-Verbindung von databaseserver zum apacheserver, dann darf kein Login-Dialog mehr kommen - die Remote-Shell erscheint sofort (beim allerersten Mal kommt aber noch die übliche Frage, ob der Schlüssel des entfernten Rechners in den Cache aufgenommen werden soll).
[oracle@databaseserver]$ ssh -l oracle spatial.de.oracle.com
Last login: Mon Nov 19 10:02:55 2012 from dhcp-u19825.mydomain.com
[oracle@apacheserver ~]$
Der nächste Schritt ist nun sehr einfach: Mit einem einfachen SSH-Kommando kann die ganze entfernte Datei auf der Konsole des Datenbankservers ausgegeben werden - dieses Kommando packen wir in eine Skriptdatei wie folgt:
#!/bin/sh
/usr/bin/ssh -l oracle apacheserver.mydomain.com cat /oracle/u01/app/oracle/product/ohs/ohs/logs/error_log
Diese Datei nennen wir dann get-exttab, machen sie ausführbar mit chmod +x, und legen Sie ins Verzeichnis $HOME/exttab/scripts. Daneben legen wir noch das Verzeichnis $HOME/exttab/data an. In letzteres legen wir eine Datei namens error_log - die Inhalte sind egal. Die Verzeichnisstruktur sollte dann so aussehen:
[oracle@databaseserver]$ touch $HOME/exttab/data/error_log
[oracle@databaseserver]$ find . 
./exttab
./exttab/scripts
./exttab/scripts/get-exttab
./exttab/data
./exttab/data/error_log
Ihr könnt das ganze noch testen: Ein Aufruf des Skripts get-exttab sollte nun die Inhalte der Datei error_log des entfernten Rechners apacheserver anzeigen.
[oracle@databaseserver]$ $HOME/exttab/scripts/get-exttab
[Thu Dec 17 13:33:57 2009] [error] [client X.X.X.X] File does not exist: /oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico
[Thu Dec 17 13:34:21 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
[Thu Dec 17 13:34:22 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
:
Damit sind die Vorbereitungen abgeschlossen: Nun geht es zur Datenbank.

Einrichten der Directory-Objekte

Damit das Präprozessor-Feature nicht zum Sicherheits-Albtraum wird, ist es wichtig, die Directory-Objekte richtig anzulegen. Für das Präprozessor-Feature wurde eigens das neue Privileg EXECUTE ON DIRECTORY eingeführt - mit welchem naturgemäß sehr vorsichtig umgegangen werden sollte. Es sollte niemals gemeinsam mit dem WRITE ON DIRECTORY Privileg vergeben werden; denn der User, der diese beiden Privilegien hat, kann danach beliebige Betriebssystem-Kommandos ausführen. Das Datenbankschema, welches die externe Tabelle anlegen möchte, braucht also das READ- und EXECUTE-Privileg auf das Directory $HOME/exttab/scripts sowie READ und WRITE Privilegien auf $HOME/exttab/data. Letzteres enthält nur die Dummy-Datei error_log und wird außerdem als Ablage für entstehende Logdateien und Badfiles verwendet.
create directory scriptdir as '/home/oracle/exttab/scripts/'
/

create directory datadir as '/home/oracle/exttab/data'
/

grant read,execute on directory scriptdir to testit
/

grant read, write on directory datadir to testit
/

Erzeugen und testen der externen Tabelle

Als nächstes kann die externe Tabelle angelegt werden:
drop table ohs_error_log
/

CREATE TABLE ohs_error_log (
  zeitstempel date,
  schweregrad varchar2(200),
  client_ip   varchar2(200),
  error_modul varchar2(200),
  error_text  varchar2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY datadir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR SCRIPTDIR:'get-exttab'
    nobadfile nologfile
    fields MISSING FIELD VALUES ARE NULL(
      zeitstempel date "DY MON DD HH24:MI:SS YYYY" terminated by whitespace enclosed by '[' and ']'  ,
      schweregrad char terminated by whitespace enclosed by '[' and ']',
      client_ip   char terminated by whitespace enclosed by '[client' and ']' ltrim,
      error_modul char terminated by ':',
      error_text  char (100000) ltrim
    )
   )
   LOCATION ('error_log') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
Das interessante ist die PREPROCESSOR-Klausel: Sie besagt, dass die Inhalte der Datei error_log im Verzeichnis DATADIR (DEFAULT DIRECTORY) vom Skript get-exttab im Directory SCRIPTDIR aufbereitet werden sollen. Die Datei error_log ist, wie schon gesagt, natürlich leer - das Skript get-exttab holt einfach die Inhalte vom entfernten Server ab. Man sieht deutlich die Trennung der Directory-Objekte. Das DEFAULT DIRECTORY namens DATADIR wird zum Lesen der Pseudodatei und zur Ablage etwaiger Log- und Badfiles verwendet - das Directory SCRIPTDIR, welches das Shellskript enthält und auf welches die Execute-Privilegien eingeräumt wurden, steht in der PREPROCESSOR-Klausel. Nun wird jedesmal, wenn diese externe Tabelle selektiert wird ...
  • die Pseudodatei error_log aus dem DATADIR gelesen (diese ist leer)
  • deren Inhalte (in diesem Fall also "nichts") wird an das Skript get-exttab im SCRIPTDIR übergeben
  • get_exttab holt die Dateiinhalte vom Rechner apacheserver
  • die abgerufenen Inhalte werden mit Hilfe der SQL*Loader-Spezifikation verarbeitet und als externe Tabelle aufbereitet.
Also müsste man die Tabelle ja nun selektieren können ...
SQL> select * from ohs_error_log where rownum <20;

23.09.2009 17:40:31
error
10.165.250.114
File does not exist
/oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico

23.09.2009 17:40:34
error
10.165.250.114
mod_plsql
/pls/apex/apex HTTP-503 ORA-28000 ORA-28000: the account is locked\n

:
Für externe Tabellen öffnen sich damit völlig neue Möglichkeiten - so kann man mit dem Shellskript natürlich nicht nur eine Datei von einem externen Server abrufen, sondern auch die Ausgabe eines Programms als externe Tabelle anbinden - alles ist möglich.
In this blog posting I'd like to write a bit about external tables - and particularly about the preprocessor feature, which was introduced in Oracle11g. Briefly, the proprocessor is an executable which processes the file contents before these are being passed to the database. The table data - in the database - is then based on the proprocessed file contents. The "classic" example is an external table based on a .gz file: gunzip would be the preprocessor in that case.
So far - so good. Given that the preprocessor can be any executable, we can use that feature in far more cases than just processing the contents of a file in the database server's filesystem. When it's about providing contents from a file on a remote server as an external table, we could omit copying the file to the database server and use the preprocessor to grab the file contents directly. And such an example is the topic of this blog posting: We'll create an external table in the database, based on the Apache Webservers' error_log file. But the Apache Webserver resides an a remote server - so we'll use the preprocessor feature.

Prerequisites

Firstly, we'll need to be able to grab the remote file's contents at the operating system level. Expressed otherwise: We need the executable which gets the remote file contents for us and will be used as the preprocessor afterwards. Since we write the year 2012, we'll use ssh for the communication from the database server (databaseserver.mydomain.com) to the machine hosting the Apache Webserver (apacheserver.mydomain.com).
So we'll have to configure ssh in order to be able to execute the cat command on apacheserver - from databaseserver. First step is to generate a key pair on databaseserver and to provide the public key to apacheserver. After this, apacheserver is able to verify the identity of databaseserver based on the shared public key.
[oracle@databaseserver]$ ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa

Generating public/private dsa key pair.
Created directory '/home/oracle/.ssh'.
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
32:b1:02:a2:55:94:d5:49:01:7b:51:db:b5:29:d8:d3 oracle@databaseserver.mydomain.com
Now oracle's public key is contained in the file $HOME/.ssh/id_dsa.pub. We'll copy this to apacheserver and append it to the file $HOME/.ssh/authorized_keys. This file should then look as follows:
[oracle@apacheserver]$ cat ~/.ssh/authorized_keys
ssh-dss AAAAB3NzaC1kc3MAAACBAJrsXSk8zxAiGiAoEjbxYTCKoHgZCXT9+pfSgSnNAxHVaD1ND5UkOii2a0ySopS3arxHNiHXT/rU9sgstUtn+vkMPDFOHtoLOP5hJ6adaq7ed3xILeljgj21388432kjdnsakjfdsajfcbO1K5PYJ8oXhasIOX/lebKiRUfkMtAAAAFQDjsMdyn3dkIC7rZfmU5DdA3XfwBQAAAIAKCfCjqy4sQ/JlOHVoHw83amB4XzCcUTTKpa7X+f69zNxNa6Q9arG4VO0iODtiHiotzTlWaMsTPyrSgmhGPsYnaVLNyBR7o6a7YAWt1EvvxC8IM64RCG9U8owN2o5OXTsahd872909123ß219kjdsalk0943jHrPmECvdpwzo47jd9rFkgfqQAAAIAROytFKMsuhldyF7tPfcWywqDqzQDLjiyhm05eYUYsjerR6+LrAayRGbHzPPBjK5KAGBqBE2EYUtWQZrmqk226EFIT+KAxjHFxWtl3Rb6bfJSmWEQGcG3uD5gcNJ1yowHuvnLvfy1eUyTErGylAzCUO7pMbz4ZW3QXkg1Qzdfgkw== oracle@databaseserver.mydomain.com
Then we can perform a first test: Opening an ssh connection from databaseserver to apacheserver should result in a shell prompt directly - without any password login dialog. Authentication must be performed just with the keys exchanged. When opening the connection for the very first time, the usual dialog about adding the server's key to the cache will appear.
[oracle@databaseserver]$ ssh -l oracle spatial.de.oracle.com
Last login: Mon Nov 19 10:02:55 2012 from dhcp-u19825.mydomain.com
[oracle@apacheserver ~]$
The next step is rather simple: The remote file can be listed on the local console with a simple SSH command:
#!/bin/sh
/usr/bin/ssh -l oracle apacheserver.mydomain.com cat /oracle/u01/app/oracle/product/ohs/ohs/logs/error_log
The script file is named get-exttab and then placed in the directory $HOME/exttab/scripts (don't forget to make it executable with chmod +x). Beyond this, we'll create the directory $HOME/exttab/data, into which we'll place a file named error_log. The contents of that file don't matter - it's just a dummy file for the external table mechanism in Oracle. The "real" file contents are being grabbed with the shell script. After that we should habe the following file/folder structure.
[oracle@databaseserver]$ touch $HOME/exttab/data/error_log
[oracle@databaseserver]$ find . 
./exttab
./exttab/scripts
./exttab/scripts/get-exttab
./exttab/data
./exttab/data/error_log
Now we can do another test: Calling the script get-exttab should result in the contents of the remote file being listed on the console.
[oracle@databaseserver]$ $HOME/exttab/scripts/get-exttab
[Thu Dec 17 13:33:57 2009] [error] [client X.X.X.X] File does not exist: /oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico
[Thu Dec 17 13:34:21 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
[Thu Dec 17 13:34:22 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
:
Now we have met all prerequisites. The next steps are being performed in the database.

Creating directory objects

When using the preprocessor feature, one should take particular care about security. Executing the preprocessor means executing an operating system command on the database server. Oracle introduced a new privilege EXECUTE ON DIRECTORY for that purpose. A user having that privilege can execute any file residing in the given directory. So having both WRITE ON DIRECTOTY and EXECUTE ON DIRECTORY privileges might result in a security nightmare: that user now can execute any operating system command on the database server. Take care!
So we have seen the reason for placing the script get-exttab into a separate directory. Our database schema will only get READ and EXECUTE privileges on that directory. For the other directory $HOME/exttab/data the database schema gets READ and WRITE privileges: It needs to "read" the dummy file error_log from there and to write Logfiles and Badfiles.
create directory scriptdir as '/home/oracle/exttab/scripts/'
/

create directory datadir as '/home/oracle/exttab/data'
/

grant read,execute on directory scriptdir to testit
/

grant read, write on directory datadir to testit
/

Erzeugen und testen der externen Tabelle

Now we can create the external table as follows. I'll not elaborate on external tables themselves and the SQL*Loader syntax - there's plenty of documentation and other blog postings about this.
drop table ohs_error_log
/

CREATE TABLE ohs_error_log (
  zeitstempel date,
  schweregrad varchar2(200),
  client_ip   varchar2(200),
  error_modul varchar2(200),
  error_text  varchar2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY datadir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR SCRIPTDIR:'get-exttab'
    nobadfile nologfile
    fields MISSING FIELD VALUES ARE NULL(
      zeitstempel date "DY MON DD HH24:MI:SS YYYY" terminated by whitespace enclosed by '[' and ']'  ,
      schweregrad char terminated by whitespace enclosed by '[' and ']',
      client_ip   char terminated by whitespace enclosed by '[client' and ']' ltrim,
      error_modul char terminated by ':',
      error_text  char (100000) ltrim
    )
   )
   LOCATION ('error_log') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
The PREPROCESSOR clause is the most interesting part. It states that the file contents are being preprocessed by the script get-exttab in the directory SCRIPTDIR. The file contents come from the file error_log (LOCATION) in the directory DATADIR (DEFAULT DIRECTORY). So when the table is being selected ...
  • the dummy file error_log within DATADIR is being read (the file is empty in our case)
  • then the executable script get-exttab within SCRIPTDIR will be called
  • get_exttab grabs the file contents from the remote server
  • the file contents will then be processed according to the SQL*Loader specification and prepared as an external table.
So we can select the table now:
SQL> select * from ohs_error_log where rownum <20;

23.09.2009 17:40:31
error
10.165.250.114
File does not exist
/oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico

23.09.2009 17:40:34
error
10.165.250.114
mod_plsql
/pls/apex/apex HTTP-503 ORA-28000 ORA-28000: the account is locked\n

:
The preprocessor clause for external tables is a very powerful new feature: This use cases range from simple 'gunzipping' files over grabbing contents from remote servers up to even creating external tables based on application output. There are no limits.

29. Oktober 2012

Hilfreiche Linemode Skripte: Zusammenfassung in der deutschspr. DBA Community

Heute möchte ich auf ein Posting meiner Kollegen von der deutschsprachigen DBA Community hinweisen: Ihr kennt sicherlich die Kommandozeilenskripte aus $ORACLE_HOME/rdbms/admin, die man immer wieder braucht. Im DBA Community-Posting "Hilfreiche Linemode-Skripte sind die wichtigsten mitsamt deren Bedeutung erläutert.
Today I'd like to emphasize a posting about useful line mode scripts within $ORACLE_HOME from my colleagues maintaining the german language DBA community webpage. It's in german only, but for english readers Google Translate might do the trick.

10. Oktober 2012

DBMS_LDAP: Suche mit einem binären Filter ("ObjectGUID")

DBMS_LDAP: Searching with a binary filter ("ObjectGUID")
Vor mittlerweile einiger Zeit (um genau zu sein: vor 5 Jahren) hatte ich das Blog-Posting "LDAP-Server abfragen ... mit SQL veröffentlicht, welches zeigt, wie man mit DBMS_LDAP auf einen LDAP-Server zugreift und die Ergebnisse als Tabelle aufbereiten kann. Das Paket DBMS_LDAP war eigentlich gar nicht mein Ziel, sondern vielmehr die darin beschriebene "andere Art der Table Function". Aber egal - dieses Blog-Posting gehört zu denen, zu denen ich die meisten Fragen bekomme. LDAP-Zugriffe aus der Datenbank scheinen weit verbreitet und häufig gefordert zu sein.
Eine der Fragen war die von André Meier nach der Möglichkeit, eine LDAP-Suche in einem Microsoft Active Directory anhand der ObjectGUID durchführen zu können. Zuerst dachte ich, dass man das mit einem einfachen LDAP-Filter á la (OBJECTGUID=\55\34\5D\11\96\95\24\47\88\22\85\01\52\EB\13\F9) macht - aber das war falsch gedacht. Denn auch ein LDAP-Server kennt Datentypen; und der Datentyp einer ObjectGUID im Active Directory ist Binary - in der Oracle-Datenbank würde man RAW nehmen.
Und genau hier ist das Problem: Die Funktion SEARCH_S im Package DBMS_LDAP unterstützt für den Parameter FILTER nur Datentypen VARCHAR2 - eine Überladung mit dem Datentypen RAW gibt es nicht. Nun kann man sich Tricks überlegen, einen RAW-Wert mit UTL_RAW als VARCHAR2 aufzufassen ...
SQL> select utl_raw.cast_to_varchar2(hextoraw('55345d11969524478822850152eb13f9')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('55345D11969524478822850152EB13F9'))
--------------------------------------------------------------------------------
U4]?¦¦$G¦"¦?R¦

1 Zeile wurde ausgewählt.
... aber das hilft alles nix - denn man kann sich nie darauf verlassen, dass der String auf dem Weg von der Datenbank zum LDAP-Server nicht doch konvertiert wird - damit wird der übergebene Filter verfälscht und die Suche schlägt fehl. Generell kann man also festhalten, dass die Suche nach einer ObjectGUID in einem Microsoft Active Directory mit dem Paket DBMS_LDAP nicht möglich ist - oder allgemeiner formuliert: RAW-Werte können mit DBMS_LDAP nicht als Filter übergeben werden (das Abrufen von binären Attributen ist dagegen möglich).
Aber es gibt ja noch Java in der Datenbank ...
Mein nächster Vorschlag war denn auch, "Java in der Datenbank" zu nutzen. André Meier und Stefan Bucholz haben dann eine Java-Klasse geschrieben, die das Problem mit Hilfe der LDAP-Client API in Java löst (erstmal außerhalb der Datenbank). In Java kann man auch zur Suche binäre Werte an den LDAP-Server übergeben. Und wenn das mit Java außerhalb der Datenbank läuft, läuft das auch in der Datenbank. Der Java-Code von Stefan Buchholz sieht dann so aus - und mit einem CREATE OR REPLACE JAVA SOURCE bekommt man das Java-Programm auch direkt in die Datenbank ...
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "XpLdapClientJava" AS 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Hashtable;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
import javax.naming.directory.SearchControls;
import javax.naming.directory.SearchResult;
import javax.naming.ldap.InitialLdapContext;
import javax.naming.ldap.LdapContext;

import java.sql.*;

/**
* @author Stefan Buchholz, Leipzig
*/

public class XpLdapClientQuery {
  private static XpLdapClientQuery ldapclient = null;
  private String ldapAdServer;
  private String ldapSearchBase;
  private String ldapUsername;
  private String ldapPassword;
  private LdapContext ctx;
  
  private XpLdapClientQuery() throws Exception {
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
      "SELECT 'ldap://' || LDAP_HOST, LDAP_SEARCH_BASE, LDAP_USER, LDAP_PASSWD FROM XP_LDAP_CLIENT_PROPERTIES"
    );
    if (rs.next()) {
      this.ldapAdServer = rs.getString(1);
      this.ldapSearchBase = rs.getString(2);
      this.ldapUsername = rs.getString(3);
      this.ldapPassword = rs.getString(4);
    }
    rs.close();
    stmt.close();

    Hashtable<String, String> env = new Hashtable<String, String>();
    if (ldapUsername == null) {
      env.put(Context.SECURITY_AUTHENTICATION, "none");
    } else {
      env.put(Context.SECURITY_AUTHENTICATION, "simple");
      env.put(Context.SECURITY_PRINCIPAL, ldapUsername);
      env.put(Context.SECURITY_CREDENTIALS, ldapPassword);
    }
    env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
    env.put(Context.PROVIDER_URL, ldapAdServer);

    env.put("java.naming.ldap.attributes.binary", "objectGUID");
    ctx = new InitialLdapContext(env, null);
  }

  public static String getAccount(String p_uid) throws Exception {
    if (ldapclient == null) {
      ldapclient = new XpLdapClientQuery();
    }
    return ldapclient.findAccountNameByObjectId(p_uid);
  }

  private String findAccountNameByObjectId(String obectId) throws Exception {
    String searchFilter = "(&(objectClass=user)(objectguid=" + obectId + "))";
    SearchControls searchControls = new SearchControls();
    searchControls.setSearchScope(SearchControls.SUBTREE_SCOPE);
    String ret = null;
    try {   
      NamingEnumeration<SearchResult> results = ctx.search(ldapSearchBase, searchFilter, searchControls);
      if (results.hasMoreElements()) {
        SearchResult searchResult = results.nextElement();
        if (results.hasMoreElements()) {
          throw new Exception("MORE THAN ONE MATCH FOR GIVEN OBJECTGUID");
        } else {
          ret = (String) searchResult.getAttributes().get("sAMAccountName").get();
        }
      }
    } catch (NullPointerException e) {
      throw new Exception ("NO MATCH FOR GIVEN OBJECTGUID");
    }   
    return ret;
  }
}
/
Das Programm setzt voraus, dass die Informationen zum LDAP-Server, also Hostname, TCP/IP-Port, Username und Passwort in der Tabelle XP_LDAP_CLIENT_PROPERTIES zu finden sind.
create table XP_LDAP_CLIENT_PROPERTIES(
   LDAP_HOST         varchar2(100), 
   LDAP_SEARCH_BASE  varchar2(500), 
   LDAP_USER         varchar2(200), 
   LDAP_PASSWD       varchar2(200)
)
/
Der entscheidende Unterschied zu DBMS_LDAP ist in der Zeile ...
   env.put("java.naming.ldap.attributes.binary", "objectGUID");
Womit klar ist, dass dieser Wert als binär zu behandeln ist und in der Form \XX\XX\XX ... übergeben werden kann. Bei Java in der Datenbank kann jede Java-Methode, die "static" ist, mit Hilfe einer PL/SQL Call Specification auf PL/SQL abgebildet werden.
/**
 * Diese Wrapper-Funktion fuer Java gibt fuer in_objectguid (MSAD) den samaccountname zurück.
 * @AUTHOR  AMei, 20120802
 * @PARAM:  in_objectguid
 * @RETURN: VARCHAR2
 * @EXCEPTION: "NO MATCH FOR GIVEN OBJECTGUID"
 * @SEE: XpLdapClientJava, XpLdapClientQuery
 * SELECT get_samaccountname ('\59\34\5d\11\96\95\24\47\88\22\85\01\52\eb\13\f9') AS samaccountname FROM dual;
 */
CREATE OR REPLACE FUNCTION get_samaccountname (
  in_objectguid VARCHAR2
) RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'XpLdapClientQuery.getAccount(java.lang.String) return java.lang.String';
Bevor wir es ausprobieren können, braucht der Datenbank-User noch die nötigen Netzwerkprivilegien, denn ein "normaler" Datenbankuser hat erstmal nicht das Privileg, Netzwerkoperationen durchzuführen. Der Datenbankadministrator kann die Rechte wie folgt einräumen.
begin
  dbms_java.grant_permission(
    grantee           => '[Datenbankuser, welcher die LDAP-Suche durchführen soll]',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => '[LDAP-Server oder "*" für das ganze Netzwerk]',
    permission_action => 'connect,resolve'
  );
end;
Damit ist es fertig. Nun kann auch eine "ObjectGUID"-Suche wie folgt durchgeführt werden.
SELECT get_samaccountname ('\59\34\5d\11\96\95\24\47\88\22\85\01\52\eb\13\f9') AS samaccountname FROM dual;

SAMACCOUNTNAME
--------------------
      Max_Mustermann
Und mit diesem Accountnamen kann man dann per DBMS_LDAP weitersuchen ... den der liegt ja nun als String vor. Viel Spaß beim Ausprobieren ...
Almost five years ago, I published a blog posting about how to access an LDAP Server ... with SQL. The primary goal was indeed the "different" kind of table function which I used, and not that much DBMS_LDAP. DBMS_LDAP was just the "showcase". But this is one of the most popular blog postings and I still get frequent questions about it.
And this blog posting is about one of these questins: André Meier asked, how he could perform an LDAP search for an objectGUID within Microsoft Active Directory. My first thought was that this is a no-brainer and a simple LDAP filter like (OBJECTGUID=\55\34\5D\11\96\95\24\47\88\22\85\01\52\EB\13\F9) would to the trick - but this is not the case. LDAP servers also have a concept of data types; and the data type of an objectGUID is not string, its binary. The mapping within the Oracle database would be RAW.
And this exactly is the root cause of the proplem - the FILTER argument of DBMS_LDAP.SEARCH_S is a VARCHAR2 data type - a variant with RAW does not exist. We could know try some dirty tricks like an explicit cast of a VARCHAR2 to a RAW ...
SQL> select utl_raw.cast_to_varchar2(hextoraw('55345d11969524478822850152eb13f9')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('55345D11969524478822850152EB13F9'))
--------------------------------------------------------------------------------
U4]?¦¦$G¦"¦?R¦

1 row selected.
... but this will not work - we cannot guarantee that this VARCHAR2 value will reach the LDAP server unchanged. DBMS_LDAP treats it as a VARCHAR2 - so the bytes might be converted on their way to the LDAP server - and the query will not return the desired results. In principle, DBMS_LDAP does not allow search operations with binary filters - or, expressed otherwise: RAW values cannot be supplied as arguments for LDAP searches with DBMS_LDAP (but fetching binary values from a search result set is supported).
But we still have java in the database ...
So my next proposal was to use Java in the database, since the Java class library contains some LDAP client classes. So André Meier and Stefan Bucholz wrote a Java class which solves the problem - since in Java there is an option to use a binary search filter. In the first step, this Java class ran outside the database, in the next step, we loaded the Java source into the database (CREATE OR REPLACE ... JAVA SOURCE ...) and executed it there.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "XpLdapClientJava" AS 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Hashtable;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
import javax.naming.directory.SearchControls;
import javax.naming.directory.SearchResult;
import javax.naming.ldap.InitialLdapContext;
import javax.naming.ldap.LdapContext;

import java.sql.*;

/**
* @author Stefan Buchholz, Leipzig
*/

public class XpLdapClientQuery {
  private static XpLdapClientQuery ldapclient = null;
  private String ldapAdServer;
  private String ldapSearchBase;
  private String ldapUsername;
  private String ldapPassword;
  private LdapContext ctx;
  
  private XpLdapClientQuery() throws Exception {
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
      "SELECT 'ldap://' || LDAP_HOST, LDAP_SEARCH_BASE, LDAP_USER, LDAP_PASSWD FROM XP_LDAP_CLIENT_PROPERTIES"
    );
    if (rs.next()) {
      this.ldapAdServer = rs.getString(1);
      this.ldapSearchBase = rs.getString(2);
      this.ldapUsername = rs.getString(3);
      this.ldapPassword = rs.getString(4);
    }
    rs.close();
    stmt.close();

    Hashtable<String, String> env = new Hashtable<String, String>();
    if (ldapUsername == null) {
      env.put(Context.SECURITY_AUTHENTICATION, "none");
    } else {
      env.put(Context.SECURITY_AUTHENTICATION, "simple");
      env.put(Context.SECURITY_PRINCIPAL, ldapUsername);
      env.put(Context.SECURITY_CREDENTIALS, ldapPassword);
    }
    env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
    env.put(Context.PROVIDER_URL, ldapAdServer);

    env.put("java.naming.ldap.attributes.binary", "objectGUID");
    ctx = new InitialLdapContext(env, null);
  }

  public static String getAccount(String p_uid) throws Exception {
    if (ldapclient == null) {
      ldapclient = new XpLdapClientQuery();
    }
    return ldapclient.findAccountNameByObjectId(p_uid);
  }

  private String findAccountNameByObjectId(String obectId) throws Exception {
    String searchFilter = "(&(objectClass=user)(objectguid=" + obectId + "))";
    SearchControls searchControls = new SearchControls();
    searchControls.setSearchScope(SearchControls.SUBTREE_SCOPE);
    String ret = null;
    try {   
      NamingEnumeration<SearchResult> results = ctx.search(ldapSearchBase, searchFilter, searchControls);
      if (results.hasMoreElements()) {
        SearchResult searchResult = results.nextElement();
        if (results.hasMoreElements()) {
          throw new Exception("MORE THAN ONE MATCH FOR GIVEN OBJECTGUID");
        } else {
          ret = (String) searchResult.getAttributes().get("sAMAccountName").get();
        }
      }
    } catch (NullPointerException e) {
      throw new Exception ("NO MATCH FOR GIVEN OBJECTGUID");
    }   
    return ret;
  }
}
/
The program requires that the LDAP connection details (hostname, port number, LDAP username and password) are stored in the table XP_LDAP_CLIENT_PROPERTIES (we are in the database, so we don't use property files, we use tables).
create table XP_LDAP_CLIENT_PROPERTIES(
   LDAP_HOST         varchar2(100), 
   LDAP_SEARCH_BASE  varchar2(500), 
   LDAP_USER         varchar2(200), 
   LDAP_PASSWD       varchar2(200)
)
/
The interesting bit is contained in just one line ...
   env.put("java.naming.ldap.attributes.binary", "objectGUID");
And this instruction makes clear that the value is to be treated as a binary value - it's specified as \XX\XX\XX and will reach the LDAP server without any conversion. The Oracle database JVM allows any static method to be mapped as a PL/SQL procedure or function. The PL/SQL call specification looks as follows ...
/**
 * Diese Wrapper-Funktion fuer Java gibt fuer in_objectguid (MSAD) den samaccountname zurück.
 * @AUTHOR  AMei, 20120802
 * @PARAM:  in_objectguid
 * @RETURN: VARCHAR2
 * @EXCEPTION: "NO MATCH FOR GIVEN OBJECTGUID"
 * @SEE: XpLdapClientJava, XpLdapClientQuery
 * SELECT get_samaccountname ('\59\34\5d\11\97\95\24\47\28\22\85\12\52\eb\13\f9') AS samaccountname FROM dual;
 */
CREATE OR REPLACE FUNCTION get_samaccountname (
  in_objectguid VARCHAR2
) RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'XpLdapClientQuery.getAccount(java.lang.String) return java.lang.String';
Before testing the new function, we need the necessary network privileges. An ordinary database user is not allowed to perform network operations with Java. The DBA needs to grant the privileges as follows:
begin
  dbms_java.grant_permission(
    grantee           => '[database schema performing the LDAP search operation]',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => '[ldap server name or "*" for the whole network]',
    permission_action => 'connect,resolve'
  );
end;
And now we can perform an ObjectGUID search - even from within the database ...
SELECT get_samaccountname ('\59\34\5d\11\96\95\24\47\88\22\85\01\52\eb\13\f9') AS samaccountname FROM dual;

SAMACCOUNTNAME
--------------------
      Max_Mustermann
And having this account name, we can use "classic" DBMS_LDAP calls to retrieve further details, since this is a VARCHAR2 value. Have fun trying it out.

24. September 2012

JSON generieren: In der Datenbank mit einer SQL-Abfrage

Generating JSON in the database
Mehr und mehr kommt die Anforderung, aus Datenbankinhalten JSON zu generieren. JSON steht für JavaScript Object Notation; kann also von einer JavaScript Engine direkt interpretiert und als Objekt im Hauptspeicher aufgebaut werden. JSON wird gerne als "leichtgewichtiges" Datenaustauschformat verwendet - insbesondere dann, wenn es um moderne Webanwendungen geht und die Daten im Browser verarbeitet werden sollen.
Dieses Blog-Posting zeigt, wie man JSON anhand einer SQL-Abfrage generieren kann. APEX-Entwickler kennen das PL/SQL-Paket APEX_UTIL - darin sind einige Prozeduren zum Generieren von JSON enthalten. Mit denen möchte ich mich hier aber nicht beschäftigen - und das aus folgenden Gründen:
  • Nicht überall ist APEX installiert - und wo es kein APEX gibt, da gibt es auch kein APEX_UTIL.
  • Die JSON-Funktion ist in Form von Prozeduren enthalten; das Ergebnis wird demnach in den HTP Buffer geschrieben (HTP.P). Besser wäre es aber, wenn man das Ergebnis als Rückgabewert einer Funktion und dann als VARCHAR2 oder CLOB erhalten könnte.
  • Und schließlich möchte ich einen Weg vorstellen, mit dem man jede beliebige Objektstruktur generieren kann - und nicht nur einfach das "flache" Äquivalent zu einer SQL-Abfrage.
JSON hat starke Ähnlichkeit zu XML. Und in der Tat wird es teilweise aus den gleichen Gründen verwendet, aus denen man vor 10 Jahren auf XML umgestiegen ist: Sehr flexibel und leicht von Anwendungen konsumierbar. Wie XML bildet auch JSON eine hierarchische Objektstruktur ab - jedes XML kann als Objekt aufgefasst werden und umgekehrt. Na ja - und dann kann man auch jedes XML in ein JSON übersetzen und umgekehrt. Und genau aus diesem Grund werden die XML-Funktionen der Datenbank beim Erzeugen des JSON helfen.
Funktionen zum Erzeugen von XML haben wir in der Datenbank reichlich: Die SQL/XML-Funktionen sind in diesen Blog Postings beschrieben:
Wir nehmen einer der SQL/XML-Abfragen dieses Tutorials als Ausgangspunkt - damit haben wir schon mal das XML, welches nun "nur" noch in ein JSON umgewandelt werden muss. Und das Umwandeln von XML in etwas anderes wird meistens mit XSLT-Stylesheets gemacht. Und so findet man im Internet auch einige fertige Stylesheets; bei code.google.com gleich zwei. Zuerst habe ich xml2json-xslt ausprobiert; das hatte allerdings die Nachteile, dass es XML-Attribute nicht verarbeiten und mit 1:n-Beziehungen, also der Hierarchie nicht richtig umgehen kann. Also habe ich mit xmltojson von Keith Chadwick weitergearbeitet.
Wichtig ist, dass das XML, welches wir in JSON umwandeln wollen, keine leeren Elemente, also keine leeren Tags enthält - andernfalls wird fehlerhaftes JSON generiert. Das kann insbesondere bei SQL NULL Values auftreten - denn wenn die Tabellenspalte, die mit XMLElement() in ein Tag umgewandelt wird, SQL NULL enthält, macht die Funktion ein leeres Tag. Hier sollte man also sicherstellen, dass mit der NVL-Funktion gearbeitet wird. Als Ausgangspunkt dient also diese SQL-Abfrage, welche aus den Tabellen EMP und DEPT ein XML-Dokument generiert. Dieses XML werden wir dann mit dem Stylesheet xmltojson in JSON umwandeln.
select
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement("salary", e.sal),
         xmlelement("commission", nvl(e.comm,0)),
         XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
from scott.dept d
/
Dann laden wir von https://code.google.com/p/xmltojson/ die Dateien xmltojsonv2.xsl und config.xml herunter ...
 Directory of D:\

14.09.2012  11:13             5.898 config.xml
14.09.2012  11:14            20.282 xmltojsonv1.xsl
Dann müssen die Dateien in die Datenbank gebracht werden, damit die datenbankeigene XSLT-Transformation sie benutzen kann. Dabei muss beachtet werden, dass das XSLT-Stylesheet xmltojsonv1.xsl die Datei config.xml referenziert. Damit das auch in der Datenbank einwandfrei funktioniert, brauchen wir das "virtuelle Dateisystem" der XML DB. Dorthin werden wir die Dateien mit FTP laden - und zwar ins Verzeichnis /public. Und dieser Pfad  /public/config.xml muss als absoluter Pfad in die Datei xmltojsonv1.xsl eingetragen werden.
  <!--
  ///////////////////////////////////////////////////////////////////////////////////////////
  Define the configuration document along with configuration parameters
  and switches
  ///////////////////////////////////////////////////////////////////////////////////////////
-->

  <xsl:param name="doc">/public/config.xml</xsl:param>
  <xsl:param name="config" select="document($doc)"/>

  <xsl:param name="encaseObject" select="$config/xmltojson/settings/encase/objectNames" />
Nun ladet Ihr die Dateien per FTP in die Datenbank (damit FTP funktioniert, muss der Protokollserver ggfs. vorher mit DBMS_XDB.SETFTPPORT(2100) freigeschaltet werden. Der Upload geht danach wie folgt:
D:\xml2json>ftp -n
ftp> open sccloud030 2100 
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott **** 
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public 
250 CWD Command successful
ftp> put D:\config.xml 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 5898 bytes sent in 0,00Seconds 5898000,00Kbytes/sec.
ftp> put D:\xmltojsonv1.xsl 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 20282 bytes sent in 0,00Seconds 20282000,00Kbytes/sec.
ftp> bye 
Mit der SQL-Funktion XDBURITYPE bekommt Ihr nun sehr einfach Zugang zum Stylesheet ...
SQL> select xdburitype('/public/xmltojsonv1.xsl').getxml() XSL from dual;

XSL
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<xsl:stylesheet version="1.0" xmlns:xsl="

1 Zeile wurde ausgewählt.
Und damit können wir loslegen. Nehmt einfach die XML-Abfrage von vorhin. Das XML-Dokument, welches sie produziert, muss nun noch "durch" die Funktion XMLTRANSFORM (welche eine Stylesheet-Transformation macht) geleitet werden. Das sieht dann so aus:
select
 xmltransform(
  xmlelement(  
   "department",
   xmlelement("name", d.dname),
   (
    select
     XMLAgg(
      xmlelement(
       "employee",
        XMLAttributes(
         e.empno as "id",
         e.hiredate as "hire-date"
        ),
        xmlelement("name", e.ename),
        xmlelement("salary", e.sal),
        xmlelement("commission", nvl(e.comm,0)),
        XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
      )
     )
    from scott.emp e where e.deptno = d.deptno
   )
  ),
  xdburitype('/public/xmltojsonv1.xsl').getxml()
 ).getclobval() as json
from scott.dept d
/
Das Ergebnis sieht dann etwa so aus ...
JSON
--------------------------------------------------------------------------------
[{"department":{"name":"ACCOUNTING","employee":[{"id":7782,"hire-date":"1981-06-
09","name":"CLARK","salary":2450,"commission":0},{"id":7839,"hire-date":"1981-11
-17","name":"KING","salary":5000,"commission":0},{"id":7934,"hire-date":"1982-01
-23","name":"MILLER","salary":1300,"commission":0}]}}]

:

[{"department":{"name":"SALES","employee":[{"id":7499,"hire-date":"1981-02-20","
name":"ALLEN","salary":1600,"commission":300},{"id":7521,"hire-date":"1981-02-22
","name":"WARD","salary":1250,"commission":500},{"id":7654,"hire-date":"1981-09-
28","name":"MARTIN","salary":1250,"commission":1400},{"id":7698,"hire-date":"198
1-05-01","name":"BLAKE","salary":2850,"commission":0},{"id":7844,"hire-date":"19
81-09-08","name":"TURNER","salary":1500,"commission":0},{"id":7900,"hire-date":"
1981-12-03","name":"JAMES","salary":950,"commission":0}]}}]

[{"department":{"name":"OPERATIONS"}}]
Im Browser könnt Ihr das mit dem "Firebug-Plugin" recht einfach testen. Nehmt das generierte JSON (Zeilenumbrüche entfernen!) und setzt es in folgenden JavaScript-Code ein: window.JSON.parse('<<JSON-CODE hier>>').
Viel Spaß beim Ausprobieren ...
Generating JSON from SQL queries is a requirement which occurs more and more often. JSON is the abbreviation for JavaScript Object Notation; JavaScript engines are able to consume it directly in order to build an in-memory object structure. JSON is therefore very well suited to provide data for AJAX based web applications: the data can be directly consumed by the browsers' javascript engine.
In this blog posting I'll show an approach to generate JSON from SQL queries. Most APEX developers know the PL/SQL package APEX_UTIL - it contains some procedures to generate JSON from the reesults of a SQL query. But I'll not elaborate on these and show another approach - for these reasons:
  • APEX is not installed in every database - some databases don't have APEX, so no APEX_UTIL.
  • APEX_UTIL provides the functionality as procedures - the JSON output is written to the HTP buffer, which is appropriate for APEX applications. But I would like a more generic approach: The JSON should be returned from a PL/SQL function as a CLOB or VARCHAR2.
  • And last, but not least: The JSON returned by the APEX_UTIL procedures is flat - similar to the results of a SQL query - I'd like an approach which is able to generate nested object structures as well.
JSON is very similar to XML - some guys even say that JSON is the more flexible and lightweight alternative to XML (it's interesting that one of reasons for XML - 10 years ago - was that XML is "a lightweight and flexible alternative to ..."). As XML, JSON represents a hierarchical object structure - any object can be expressed as JSON or XML - and any XML or JSON can be interpreted as an programming languages' object. And that fact allows us to use the XML functionality of Oracle (the SQL/XML functions) in order to generate JSON. I have described the SQL/XML functions in another blog posting some time ago ...
We'll use one of the SQL/XML queries in that tutorial as a starting point for JSON generation. The SQL/XML query constructs the data as a nested XML document. When it's about converting XML to something different (HTML, TEXT, etc.), we can use XSLT-Stylesheets for that. So we need an XSLT Stylesheet, which converts XML to JSON. After googling a bit around, I found two implemetations on code.google.com. I started with xml2json-xslt, but this one had some severe disadvantages: It was not able to process XML attributes and a 1:n hierarchy in the XML (like DEPT -> EMP) led to errornous JSON. So I continued my work with xmltojson from Keith Chadwick.
The second stylesheet also has a limitation: It cannot work with empty XML tags. So our SQL/XML query must ensure, that the resulting XML document has no empty tag. Empty tags can occur, when the XMLElement() function is being used with a SQL NULL value - it then generates an empty tag. So we need to have a look at the SQL query and add some NVL() functions in those cases where a column value might be SQL NULL. This results in the following query, based on EMP and DEPT. This query produces XML output which we will then convert to JSON.
select
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement("salary", e.sal),
         xmlelement("commission", nvl(e.comm,0)),
         XMLComment('This XML document is generated as of '||to_char(sysdate, 'DD/MM/YYYY HH24:MI'))
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
from scott.dept d
/
Now download the files xmltojsonv2.xsl and config.xml from https://code.google.com/p/xmltojson/.
 Directory of D:\

14.09.2012  11:13             5.898 config.xml
14.09.2012  11:14            20.282 xmltojsonv1.xsl
Next, both files must be brought into the database. And this very XSLT stylesheet has a specific "feature": It stores its configuration in the second file: config.xml. So the Stylesheet must be able to access the config.xml - also in the database. For that reason we cannot just store the XSLT in an ordinary table, we need to use the "virtual file system" of the XML DB Repository. We'll store them in the "folder" public - so we will then have two paths for two files: /public/xmltojsonv1.xml and /public/config.xml. Since the database does not support relative paths here, we now make sure that the path to config.xml within the stylesheet is absolute. Open xmltojsonv1.xsl with the editor of your choice and change the highlighted entry from config.xml to /public/config.xml.
  <!--
  ///////////////////////////////////////////////////////////////////////////////////////////
  Define the configuration document along with configuration parameters
  and switches
  ///////////////////////////////////////////////////////////////////////////////////////////
-->

  <xsl:param name="doc">/public/config.xml</xsl:param>
  <xsl:param name="config" select="document($doc)"/>

  <xsl:param name="encaseObject" select="$config/xmltojson/settings/encase/objectNames" />
Having done that we can bring the files into the database. This can be achieved using the package DBMS_XDB, another (and simpler) approach is to use the database-embedded FTP Server. This allows us to upload the files directly to the XML DB Repository. If your FTP Server does not work, activate it by executing DBMS_XDB.SETFTPPORT(2100) (with DBA privileges). Then you can upload the files as follows ...
D:\xml2json>ftp -n
ftp> open sccloud030 2100 
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott **** 
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public 
250 CWD Command successful
ftp> put D:\config.xml 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 5898 bytes sent in 0,00Seconds 5898000,00Kbytes/sec.
ftp> put D:\xmltojsonv1.xsl 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 20282 bytes sent in 0,00Seconds 20282000,00Kbytes/sec.
ftp> bye 
Now let's do a quick test. Try to access the stylesheet using the function XDBURITYPE:
SQL> select xdburitype('/public/xmltojsonv1.xsl').getxml() XSL from dual;

XSL
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<xsl:stylesheet version="1.0" xmlns:xsl="

1 row selected.
Now we can do the converion of our XML to JSON. Just take the above SQL query and add the XMLTRANSFORM function. XMLTRANSFORM performs a stylesheet transformation - in the database. The query should look like this:
select
 xmltransform(
  xmlelement(  
   "department",
   xmlelement("name", d.dname),
   (
    select
     XMLAgg(
      xmlelement(
       "employee",
        XMLAttributes(
         e.empno as "id",
         e.hiredate as "hire-date"
        ),
        xmlelement("name", e.ename),
        xmlelement("salary", e.sal),
        xmlelement("commission", nvl(e.comm,0)),
         XMLComment('This XML document is generated as of '||to_char(sysdate, 'DD/MM/YYYY HH24:MI'))
      )
     )
    from scott.emp e where e.deptno = d.deptno
   )
  ),
  xdburitype('/public/xmltojsonv1.xsl').getxml()
 ).getclobval() as json
from scott.dept d
/
And the result looks like this:
JSON
--------------------------------------------------------------------------------
[{"department":{"name":"ACCOUNTING","employee":[{"id":7782,"hire-date":"1981-06-
09","name":"CLARK","salary":2450,"commission":0},{"id":7839,"hire-date":"1981-11
-17","name":"KING","salary":5000,"commission":0},{"id":7934,"hire-date":"1982-01
-23","name":"MILLER","salary":1300,"commission":0}]}}]

:

[{"department":{"name":"SALES","employee":[{"id":7499,"hire-date":"1981-02-20","
name":"ALLEN","salary":1600,"commission":300},{"id":7521,"hire-date":"1981-02-22
","name":"WARD","salary":1250,"commission":500},{"id":7654,"hire-date":"1981-09-
28","name":"MARTIN","salary":1250,"commission":1400},{"id":7698,"hire-date":"198
1-05-01","name":"BLAKE","salary":2850,"commission":0},{"id":7844,"hire-date":"19
81-09-08","name":"TURNER","salary":1500,"commission":0},{"id":7900,"hire-date":"
1981-12-03","name":"JAMES","salary":950,"commission":0}]}}]

[{"department":{"name":"OPERATIONS"}}]
To test the JSON, just open the Firefox, Chrome or Safari browser. The following screenshot was taken from Firefox with the Firebug plugin. I opened the console and executed the following JavaScript-Code window.JSON.parse('<<YOUR JSON-CODE GOES HERE>>)'. When you copy and paste the JSON from SQL*Plus, make sure that you remove the line breaks.
Have fun!

5. September 2012

DELETE und UPDATE: Welche(!) Zeilen waren betroffen?

DELETE and UPDATE: Which(!) rows were affected?
In diesem Blog Posting stelle ich die RETURNING-Klausel für SQL DML-Anweisungen nochmals vor. Für SQL INSERT Kommandos hat die sicherlich fast jeder schonmal gesehen (dennoch erkläre ich es kurz) und danach zeige ich, wie man mit der RETURNING-Klausel feststellen kann, welche Zeilen von einem DELETE oder UPDATE erfasst wurden.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Nun stellt sich die Frage, wie man an die während dem INSERT generierte ID wieder herankommt. Nachträgliches Abfragen funktioniert nicht, da der einzige, eindeutige Schlüssel (eben die ID) noch nicht bekannt ist. Eine Variante wäre ein ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
Aber das bedeutet das Absetzen einer neuen SQL-Abfrage. Viel eleganter wäre es doch, wenn man die ID direkt beim INSERT zurückbekommen könnte - und das geht mit der RETURNING-Klausel ganz einfach.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
Weniger bekannt ist, dass man RETURNING auch bei UPDATE und DELETE-Kommandos einsetzen kann. Da hier auch mal mehr als eine Zeile zurückkommen kann, muss man aber noch mit der BULK COLLECT-Klausel arbeiten. Ein Beispiel für DELETE.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
Die Ausgabe ist dann wie folgt:
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL-Prozedur erfolgreich abgeschlossen.
Auch bei einem Update kann die RETURNING-Klausel eingesetzt werden, dabei werden aber stets die neuen Werte zurückgegeben, nicht die alten. Aber die Primärschlüsselspalten dürften ohnehin die interessanteren sein. Hier also das Beispiel für ein UPDATE mit RETURNING-Klausel.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
Ich arbeite nun seit mehr als 10 Jahren mit Oracle - und RETURNING nutze ich regelmäßig. Aber auf die Kombination mit DELETE oder UPDATE wurde ich erst vor zwei Wochen aufmerksam.
In this blog posting I'll introduce the RETURNING clause for SQL DML commands. Some of you might have seen a RETURNING clause as part of a SQL INSERT statement. I'll show this as well but the more interesting bit is an UPDATE or DELETE command with a RETURNING clause, since this allows you to retrieve the affected rows. Let's start with a typical, simple example with a SQL INSERT and a sequence.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Now, the question is: How can we get the ID number generated by the sequence. We cannot select the table since we don't know the unique ID - and all other columns might be non-unique. A working approach is ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
But this required another SQL Query - it would be much more elegant to get the generated number directly from the SQL INSERT statement. And the returning clause makes it easy.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
You can use the RETURNING clause for DELETE statements as well. But since these typically affect more than one row, you'll also need the BULK COLLECT clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
This PL/SQL block produces the following output.
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL procedure successfully completed.
RETURNING does also work for UPDATE statements. In this case the new column values will be returned, not the "old" ones. But in practice, RETURNING will most often be used the get the non-changing primary key values back. Here is the example for a SQL UPDATE statement with a RETURNING clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
I'm working with Oracle for more than 10 years now and I frequently work with RETURNING. But I got aware of the combination with DELETE about three weeks ago.

Beliebte Postings