11. März 2008

Neue Version verfügbar: Betriebssystem-Kommandos mit SQL ausführen

New version available: Executing OS commands from the database

Seit der initialen Version ist nun einige Zeit vergangen - inzwischen ist die neue Version des PL/SQL-Packages zum Aufruf von Betriebssystem-Kommandos verfügbar. Ich habe die Anregung von Dietmar Aust aufgegriffen und Code zum Umgang mit Dateien und Verzeichnissen hinzugefügt. Außerdem habe ich gründlich reorganisiert, so dass nun mehrere PL/SQL-Objekte zur Verfügung stehen:
  • OS_COMMAND: zum Aufrufen von Betriebssystem-Kommandos; STDIN oder STDOUT können in die SQL-Ebene zurückübertragen und dort weiterverarbeitet werden
  • FILE_TYPE: (Objekttyp!) Repräsentiert einen File-Handle; damit können Dateien erstellt, umbenannt, kopiert ausgelesen oder geschrieben werden
  • FILE_PKG: Stellt die File-Handles (FILE_TYPE) bereit. File-Handles können einzeln, als einfaches oder rekursives Directory-Listing (virtuelle Tabelle!) abgerufen werden.
  • LOB_WRITER_PLSQL: das ist "nebenbei" entstanden; mit einen PL/SQL-Mitteln (ohne Java) können LOBs ins Dateisystem geschrieben werden
Was man mit den Packages machen kann (und wie einfach das dann ist), illustriert das folgende Beispielskript - es packt ein ZIP-Archiv in ein temporäres Verzeichnis aus und lädt alle entstandenen Dateien mit einem einzigen INSERT-Kommando in eine Tabelle:
Some time passed by since the initial version of the PL/SQL package OS_COMMAND - now the new version (0.5) is available. Based on the Dietmar Aust's comment I added some code to deal with files and folders (based on java.io.File). Beyond this the code is reorganized and now distributed over 4 PL/SQL objects:
  • OS_COMMAND: to execute shell commands from the SQL layer. STDIN or STDOUT contents can be passed between Java and the SQL layer
  • FILE_TYPE: (object type!) represents a file handle; allows to create, move, copy, write to and read from files
  • FILE_PKG: package to get a file handle - this might be a single file handle or multiple ones from a simple or recursive directory listing (the latter two are returned as a virtual table)
  • LOB_WRITER_PLSQL: this was created by the way; it allows to write a LOB to a file with pure PL/SQL - no java involved.
The following SQL script demonstrates the possibilities (and the simplicity) - it extracts a zip file into a temporary folder, loads all files into a database table (with one single INSERT command) and finally deletes the temporary directory.
set verify off

drop table document_table
/

drop sequence seq_documents
/

create table document_table(
  id number(10),
  file_path varchar2(4000),
  file_name varchar2(4000),
  document clob
)
/

create sequence seq_documents 
/


accept ZIPFILE default '/home/oracle/files.zip' prompt '>> Contents of which ZIP file to be loaded [/home/oracle/files.zip] '

declare
  f  file_type;
  fz file_type;

  r  number;
begin
  -- get a handle for the "tmp" directory
  f:=file_pkg.get_file('/tmp');

  -- create a new temporary directory where the zip archive is being
  -- extracted into ... make the filename unique using TIMESTAMP
  fz := f.create_dir(
    'zipdir_temp_'||user||'_'||to_char(systimestamp, 'YYYYMMDD_HH24MISS.SSSS')
  );

  -- DOIT: 
  -- extract the zipfile; the -qq switch is very important here - otherwise
  -- the OS process will not come back
  r := os_command.exec('unzip -o -qq &ZIPFILE. -d '||fz.file_path);

  -- if the result is 0 (=success) load the contents of the temporary directory
  -- (recursively) with ONE (!) SQL INSERT command
  if r = 0 then 
    insert into document_table (
      select 
        seq_documents.nextval id,
        e.file_path,
        e.file_name,
        file_pkg.get_file(e.file_path).get_content_as_clob('iso-8859-1') content 
      from table(file_pkg.get_recursive_file_list(fz)) e
    ); 
  end if;
  
  -- finally delete the temporary directory and its contents
  fz := fz.delete_recursive();
end;
/
sho err
Es fehlt noch die ausführliche Dokumentation der Packages - diese folgt nach; den Code wollte ich möglichst frühzeitig bereitstellen. Probiert es mal aus - An Feedback bin ich natürlich stets interessiert ...
A comprehensive documentation is missing at the moment - I wanted to release the code early. Just give it a try - Feedback is very appreciated.

Kommentare:

daust_de hat gesagt…

Hallo Carsten,

wie hast Du die Dokumentation der Packages erzeugt, mit pldoc?

Grüße,
~Dietmar.

Carsten Czarski hat gesagt…

Hi Dietmar,

im Prinzip ja - aber da PL/DOC keine Objekttypen kann, musste ich aus dem Code des TYPE BODY zunächst ein Package machen ... erschien mir weniger Aufwand als die ganzen HTML-Seiten selbst zu bauen ...

Viele Grüße

-Carsten

daust_de hat gesagt…

Hallo Carsten,

ja, gefällt mir schon gut. Jedoch ist das mit den Objekttypen eine echte Limitierung.

Im Projekt werde ich es wohl doch nicht einsetzen, da es ja auch nur die APIs beschreibt und nicht den Rest des Datenmodells.

Wir experimentieren im aktuellen Projekt mit OraSchemDoc:
http://oraschemadoc.yarpen.cz/
Dann können wir eher von Hand noch die Applikation erweitern um die Funktionalität der API.

Wird auch noch weiter entwickelt. PL/Doc wurde 2004 eingestellt, glaube ich.

PL/Doc schien mir nicht leicht zu erweitern sein, die scheinen eine Java-ähnliche Metasprache zu verwenden.

Viele Grüße,
~Dietmar.

Beliebte Postings