Skip to content
 

External Tables in Oracle – und der “ls” Befehl

In diesem Blogeintrag beschreibt Ulrike Schwinn von Oracle sehr gut, wie man External Tables benutzt – und vor allem das neue 11g-Feature. Damit ist es möglich, vor dem Lesen der Datei Scripte auszuführen, um z.B. die Datei erst einmal zu entpacken (oder auch per scp/ftp irgendwo abzuholen).

Aber damit kann wesentlich mehr erreicht werden. Unter anderem ist dies eine Möglichkeit, Verzeichnisinhalte auszulesen. Bis jetzt war das eine der Aufgaben, die rein mit PL/SQL nicht gelöst werden konnten – man musste auf Java Stored Procedures ausweichen.

Entwickeln wir das Beispiel von Frau Schwinn noch etwas weiter.

Ich habe also zwei Directory-Objekte, data_dir (in dem meine Files liegen) und exec_dir (in dem meine Scripte liegen).

Im Verzeichnis exec_dir lege ich jetzt ein neues Script mit dem Namen ls.sh mit folgendem Inhalt an:

#!/bin/bash
/bin/ls -1 $1

Ausserdem eine neue externe Tabelle:

CREATE TABLE test_ls
(line VARCHAR2(80))
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS 
(
  RECORDS DELIMITED BY NEWLINE 
  PREPROCESSOR exec_dir:'ls.sh'
  FIELDS TERMINATED BY "|"
)
LOCATION ('prodDelta.gz')
)
REJECT LIMIT UNLIMITED NOPARALLEL;

Was ergibt nun eine Abfrage auf diese Tabelle?

SQL> SELECT * FROM test_ls;
 
LINE
-----------------------------------------------------------------
/u00/app/oracle/demo/external_tables/data/prodDelta.gz

Da der Filename als Parameter an das Script übergeben wird, wird genau diese Datei angezeigt. Übrigens, diese Datei muss vorhanden sein, auch wenn überhaupt nichts mit ihr gemacht wird.

In dem Script können natürlich beliebige Befehle stehen, alles, was auf Standard Output ausgegeben wird, wird als Tabelleninhalt angezeigt.

Ein typisches Szenario ist, dass Dateien in die Datenbank geladen werden müssen, der Dateiname aber nicht bekannt ist, da er z.B. einen Timestamp beinhaltet.
Das Beispiel noch ein kleines bisschen weiter entwickelt löst diese Aufgabe.

Wir ändern unser Scipt ls.sh ab in:

#!/bin/bash
/bin/ls -ls /tmp/

und führen den Select erneut aus:

SQL> select * from test_ls;

LINE
--------------------------------------------------------------------------------
total 16
4 drwxr-xr-x 2 oracle  dba     4096 2009-12-04 16:29 hsperfdata_oracle
4 drwxr-xr-x 2 sntrsrv sntrsrv 4096 2009-12-04 16:10 hsperfdata_sntrsrv
4 drwxrwxrwt 2 root    root    4096 2009-12-04 16:10 VMwareDnD
4 drwx------ 2 root    root    4096 2009-12-04 16:10 vmware-root

und es werden alle Dateien des tmp-Verzeichnisses angezeigt. Natürlich könnte man Name, Grösse, Datum, … noch in einzelne Felder legen.

Ich finde dies eine sehr gute Möglichkeit, Betriebssystem-Befehle in Oracle zu integrieren. Über die Risiken spreche ich nächste Woche in meinem Vortrag auf dem DOAG Regionaltreffen Südbayern/München – und werde danach die Folien hier zur Verfügung stellen.

5 Comments

  1. […] « External Table in Oracle – und der “ls” Befehl […]

  2. […] Tables und Database Vault Ein kleiner Nachtrag zu meinem Eintrag über External Tables: Die Ausführung von Scripten (also die Option PREPROCESSOR) funktioniert nicht mit Oracle […]

  3. Sven Vetter says:

    Eine Ergänzung dazu:

    Dies funktioniert auch mit Oracle 11.1.0.7.
    Einzig die Scripte müssen angepasst werden, so dass es “ordentliche” Unix-Scripts sind – also die Shebang-Zeile (z.B. #!/bin/bash) muss vorhanden sein.

    Ich habe dies im obigen Beispiel angepasst.

  4. […] dritter Teil meiner kleinen Blogreihe über das neue External Table Feature (Scriptausführung per PREPROCESSOR) noch ein paar Worte über […]

  5. […] Tables, Scripts und Security-Risiken Als dritter Teil meiner kleinen Blogreihe über das neue External Table Feature (Scriptausführung per PREPROCESSOR) noch ein paar Worte über Security ;-) Was ist hierbei […]

Leave a Reply