Datei(Bild) in Oracle-Tabelle speichern

A

AKST

Erfahrenes Mitglied
Hallo Leute,

wie kann ich eine Datei in einem Blob-Feld speichern?
Ich möchte das im Enterprise Manager von Oracle (10g) (per mausklicks) und in SQL bewerkstelligen.
Beispiel:

Tabelle: "dozent"
Feld (Blob): "D_PIC"
restliche Felder: "D_NR", "D_Name"

Gruß
 
Zuletzt bearbeitet:
Bisher habe ich nur ein Beispiel für den Datentyp OrdImage gefunden. Wie gehts mit Blob?

update scott.dozent set D_Pic=ORDSYS.ORDImage.init('file','C:\','blacky.jpg') where d_nr=1;
COMMIT;
 
Ich habe mal ein komplettes Beispiel wo mein Problem mit PL/SQL gelöst wird. Es funktioniert auch. Dieses Beispiel bezieht sich aber nicht auf die Beispieltabelle des ersten Beitrages (dozent):

Ich finde das aber sehr umfangreich und umständlich, geht das nicht einfacher, so wie bei: "ORDSYS.ORDImage.init('file','C:\ordner','datei.jpg')"


-- Debugging Ausgaben (DBMS_OUTPUT) anschalten
SET SERVEROUTPUT ON;

-- Tabelle erzeugen
CREATE TABLE Bilder (
Lfd NUMBER(5) NOT NULL,
Bezeichnung VARCHAR(32),
Bild BLOB default EMPTY_BLOB(),
CONSTRAINT BILDER_PRIMARY_KEY PRIMARY KEY(Lfd)
);

-- Oracle DIRECTORY erzeugen
CREATE DIRECTORY MYDIR AS '/home/uhlmann/uni/db2/';

-- Datensatz mit leerem (aber nicht NULL) BLOB eintragen
INSERT INTO Bilder VALUES(1,'Star Wars Episode 1', EMPTY_BLOB());

-- PL/SQL spoken here
DECLARE
Dest_loc BLOB; -- mein temporaeres BLOB
Src_loc BFILE := BFILENAME('MYDIR', 'starwars1.jpg'); -- ein BFILE das auf die gewuenschte Datei zeigt
Amount INTEGER; -- Groesse des Bildes
BEGIN
-- den gewuenschten Datensatz raussuchen
SELECT Bild INTO Dest_loc FROM Bilder WHERE Lfd = 1 FOR UPDATE;

IF (DBMS_LOB.FILEEXISTS(Src_loc) = 1) THEN -- existiert die Datei?
-- ja:
Amount := DBMS_LOB.GETLENGTH(Src_loc); -- Bildgroesse bestimmen
DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); -- Quelle oeffen (ro)
DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE); -- Ziel oeffnen (rw)
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount); -- kopieren
DBMS_LOB.CLOSE(Dest_loc); -- Ziel schliessen
DBMS_LOB.CLOSE(Src_loc); -- Quelle schliessen
COMMIT; -- und committen
DBMS_OUTPUT.PUT_LINE('File successfully loaded.');
ELSE
DBMS_OUTPUT.PUT_LINE('File does not exist.'); -- nein: Fehlermeldung
END IF;
EXCEPTION -- Exceptions fangen und Fehlermeldungen ausgeben
WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory does not exist.');
WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('You do not have privileges for the directory.');
WHEN DBMS_LOB.INVALID_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory is invalid.');
END;

/

-- Anzeigen. BLOBs koennen nicht angezeigt werden, daher nur die Bildgroesse
SELECT Lfd,Bezeichnung,DBMS_LOB.GETLENGTH(Bild) FROM BILDER;

-- Aufraeumen
DROP TABLE BILDER;
DROP DIRECTORY MYDIR;
 
Hallo!

Danke für das nette Beispiel, allerdings ist es im Allgemeinen keine gute Praxis binäre Daten wie etwa Bilder in der Datenbank zu speichern. Da ist ein externer Speicher und nur die Pfade zu den Bildern schon eine "bessere Alternative".

Gruß Tom
 
@Thomas, sicherlich gibt es oft gute Gründe keine Bilder in einer Datenbank abzuspeichern, aber ich wollte das mal ausprobieren.

