Oracle Packages Teil VI: DBMS_LOB

Was sind LOBs?

Die Ablage von unstrukturierbaren Daten wie Text, Video, Bildern oder Sound kann in Oracle in LOB-Datentypen (Large Objects) vorgenommen werden. Hierbei kann es sich sowohl um binäre als auch um charakter-basierte Daten handeln. Diese Daten können in der Datenbank gehalten werden (BLOB, CLOB und NCLOB) oder auch im Filesystem (BFILE).

Bei den BFILE-Datentypen werden Dateipfad und -name in der Datenbank abgelegt. Bei allen Datentypen können bis 4 GB Daten gespeichert werden. Leider können LOBs nicht mittels SQL bearbeitet werden.

Es gibt aber das Package „DBMS_LOB“, das den Zugriff und die Verwaltung von LOBs unterstützt. Dieses Package wird im Folgenden vorgestellt. Die Suche innerhalb von LOBs wird von der InterMedia Option unterstützt. Darauf geht dieser Artikel jedoch nicht näher ein.

Eine Einschränkung gibt es bei der Nutzung des Package: DBMS_LOB kann nur von der Server-Seite genutzt werden, nicht vom Client aus.

Wie bekommt man die Daten in die Datenbank?

In den meisten Fällen liegen die Daten anhand von Dateien im Filesystem vor. Hierzu ist die Betrachtung des BFILE-Datentyps nützlich.

Der BFILE-Datentyp wird benutzt, um „large binary objects“ bis zu einer Größe von vier Gigabyte außerhalb der Datenbank zu halten. Zur Verwaltung benutzt man einen Locator. Dieser zeigt auf einen Directory-Alias und einen Dateinamen. Dazu sind die folgenden Voraussetzungen notwendig:

• Der DBA muss dem User das Recht geben, Directory-Aliasnamen anzulegen z. B. über „grant create any directory to lobuser;“.
• Der User kann dann mit folgendem Befehl ein Directory-Alias anlegen: CREATE DIRECTORY lobfiles AS ‘c:\bk\daten’.
• Dies kann über den Befehl „select directory_name, directory_path from all_directories;“ kontrolliert werden.
• Die Rechte zum Lesen in diesem Directory können an andere User vergeben werden: „grant read on directory lobfiles to ck“.

Die einzulesenden Daten sollen in eine Tabelle mit entsprechender Struktur eingeladen werden.

Beispiel:

create table lob_documents (
doc_id		NUMBER(10,0) PRIMARY KEY
docname 	VARCHAR2(200),
doc 		CLOB,
timestamp 	DATE);

CREATE OR REPLACE PROCEDURE lobfile_einladen
(dir varchar2,name varchar2:=NULL)
is
dasbfile 		BFILE;
dasclob  		CLOB;
docname 		VARCHAR2(200):=nvl(name,’standard.txt’);
b_existiert_datei	BOOLEAN;
BEGIN
  INSERT INTO lob_documents(doc_id, docname, doc, timestamp)
  VALUES
  -- empty_clob/empty_blob initialisieren die LOB-Spalte
  (seq_lob.nextval, docname,empty_clob(),sysdate)
  -- Die Returning-Klausel erwartet einen gueltigen Locator
  returning doc into dasclob;

  -- BFILE-Locator wird auf eine Datei im Filesystem gesetzt
  dasbfile:=bfilename(dir,name);
  b_existiert_datei:= DBMS_LOB.FILEEXISTS(dasbfile)=1;
  IF b_existiert_datei
  THEN
-- die Datei wird zum Lesen geöffnet
     DBMS_LOB.FILEOPEN(dasbfile);
-- die Datei wird in dasclob gelesen,
     DBMS_LOB.LOADFROMFILE(dest_lob=>dasclob,src_lob=>dasbfile,
-- getlength ermittelt die Laenge der Datei
     amount=>DBMS_LOB.GETLENGTH(dasbfile));
  IF DBMS_LOB.FILEISOPEN(dasbfile)=1 -- Funktion liefert kein BOOLEAN!
     THEN
        DBMS_LOB.FILECLOSE(dasbfile);
     END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
-- Falls noch irgendein Locator offen ist, so wird er jetzt geschlossen
     DBMS_LOB.FILECLOSEALL;
