Ein einfacher Ansatz, Dateien eines Ordners komplett in Tabellen zu laden

English title: A simple approach to load an entire directory into a table

Letzte Woche bekam ich die Frage, wie man denn am einfachsten ein ganzes Verzeichnis voller Dateien (Bilder, PDF-Dokumente oder andere) in die Datenbank laden kann. Der SQL*Loader setzt ja voraus, dass alle Inhalte in einer oder zumindest in bestimmten, bekannten Dateien enthalten sind. Im vorliegenden Fall ist die Anforderung ja eher, dass man ein Verzeichnis kennt und einfach den ganzen Inhalt in eine Tabelle mit einer BLOB-Spalte laden möchte.

Last week I was asked for an easy approach to load an entore directory containing files (PDF documents, images, etc) into a database table. SQL*Loader is not appropriate for this since all the content must be in one or multiple known files. But this case is another - the directory is known, but not the files in it.

Meist fängt man dann an, umfangreiche Java oder C-Programme zu schreiben - das muss aber nicht unbedingt sein. Hilfreich kann die PL/SQL-Unterstützung für Betriebssystem-Kommandos (OS_COMMAND) sein, welche ich vor einiger Zeit veröffentlicht habe und welche hier zum Download bereitsteht.

Most people start coding at this point: some C/C++, Java or PL/SQL logic which imports the files into the database. But this is not necessary: The PL/SQL package for operating system interaction (OS_COMMAND) which I published some time ago (and which is downloadable here) might be helpful here.

Nach Installation steht der Objekttyp FILE_TYPE und das Paket FILE_PKG zur Verfügung. Zunächst brauchen wir das Listing eines Verzeichnisses: Nichts leichter als das!

After installation we have an object type FILE_TYPE and the PL/SQL package FILE_PKG. First we need the directory listing: Nothing is easier!

SQL> select e.file_name, e.is_dir, e.file_size 
  2  from table(file_pkg.get_file_list(file_pkg.get_file('/path/to/my/dir'))) e

FILE_NAME                      I  FILE_SIZE
------------------------------ - ----------
another_directory              Y          0
DSC_5112_m.jpg                 N    5384837
DSC_5128_m.jpg                 N    7005470
DSC_5130_m.jpg                 N    6212896
DSC_5131_m.jpg                 N    7203724
DSC_5134_m.jpg                 N    7295177
DSC_5135_m.jpg                 N    7057265
DSC_5136_m.jpg                 N    7194343
:                              :          :

Mit der Methode GET_CONTENT_AS_BLOB können die Dateien ausgelesen werden; geht es also darum, eine Tabelle effizient zu füllen, könnte das so aussehen:

The method GET_CONTENT_AS_BLOB retrieves the file contents as a BLOB. To import the files into a table we can use it as follows:

drop table bilder_tab
/

create table bilder_tab (
  filename varchar2(200),
  content  blob
) nologging
/

/*
 * All at once 
 */

declare
  v_dirname varchar2(200) := '/path/to/my/dir';
begin
  insert /*+ APPEND*/ into bilder_tab (
    select e.file_name, e.get_content_as_blob() 
    from table(file_pkg.get_file_list(file_pkg.get_file(v_dirname))) e
    where e.is_dir = 'N'
  );
end;
/
sho err

commit
/

Ein anderer Ansatz (mit Commit-Intervall) könnte so aussehen:

Another approach (which COMMITs every n files) might look as follows:

set serveroutput on

declare
  v_dirname varchar2(200) := '/path/to/my/dir';
  v_counter pls_integer := 0;
  v_commit  pls_integer := 5;
begin
  for i in (
    select e.file_name, e.get_content_as_blob() as content
    from table(file_pkg.get_file_list(file_pkg.get_file(v_dirname))) e
  ) loop
    v_counter := v_counter + 1;
    insert into bilder_tab values (i.file_name, i.content);
    if mod(v_counter, v_commit) = 0 then
      commit;
      dbms_output.put_line(v_counter || 'file loaded: COMMIT!');
    end if;
  end loop;
end;
/
sho err

commit
/