Andererseits habe ich eine Webanwendung (zum Erlernen von Servlets und JSP's) in der ich zu jedem Dozenten ein Bild speichern möchte. Der Benutzer dieser Anwendung kann sich die Bilder anschauen oder selbst welche hochladen. Ich finde, dass so eine DB ganz praktisch ist. Wenn die DB dann auf einem anderen Server läuft, muss man sich nicht um Dateipfade kümmern. Ich finde es einfach praktischer, da man sich beim Aktualisieren eines Bildes nicht um das Löschen des alten Bildes kümmern muss. Natürlich muss man aufpassen, wenn man z.B. eine Liste von Dozenten-Objekten in einem Vector speichert, dass die Binärdaten der Bilder nicht zuviel Speicher für jede Session verbrauchen.
 
Ein wirklich guter Grund, weshalb LOB`s in der Datenbank abgelegt werden sollten ist die Datenintegrität. Arbeiten mehrere Benutzer gleichzeitig an einem LOB (es muss ja kein komplettes Bild sein...) sichert die Datenbank die Integrität durch Transaktionshandling. Liegen die Daten einfach nur auf dem Filesystem muss ich um meine SQL Anweisungen und die Aktionen auf dem Filesystem mein eigenes Transaktionshandling programmieren.

Eine gute Alternative zu LOB`s in der DB sind BFILES. Das sind im Grunde auch nur Links auf externe Files. Es ist aber ein eigener von Oracle unterstützter Datentyp, mit dem das Transaktionshandling vereinfacht wird.

Das größte Problem bei externen Daten ist immer die Datensicherheit. Wer denkt schon an die externen Bilder etc.... ? Ein Export wird kompliziert, da nicht nur das Dumpfile sondern auch die externen Daten mitgeliefert werden müssen. Die Verzeichnisstruktur muss erhalten bleiben und evtl können sogar Probleme mit der Berechtigung auf OS Ebene auftreten.

LOB`s in der Datenbank sind nicht schön und der Zugriff per PL/SQL ist recht umständlich, aber wenn man sich einmal ein paar Standardprozeduren gebastelt hat ist das alles kein Problem mehr.
 
Hallo Leute,

ich habe folgenden Code mit dem ich nun ein Bild in die DB einfügen kann:

Code: 
set serveroutput on;

CREATE or replace DIRECTORY DIR AS 'C:\';

DECLARE
 Dest_loc       BLOB;						-- mein temporaeres BLOB
 Src_loc        BFILE;	-- ein BFILE das auf die gewuenschte Datei zeigt
 Amount         INTEGER;					-- Groesse des Bildes
BEGIN

 
 Src_loc:= BFILENAME('DIR', 'icke.jpg');
 -- den gewuenschten Datensatz raussuchen
 SELECT Bild INTO Dest_loc FROM scott.dozent WHERE D_NR = 5 FOR UPDATE;
 
 IF (DBMS_LOB.FILEEXISTS(Src_loc) = 1) THEN			-- existiert die Datei?
 								-- ja:
  Amount := DBMS_LOB.GETLENGTH(Src_loc);			--  Bildgroesse bestimmen
  DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);		--  Quelle oeffen (ro)
  DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);		--  Ziel oeffnen (rw)
  DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);		--  kopieren
  DBMS_LOB.CLOSE(Dest_loc);					--  Ziel schliessen
  DBMS_LOB.CLOSE(Src_loc);					--  Quelle schliessen
  COMMIT;							--  und committen
  DBMS_OUTPUT.PUT_LINE('File successfully loaded.');
 ELSE
  DBMS_OUTPUT.PUT_LINE('File does not exist.');			-- nein: Fehlermeldung
 END IF;
 EXCEPTION							-- Exceptions fangen und Fehlermeldungen ausgeben
  WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory does not exist.');
  WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('You do not have privileges for the directory.');
  WHEN DBMS_LOB.INVALID_DIRECTORY THEN DBMS_OUTPUT.PUT_LINE('Directory is invalid.');
END;

Jetzt möchte ich aus diesem Code eine Procedure oder Funktion machen der ich Tabellenname, Feld, Bildname, Ordername übergeben kann.
Ich kriege allein schon das einrichten eines directory in einer Procedure nicht hin.
Ein weiteres Problem besteht in folgender Zeile:
SELECT Bild INTO Dest_loc FROM scott.dozent WHERE D_NR = 5 FOR UPDATE;

hier möchte ich eben variable Werte einsetzen.
Hat jemand einen Lösungsvorschlag?

Gruß
 
Also, das CREATE DIRECTORY würde ich nicht in eine Procedure einbauen, da das Sicherheitsrisiko zu hoch ist. Das Directory sollte nur SYS anlegen und dem entsprechenden User Zugriff auf das Directory Objekt geben. Aber wenn du es trotzdem in die Funktion einbauen willst geht das mit

Code: 
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY DIR AS ''C:\''';

Habe leider grade nicht so viel Zeit und kann auch nicht testen, aber das ist mal ein Ansatz...

Code: 
CREATE OR REPLACE PROCEDURE LOAD_IMG (
	Dest_loc	INOUT BLOB,
	filename    IN VARCHAR2,
	directory   IN VARCHAR2
) AS
	Src_loc        BFILE;	
	Amount         INTEGER;
BEGIN

	EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY DIR AS ''' || directory || '''';
	Src_loc:= BFILENAME('DIR', filename);
   
 IF (DBMS_LOB.FILEEXISTS(Src_loc) = 1) THEN			
 								
	Amount := DBMS_LOB.GETLENGTH(Src_loc);			
	DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);	
	DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);	
	DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);	
	DBMS_LOB.CLOSE(Dest_loc);	
	DBMS_LOB.CLOSE(Src_loc);	
	COMMIT;					

 ELSE
 	ROLLBACK;
	RAISE_APPLICATION_ERROR( -20000, 'File does not exist.');
 END IF;
 
 EXCEPTION
	WHEN DBMS_LOB.NOEXIST_DIRECTORY 
		THEN RAISE_APPLICATION_ERROR( -20001, 'File does not exist.');
	WHEN DBMS_LOB.NOPRIV_DIRECTORY 
		THEN RAISE_APPLICATION_ERROR( -20002, 'You do not have privileges for the directory.');
	WHEN DBMS_LOB.INVALID_DIRECTORY 
		THEN RAISE_APPLICATION_ERROR( -20003, 'Directory is invalid.');
		
END;
/
 
Hallo und danke für deine Hilfe. Wenn ich nun ein user mit eingeschränkten Rechten bin, wie kann ich dann Datein in blobs speichern ohne ein directory anlegen zu müssen?
Dann muss ich ur joch herausfinden, wie ich folgende Anweisung ,mit Variablen ausführe:
SELECT Bild INTO Dest_loc FROM scott.dozent WHERE D_NR = 5 FOR UPDATE;

Ich bekomme da ständig Fehlermeldungen.

Gruß
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück