Import von Bild-Dateien in die Datenbank

02.
November
2009
Veröffentlicht von: Elke Fritsch

Jeder hat schon mal von den LOB-Datentypen gehört, die für die Speicherung von großen Textdateien bzw. binären Dateien geeignet sind. In der Oracle Datenbankversion 10g können sie Daten bis zu 128 Terabyte pro Feld aufnehmen ((4GB-1) * DB-Blockgröße).
Das Einfügen von Bildern in Tabellen ist allerdings nicht über einfachen Insert möglich, sondern nur über PL/SQL-Prozeduren und das Package DBMS_LOB.

Jeder hat schon mal von den LOB-Datentypen gehört, die für die Speicherung von großen Textdateien bzw. binären Dateien geeignet sind. In der Oracle Datenbankversion 10g können sie Daten bis zu 128 Terabyte pro Feld aufnehmen ((4GB-1) * DB-Blockgröße).
Das Einfügen von Bildern in Tabellen ist allerdings nicht über einfachen Insert möglich, sondern nur über PL/SQL-Prozeduren und das Package DBMS_LOB.
Will man ein Bild in der DB nicht nur speichern, sondern auch seine Attribute (Höhe, Breite, Farbtiefe etc.) auslesen oder diverse Manipulationen durchführen wie z.B. Vergrößern, Rotieren etc., bietet sich der Datentyp ORDimage an (den man allerdings nicht in der Oracle Express-Edition verwenden kann, da die Intermedia-Komponente hier fehlt).
Dieser Monatstip soll eine kurze Einführung geben, wie man einzelne Bilder bzw. alle Bilder eines externen Verzeichnisses in Datenbanktabellen laden kann.

Zur Vorbereitung muss man erstmal ein Verzeichnis auf dem Server erstellen und dem Benutzer die nötigen Schreib- und Leserechte erteilen (Das Schreibrecht wird nur für die Erstellung der externen Tabelle benötigt.):

CREATE OR REPLACE DIRECTORY bilder AS 'C:\temp';
GRANT READ, WRITE ON DIRECTORY bilder TO scott;

 

Einzelne Bilder in bestehenden Datensätzen ergänzen

Als Beispiel dient die beliebte Tabelle scott.emp. Wir hängen zunächst eine Spalte "Bild" mit dem Datentyp BLOB an die Tabelle an:

CONN scott/tiger
ALTER TABLE emp add(bild BLOB);

Dann erzeugen wir eine Prozedur zum Einfügen einzelner Bilder. Übergeben werden der Primärschlüssel des Datensatzes und der Name des Bildes. Die Bilder müssen im Ordner c:\temp liegen und die Bildernamen sollten keine Umlaute enthalten:

CREATE OR REPLACE PROCEDURE bild_einfuegen (
  p_empno   NUMBER,
  p_bild    VARCHAR2)
AS
  v_quelle    BFILE;
  v_ziel      BLOB;
BEGIN
-- das BLOB-Feld wird erstmal als leerer Blob initialisiert, der in die
-- Variable v_ziel zurückgeschrieben wird
   UPDATE emp SET bild = empty_blob() WHERE empno = p_empno
   RETURNING bild INTO v_ziel;
-- über die Funktion bfilename werden der Speicherort und der
-- Name des Bilds in die Variable v_quelle eingelesen
  v_quelle := bfilename('BILDER', p_bild);
-- das Bfile wird mit der Prozedur DBMS_LOB.FILEOPEN zum Lesen geöffnet
  DBMS_LOB.FILEOPEN(v_quelle, DBMS_LOB.FILE_READONLY);
-- über die Prozedur LOADFROMFILE wird das Bfile in das Blob-Feld gelesen
-- GETLENGTH ermittelt die Zahl der zu importierenden Zeichen
  DBMS_LOB.LOADFROMFILE(v_ziel, v_quelle, DBMS_LOB.GETLENGTH(v_quelle));
-- Die Ausgabe der Grösse des Bilds dient als Test,
-- ob der Import geklappt hat
  DBMS_OUTPUT.PUT_LINE('Grösse: '||DBMS_LOB.GETLENGTH(v_ziel));
  DBMS_LOB.FILECLOSE(v_quelle);
  COMMIT;
END;
/

-- Testen mit
 exec bild_einfuegen(7839, '0087-Strand.jpg')

 

Alle Bilder aus einem Verzeichnis in eine Tabelle laden

DROP TABLE bilder_hawaii;
CREATE TABLE bilder_hawaii(
bild_nr   NUMBER,
bild_name VARCHAR2(100),
bild   BLOB,
CONSTRAINT bilder_pk PRIMARY KEY(bild_nr));

Zuerst schieben wir die Photos in das Bilder-Directory c:\temp und erzeugen dann über die Eingabeaufforderung mit dir /B eine Liste der Filenamen, die in das Bilderverzeichnis zurück kopiert wird:

c:\temp> dir /B > c:\liste.txt
c:\temp> copy c:\liste.txt c:\temp\liste.txt


Diese Liste kann man in das Bilder-Verzeichnis als externe Tabelle einbinden:

DROP TABLE filenamen;
CREATE TABLE filenamen (name VARCHAR2(100))
ORGANIZATION EXTERNAL
(DEFAULT DIRECTORY bilder
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE)
LOCATION('liste.txt'));

SELECT * FROM filenamen;

NAME
--------------------------------
0003-Waikiki.jpg
0004-Waikiki-Strand.jpg
0006-Honululu-Tempel.jpg
0007b-Honululu-Baum.jpg
0008-Honululu-Blume.jpg
0009-Honululu-Foster Gardens.jpg
0012-Honululu-Foster Gardens.jpg
....
667 Zeilen ausgewählt.


Danach läßt sich der Lade-Vorgang über eine Schleife abwickeln. Im Beispiel wurde zusätzlich eine Geschwindigkeitsmessung eingebaut:

DROP SEQUENCE bildseq;
CREATE SEQUENCE bildseq;

CREATE OR REPLACE PROCEDURE bilder_einfuegen
AS
  CURSOR bildcur IS SELECT name FROM filenamen;
  v_quelle    BFILE;
  v_ziel      BLOB;
  v_start     TIMESTAMP;
  v_ende      TIMESTAMP;
  v_count     NUMBER := 0;
  v_groesse   NUMBER := 0;
BEGIN
  SELECT SYSTIMESTAMP INTO v_start FROM dual;
-- Der Cursor liefert die Namen der Bilder
  FOR rec IN bildcur LOOP
     INSERT INTO bilder_hawaii 
     VALUES(bildseq.nextval, rec.name, empty_blob())
     RETURNING bild INTO v_ziel;
     v_quelle := bfilename('BILDER', rec.name);
     DBMS_LOB.FILEOPEN(v_quelle, DBMS_LOB.FILE_READONLY);
     DBMS_LOB.LOADFROMFILE(v_ziel, v_quelle, DBMS_LOB.GETLENGTH(v_quelle));
     DBMS_LOB.FILECLOSE(v_quelle);
     v_count := bildcur%rowcount;
     v_groesse := v_groesse + DBMS_LOB.GETLENGTH(v_ziel);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE
 ('Es wurden '||v_count||' Bilder eingefügt. Gesamtgrösse: '||v_groesse);
  SELECT SYSTIMESTAMP INTO v_ende FROM dual;
  DBMS_OUTPUT.PUT_LINE('Laufzeit: '||
   REGEXP_SUBSTR((v_ende-v_start), '[^ ]+',1,2));
  COMMIT;
END;
/

--Testen mit
exec bilder_einfuegen

Es wurden 667 Bilder eingefügt. Gesamtgrösse: 413089359
Laufzeit: 00:01:38.516000000 (bei einer XE-Edition)
Laufzeit: 00:00:51.406000000 (bei einer 11g-Version auf demselben Rechner)

 

Einfügen und Bearbeiten von Bildern mit Oracle Intermedia

Oracle interMedia (ab 11g Oracle Multimedia genannt) ermöglicht neben Speichern und Abrufen auch die Manipulation von Bildern, Audiofiles und Videos in der Datenbank. Diese Komponente wird außer in der XE-Edition per default mitinstalliert.
Der Datentyp ORDimage hat gegenüber den einfachen Blobs den Vorteil der direkten Integration in die Entwicklungstools von Oracle (z. B. JDeveloper, Oracle Content Management SDK, Oracle Application Server Portal, etc.). Darüber hinaus werden Bildinformationen wie Höhe, Breite, Format, Mime-Typ etc. beim Hochladen automatisch bestimmt und gespeichert.

 

Einfügen einzelner Bilder

Zur Nutzung der Intermedia-Optionen muss man das oben beschriebene Prozedere ein bisschen abwandeln. Die Returning-Klausel kann hier nicht verwendet werden, insofern wird die Initialisierung der Bildspalte ausgelagert:

ALTER TABLE emp DROP COLUMN bild;
ALTER TABLE emp add(bild ORDSYS.ORDImage);
UPDATE emp SET bild = ORDSYS.ORDImage.init();
commit;

CREATE OR REPLACE PROCEDURE bild_einfuegen1 (
  p_empno   NUMBER,
  p_bild    VARCHAR2)
AS
  v_ziel     ORDSYS.ORDImage;
  v_context  RAW(400) := NULL;
BEGIN
   SELECT bild INTO v_ziel FROM emp WHERE empno = p_empno FOR UPDATE;
-- mit der Methode importFrom kann man das Bild hochladen
   v_ziel.importFrom(v_context, 'file', 'BILDER', p_bild);
-- Alternativ kann man auch erst die Quelle festlegen und dann
-- das File importieren
-- v_ziel.setSource('file', 'BILDER', p_bild);
-- v_ziel.import(v_context);
  UPDATE emp set bild = v_ziel WHERE empno = p_empno;
  COMMIT;
END;

--Testen mit
exec bild_einfuegen1 (7521,'0006-Honululu-Tempel.jpg')

 

Einfügen aller Bilder aus einem Verzeichnis

DROP TABLE bilder_hawaii;
CREATE TABLE bilder_hawaii(
bild_nr   NUMBER,
bild_name VARCHAR2(100),
bild   ORDSYS.ORDImage,
CONSTRAINT bilder_pk PRIMARY KEY(bild_nr));

DROP SEQUENCE bildseq;
CREATE SEQUENCE bildseq;

CREATE OR REPLACE PROCEDURE bilder_einfuegen1
AS
  CURSOR bildcur IS SELECT name FROM filenamen;
  v_ziel     ORDSYS.ORDImage;
  v_context  RAW(400);
  v_bildnr   NUMBER;
  v_start    TIMESTAMP;
  v_ende     TIMESTAMP;
  v_count    NUMBER := 0;
  v_groesse  NUMBER := 0;
BEGIN
  SELECT SYSTIMESTAMP INTO v_start FROM dual;
  FOR rec IN bildcur LOOP
 -- Diesmal wird bei der Initialisierung gleich der Quelltyp,
 -- das Quellverzeichnis und der Name der Datei angegeben.
    INSERT INTO bilder_hawaii
    VALUES(bildseq.nextval, rec.name,ORDSYS.ORDImage.init('file','BILDER',rec.name))
    RETURNING bild_nr INTO v_bildnr;
    SELECT Bild INTO v_ziel FROM Bilder_Hawaii
    WHERE bild_nr = v_bildnr FOR UPDATE;
    v_ziel.importFrom(v_context, 'file', 'BILDER', rec.name);
    UPDATE Bilder_Hawaii SET Bild = v_ziel WHERE bild_nr = v_bildnr;
    v_count := bildcur%rowcount;
    v_groesse := v_groesse + v_ziel.getContentLength;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE
 ('Es wurden '||v_count||' Bilder eingefügt. Gesamtgrösse: '||v_groesse);
  SELECT SYSTIMESTAMP INTO v_ende FROM dual;
  DBMS_OUTPUT.PUT_LINE('Laufzeit: '||
   REGEXP_SUBSTR((v_ende-v_start), '[^ ]+',1,2));
  COMMIT;
END;

--Testen mit
exec bild_einfuegen1

Es wurden 667 Bilder eingefügt. Gesamtgrösse: 413089359
Laufzeit: 00:01:13.391000000 (bei einer 11g-Version)
Laufzeit: 00:01:40.342000000 (bei einer 10g-Version)


Der Prozess dauert zwar ungefähr doppelt solange wie mit der BLOB-Methode, aber dafür werden die Bildeigenschaften gleich mit gespeichert, und man kann sie auch per Select auslesen:

SELECT b.bild.getContentLength() Grösse,
 b.Bild.getCompressionFormat() Kompression,
 b.Bild.getContentFormat() Farbraum,
 b.Bild.getFileFormat() Dateiformat,
 b.Bild.getHeight() Höhe,
 b.Bild.getWidth() Breite,
 b.Bild.getMetadata() Metadaten
FROM bilder_hawaii b
WHERE b.bild_nr = 1;

    GRÖSSE KOMPRESSION FARBRAUM DATEIFORMAT  HÖHE BREITE
---------- ------------ --------- ----------- ----- ----------
    534016 JPEG-PROGRESSIVE 24BITRGB JFIF   729  1024


Alternativ kann man die Metadaten als XML auslesen:

SELECT b.Bild.getMetadata() Metadaten
FROM bilder_hawaii b
WHERE b.bild_nr = 1;

METADATEN
----------------------------------------------------
SYS.XMLTYPE(
<ordImageAttributes xmlns="http://xmlns.oracle.com/ord/meta/ordimage" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/ordimage http://xmlns.oracle.com/ord/meta/ordimage">
<height>729</height>
<width>1024</width>
<contentLength>534016</contentLength>
<fileFormat>JFIF</fileFormat>
<contentFormat>24BITRGB</contentFormat>
<compressionFormat>JPEG-PROGRESSIVE</compressionFormat>
<mimeType>image/jpeg</mimeType>
</ordImageAttributes>)

 

Fazit

Der Import von Bildern erscheint zwar zunächst ein bisschen umständlich. Wenn man sich aber erst einen Satz geeigneter Prozeduren erstellt hat, steht der sicheren Speicherung großer Bildmengen in der Datenbank nichts mehr im Weg. Die Prozeduren kann man im Fall der BLOBs leicht abgewandelt natürlich auch für den Import anderer binärer Files (wie z. B. PDF- und Word-Dokumente) nutzen.
Mehr über den Import, Export und die Bearbeitung von LOBs erfahren Sie in unserer Opens internal link in current windowPackages-Schulung.

PL/SQL

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.