END;
/

ORDIX auf der CeBIT
vom 13.-20.03.2002 in Hannover

Besuchen Sie uns auf dem "Oracle
Partner Stand der DOAG" in Halle 4.
	

Aufruf: execute lobfile_einladen(‘LOBFILES‘,‘cebit.txt‘); Die beigefügte Datei cebit.txt, Abb. 2, ist eine Text-Datei. Bilder wären gegebenenfalls über zusätzliche Spalten analog in die Datenbank abzulegen.

Wie werden zwei LOBs miteinander verglichen?

Für den Vergleich von zwei LOB-Feldern mit demselben Typ dient der Aufruf von dbms_lob.compare. Im folgenden Bespiel wird überprüft, ob ein Dokument bereits in der Tabelle lob_document enthalten ist.

CREATE OR REPLACE FUNCTION mein_vergleich(p_lob CLOB) RETURN BOOLEAN
IS
  CURSOR c_clob IS
  SELECT * FROM lob_documents;
BEGIN
  FOR pc IN c_clob LOOP
    IF DBMS_LOB.COMPARE(p_lob, pc.doc)=0
    THEN RETURN TRUE; -- Dokument wurde gefunden
    END IF;
  END LOOP;
  RETURN FALSE;
END;
/

Wie findet man einen Suchbegriff innerhalb eines Dokuments und wie wird eine Information (z. B. das folgende Wort) dazu geholt?

Anhand der nächsten Funktion wird gezeigt, wie in einem Dokument ein Begriff gesucht werden kann und das folgende Wort ausgegeben werden kann.

CREATE OR REPLACE FUNCTION finde_und_hole_text
	(p_lob CLOB,
	p_suche VARCHAR2,
	p_nte INTEGER DEFAULT 1)

RETURN VARCHAR2
IS
  v_position      INTEGER;
  v_position_bis  INTEGER;
  v_wort          VARCHAR2(200);
BEGIN
 v_position:=DBMS_LOB.INSTR(p_lob, p_suche, 1, p_nte);
 IF  v_position> 0 -- gefunden
 THEN
  v_position:=DBMS_LOB.INSTR(p_lob, ‘ ‘, v_position);
  -- Ende Suchbegriff, nächstes Blank
  v_position_bis:=DBMS_LOB.INSTR(p_lob, ‘ ‘, v_position+1);
  --Ende nächstes Wort durch nächstes Blank
 IF v_position > 0 and v_position_bis>v_position
 THEN
 v_wort:=DBMS_LOB.SUBSTR(p_lob, v_position_bis-v_position,v_position);
   RETURN v_wort;
   ELSE RETURN NULL;
   END IF;
  ELSE RETURN NULL;
  END IF;
  RETURN NULL;
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END;
/

DECLARE
  CURSOR c_clob IS
  SELECT * FROM lob_documents;
BEGIN
FOR pc IN c_clob LOOP   DBMS_OUTPUT.PUT_LINE(NVL
  (finde_und_hole_text(pc.doc,‘Referent‘,1),‘Nichts gefunden‘));
  END LOOP;
END;
/	

Wie fügt man zu einem LOB etwas hinzu bzw. ändert einen Text?

Die Prozedur DBMS_LOB.WRITE ermöglicht das Schreiben von Daten (sowohl binär als auch charakter-basiert) in ein LOB.

DECLARE
	CURSOR c_clob IS
	SELECT * FROM lob_documents
	FOR UPDATE;

BEGIN
FOR pc IN c_clob LOOP
	DBMS_LOB.WRITE(pc.doc,
    	length(‘COPYRIGHT ORDIX AG ‘|| SYSDATE),
     DBMS_LOB.GETLENGTH(pc.doc), ‘ ORDIX AG ‘||  SYSDATE);
END LOOP;
END;
/

Sollen die Inhalte zweier LOBs konkateniert werden, so dient dazu die Prozedur DBMS_LOB.APPEND. Zur Benutzung der weiteren Funktionalitäten dieses Package wird auf die Dokumentation verwiesen.

Ausblick

In der nächsten ORDIX News Ausgabe untersuchen wir das Package DBMS_DEBUG.

Beate Künneke (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen