23. Februar 2011

BLOBs ins Dateisystem "entladen" - nur mit einer "SQL Abfrage"

"unload" a bunch of BLOBs to a filesystem folder
Wolltet Ihr schonmal eine Tabelle mit BLOBs in ein Verzeichnis "ausladen" ... wobei jeder BLOB in eine eigene Datei geschrieben werden soll ...? Bei mir war es heute wieder mal soweit ... und natürlich hätte man dazu ein wenig PL/SQL-Code mit UTL_FILE schreiben können - ich wollte es aber schnell haben. Also habe ich mein Package für Betriebssystem-Kommandos und Dateisystem-Zugriffe genommen. Meine Tabelle sah so aus ...
Did you ever have the situation where you wanted to "unload" a table with BLOBs into a folder of the local filesystem - as a individual file for each BLOB. Today I had to do so ... and of course - we could write some PL/SQL code using UTL_FILE - but I did not have that much time. So I took my package for operating system commands and filesystem access and used that. My table looked as follows ...
SQL> desc target_table
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------------
 ID                                                 NUMBER
 FILENAME                                           VARCHAR2(4000)
 FILECONTENT                                        BLOB
Das "Entladen" konnte ich mit einem einfachen SQL SELECT machen:
And this SQL "query" did the trick ...
SQL> select 
  2  filename, 
  3  file_pkg.get_file('/home/oracle/test/'||filename).make_file().append_to_file(filecontent) bytes_written 
  4 from target_table;

FILENAME                                           BYTES_WRITTEN
-------------------------------------------------- -------------
2010-003a_yahoo_quote.html                                  3305
2010-003b-bestdbproject.html                                1433
2010-007_charset_nls_iana.html                              3125
2010-009_emalert_rss.html                                  17047
2010-009_emalert_rss_01.png                               171007
2010-009_emalert_rss_02.png                                38661
2010-009_emalert_rss_03.png                               141388
2010-010_blob-remote.html                                  14636
2010-011_imp_dataonly.html                                  3471
2010-012_recursive-table-functions.html                     4916
2010-013_romannumber.html                                   1153
2010-014-doag-sig-spatial.html                              1025
2010-015-dbms-applicationinfo.html                          7539
2010-016-addmonths.html                                     3842
2010-017-zip.html                                          29251
:                                                              :

50 rows selected.

Elaped: 00:00:00.17
Danach sah das Verzeichnis /home/oracle/test so aus ...
The filesystem folder looked like this afterwards ...
[oracle@sccloud038 test]$ ls -lah
total 752K
drwxr-xr-x 2 oracle oinstall 4.0K Feb 23 13:13 .
drwxr-xr-x 3 oracle oinstall 4.0K Feb 23 13:12 ..
-rw-r--r-- 1 oracle oinstall 2.8K Feb 23 13:13 2010-001a_apex-plugins.html
-rw-r--r-- 1 oracle oinstall 8.9K Feb 23 13:13 2010-001_dbms_datapump_exp.html
-rw-r--r-- 1 oracle oinstall 3.6K Feb 23 13:13 2010-002a_oscomm_0.9.html
-rw-r--r-- 1 oracle oinstall 8.0K Feb 23 13:13 2010-002_dbms_datapump_imp.html
-rw-r--r-- 1 oracle oinstall 3.3K Feb 23 13:13 2010-003a_yahoo_quote.html
-rw-r--r-- 1 oracle oinstall 1.4K Feb 23 13:13 2010-003b-bestdbproject.html
-rw-r--r-- 1 oracle oinstall  12K Feb 23 13:13 2010-003_dbms_stat_funcs.html
-rw-r--r-- 1 oracle oinstall 7.7K Feb 23 13:13 2010-004_miniskript_lo.html
-rw-r--r-- 1 oracle oinstall  11K Feb 23 13:13 2010-005_resumable.html
-rw-r--r-- 1 oracle oinstall 6.5K Feb 23 13:13 2010-006_video.html
-rw-r--r-- 1 oracle oinstall 3.1K Feb 23 13:13 2010-007_charset_nls_iana.html
-rw-r--r-- 1 oracle oinstall  29K Feb 23 13:13 2010-008-recursive_with.html
-rw-r--r-- 1 oracle oinstall 167K Feb 23 13:13 2010-009_emalert_rss_01.png
Ist doch nett, oder ...? Alles im allem habe ich für das Schreiben dieses Postings mehr Zeit gebraucht ...
Creating this blog posting actually took more time than doing this piece of work ...

Kommentare:

Tobias Arnhold hat gesagt…

Wahnsinn wie einfach das geht. Ich glaube ich werde mir dein Package mal näher anschauen.

Grüße

Tobias

Peter Raganitsch hat gesagt…

Wiedermal ein super Tipp, Danke !

Chris, CDS-SYSTEMS hat gesagt…

Supergeile Lösung, aber............

ich habe hier einen Kunden der mehr als ein Attachment in ein BLOB geschrieben hat (1 PDF, 1 JPG zum Beispiel). Nun, diese kann ich nicht speichern, hast du hier vielleicht einen kleinen Tip ?! Wäre echt super.... LG

Carsten Czarski hat gesagt…

Hallo Chris,

hier muss ich nachfragen: Es sind mehr Dateien in ein- und demselben BLOB enthalten ...?
Geht es um Email Attachments ...? Ist der BLOB ein ZIP Archiv.

Man muss auf jeden Fall wissen, wie der BLOBH (der ja mehrere Dateien enthält) aufgebaut ist, dann kann man ihn auch "auseinandernehmen" ...

Beste Grüße

-Carsten

Chris hat gesagt…

Also,

es gibt einen Table Auftrag2, bestehend aus:
Auftrag_nr : Integer
Dateien: BLOB

Wenn ich nun mit der dazugehörigen Client-Software den Inhalt öffne, erscheint sowohl die Filenamen wie auch der jeweils einzelne Inhalt. Mit der echt coolen Funktion von dir kann ich nun jedes Blob, in dem genau 1 File vorhanden ist, problemlos exportieren. Dies ist ein PDF. Sind aber mehrere Dateien in diesem Blob, bin ich leider nicht in der Lage das exportierte File zu öffnen. Hast du irgendeine Idee, wie ich an diese Files kommen kann. Ich habe bereits den Hersteller kontaktiert, der möchte sich morgen nochmals melden, ist sich aber selbst nicht sicher :-(. Kann ich Dir vielleicht irgendwan schicken, vielleicht einfach das exportierte File oder ähnliches ? Sonst irgendeine Idee. IMO verzweifel ich , Danke und LG

Chris hat gesagt…

Nachtrag:

Ich habe mal die Files (JPG) verglichen, welche ich aus dem System speichern kann mit denen die ich mit deiner Funktion aus der Datenbank ziehe. 1.) In den ersten paar Bytes steht der Dateiname, dann kommt ein Semikolon, dann bis zum nächsten attachment blablabla und wieder ein Semikolon. Aber, wenn ich alles entferne, habe ich immer noch Unterschiede, alle HEX 00 sind mit HEX 20 ersetzt, naja, nicht alle, aber viele :-(, ne Idee..Danke

Carsten Czarski hat gesagt…

Hallo Chrism

dazu kann ich so nur wenig sagen; denn das hängt ganz davon ab, wie "der Hersteller" die Dateien in das Blob verpackt hat. Es könnte sein, dass es Standardcode gibt, der die einzelnen Dateien extrahieren kann, es kann aber auch sein, dass das ein ganz spezielles Format ist.

Ohne genauere Informationen zum Aufbau des BLOBs kann man da nur wenig machen ...

Beste Grüße

-Carsten

Jack hat gesagt…

Hallo,

funktioniert auf meiner 11.2.0.1 DB leider nicht.
Es wird ein Tracefile mit folgendem Inhalt generiert.

*** 2013-11-05 11:53:15.728
Exception in thread "Root Thread" java.io.IOException

at java.io.Win32FileSystem.createFileExclusively(Native Method)

at java.io.File.createNewFile(File.java:850)

at FileType.createEmptyFile(FILE_TYPE_JAVA:365)

Exception in thread "Root Thread" java.io.IOException

Haben Sie eine Idee?

Carsten Czarski hat gesagt…

Hallo Jack,

der Fehler tritt hier in dem Moment auf, wo die Java-Engine in der Datenbank dem Windows-Betriebssystem das Kommando gibt, die Datei anzulegen. Auf OS-Ebene schlägt das fehl. Leider steht nicht da, warum ...

In Frage kommen:

* Platte voll
* Rechteprobleme (ist es ein Netzlaufwerk)?

Beste Grüße

-Carsten Czarski

Jack hat gesagt…

Danke,

für die schnelle Antwort.
An einer vollen Platte liegt es nicht. Das es ein Rechteproblem ist, kann ich auch ausschließen.

Habe noch folgendes in einem Tarcefile gefunden


joez_compile_method: done compiling method sun/reflect/UnsafeFieldAccessorImpl.(0x2217c2a5).
joez_compile_method beginning compile of method sun/reflect/UnsafeQualifiedStaticShortFieldAccessorImpl.getShort
joez_compile_method: done compiling method sun/reflect/UnsafeQualifiedStaticShortFieldAccessorImpl.getShort(0x22186085).
joez_compile_method: failed compiling method oracle/aurora/zephyr/backend/amd64/AMD64BaseLIRInstr.length: method too long
joez_compile_method: failed compiling method oracle/aurora/zephyr/backend/amd64/AMD64BaseLIRInstr.emit: method too long
joez_compile_method: failed compiling method oracle/aurora/zephyr/backend/amd64/AMD64BaseLIRInstr.m_bits_emit: method too long
joez_compile_method: failed compiling method oracle/aurora/zephyr/bytecode/ByteCodeToMIR.translateBasicBlock: method too long
joez_compile_method: failed compiling method oracle/aurora/zephyr/backend/amd64/AMD64BaseLIRInstr.memoryOperandAllowed: method too long

Carsten Czarski hat gesagt…

Hallo Jack,

ich denke nicht, dass diese Meldungen die Ursache sind - sie kommen vom asynchron laufenden Java Native Compiler. Wenn dabei Fehler auftreten, arbeitet Java ohne.

Kann man denn mit PL/SQL und UTL_FILE eine Datei in besagtes Verzeichnis schreiben? Oder treten dann auch Fehler auf? Das wäre für die Einordnung des Problems wichtig.

Beste Grüße

Carsten

Jack hat gesagt…

Eigene Dummheit. Ich hätte mir anstatt der Trace-Files den Output vom SQL Developer genauer anschauen sollen. DBMS_JAVA.GRANT_PERMISSION
hat mein Problem gelöst. Danke für die Hilfe.

Beliebte Postings