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 Monatstipp 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ässt 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 Packages Schulung.