In diesem Tipp stellen wir Ihnen das mit APEX 4.1 neu eingeführte Feature "Data Upload" für den Endanwender vor. Während der APEX-Entwickler kommaseparierte Dateien schon seit HTMLDB 1.5 in Tabellen hochladen kann, musste ein solcher Dialog für den Endanwender erst aufwändig programmiert werden. APEX 4.1 ändert das mit dem neuen Assistenten Data Loading. Diesen stellen wir anhand eines einfachen Beispiels vor. Als kommaseparierte Datei nehmen wir die Inhalte der wohlbekannten Tabelle EMP wie folgt:

"Empno","Ename","Job","Mgr","Hiredate","Sal","Comm","Deptno"
"7369","SMITH","CLERK","7902","17-DEC-80","800","","DALLAS"
"7499","ALLEN1","SALESMAN","","20-FEB-81","1600","300","CHICAGO"
"7521","WARD","SALESMAN","","22-FEB-81","1250","500","CHICAGO"
"7566","JONES","MANAGER","","02-APR-81","2975","","DALLAS"
"7654","MARTIN","SALESMAN","","28-SEP-81","1250","1400","CHICAGO"
"7698","BLAKE","MANAGER","","01-MAY-81","2000","","NEW YORK"
"7782","CLARK","MANAGER","","09-JUN-81","2450","","BOSTON"
"7788","SCOTT","ANALYST","","09-DEC-82","3000","","DALLAS"
"7839","KING","PRESIDENT","","17-NOV-81","5000","","NEW YORK"
"7844","TURNER","SALESMAN","","08-SEP-81","1500","0","CHICAGO"
"7876","ADAMS","CLERK","7788","12-JAN-83","1100","","NEW YORK"
"7900","JAMES","CLERK","7698","03-DEC-81","950","","CHICAGO"
"7902","FORD","ANALYST","7566","03-DEC-81","3000","","NEW YORK"
"7934","MILLER","CLERK","7782","23-JAN-82","1300","","NEW YORK"

Die passende Zieltabelle ist dann (auf Basis der bekannten Tabelle EMP) ebenfalls schnell angelegt ...

create table emp2 as 
select * from emp where 1=0

CSV-Upload mit APEX Bordmitteln

Damit kann es losgehen: Wir wollen also nun (in einer APEX-Anwendung) dem Endanwender die Möglichkeit geben, CSV-Dateien hochzuladen und die Inhalte in die Tabelle EMP2 zu speichern. Erstellen Sie also eine neue APEX-Anwendung oder nehmen Sie eine bereits vorhandene. Rufen Sie, beispielsweise aus den Gemeinsamen Komponenten heraus, den Dialog zum Erstellen einer neuen Seite auf (Abbildung 1).

Abbildung 1: Eine neue Seite erstellen: "Data Loading"

Sie werden nun durch einen mehrseitigen Assistenten geführt, mit dem Sie den CSV-Upload für den Endanwender gestalten können. Zunächst wird die Zieltabelle mitsamt ihrer eindeutigen Spalten festgelegt (Abbildung 2).

Abbildung 2: Assistent Data Loading I: Zieltabelle einrichten"

Im darauf folgenden Dialog können Lookup-Tabellen konfiguriert werden. Wenn Sie sich die kommaseparierte Datei genauer ansehen, bemerken Sie, dass die letzte Spalte nicht die numerischen DEPTNOs enthält, sondern die Ortsnamen. Diese stehen aber in der Tabelle DEPT und dort in der Spalte LOC. Die Tabelle EMP2 sieht für die DEPTNO eine NUMBER-Spalte vor. Also muss die DEPTNO anhand des Ortes herausgesucht werden. Abbildung 3 zeigt, wie das dazu nötige Table Lookup eingerichtet wird (denken Sie daran, auf die Schaltfläche Add zu klicken, bevor Sie mit Next weitermachen).

Abbildung 3: Assistent Data Loading II: Lookup-Tabelle einrichten

Als nächstes können Sie einfache Transformationsregeln für die hochgeladenen Daten festlegen. In APEX 4.1 wird nur ein eingeschränkter Umfang an Transformationsfunktionen angeboten. Allerdings werden wir am Ende dieses Tipps einen Ansatz zeigen, mit dem eigene, PL/SQL basierte Transformationsregeln möglich werden und somit keine Wünsche mehr offenbleiben. Doch zunächst zurück zu den Transformationsregeln: Abbildung 4 zeigt das Einrichten einer Regel zum Umwandeln der Spalte ENAME in Lower Case.

Abbildung 4: Assistent Data Loading III: Transformationsregeln

Danach erlaubt APEX Ihnen das Anpassen der Seitentitel und Seitennummern. Spätestens hier wird deutlich, dass auch der Endanwender durch einen mehrseitigen Dialog geführt wird.

Abbildung 5: Assistent Data Loading IV: Seitendefinitionen

Legen Sie danach fest, ob und wenn ja, wie Reiterkarten für die generierten Seiten erzeugt werden sollen. Im letzten Dialog schließlich legen Sie die Namen der Schaltflächen fest und bestimmen die Anwendungsseite, auf die der Anwender bei Abbruch oder Fertigstellung des Ladevorgangs geleitet werden soll.

Abbildung 6: Assistent Data Loading V: Schaltflächen und Verzweigungen

Wie immer bekommen Sie danach eine Zusammenfassung Ihrer Angaben und ein Klick auf Finish erstellt dann die Anwendungsseiten. Starten Sie danach die Anwendung und navigieren Sie zur Seite 1001. Sie sind nun in der Rolle des Endanwenders und starten einen "Datenladevorgang".

CSV-Datei als Endanwender hochladen

Abbildung 7: CSV-Upload für den Endanwender I: Daten hochladen oder einfügen

Kommt Ihnen diese Seite bekannt vor? Es ist in der Tat nahezu die gleiche Seite, wie der APEX-Entwickler sie vom CSV-Upload her kennt. Nun befindet sich diese Seite jedoch in Ihrer APEX-Anwendung. Fügen Sie also die in diesem Tipp anfangs vorgestellten CSV-Daten per Copy & Paste ein und setzen Sie das Trennzeichen von "\t" (für TAB) auf "," um. Klicken Sie dann auf Next.

Abbildung 8 zeigt den Dialog, in dem der Endanwender die Spalten seiner CSV-Datei auf die konkreten Tabellenspalten abbildet. Die Tabelle selbst wurde vom Entwickler bereits fest definiert. Das ist auch eine ganz nützliche Eigenschaft, denn in der Praxis dürfte es pures Glück sein, wenn die Reihenfolge der Spalten in der CSV-Datei exakt der Reihenfolge in der Tabellendefinition entspricht.

Abbildung 8: CSV-Upload für den Endanwender II: Mapping der CSV-Spalten auf Tabellenspalten

Achten Sie darauf, ggfs. das Datumsformat in der Spalte HIREDATE einzurichten. Die CSV-Daten in der Spalte DEPTNO passen zwar nicht zur Tabellendefinition, aber ist egal - schließlich haben Sie als Entwickler einen Table Lookup definiert. Klicken Sie auf Next, um zur nächsten Seite zu gelangen.

Abbildung 9: CSV-Upload für den Endanwender III: Überprüfen der Daten vor dem Laden in die Tabelle

An den Daten in Abbildung 13 können Sie sehr schön erkennen, dass nun auch die definierten Table Lookup- und Transformationsregeln angewendet wurden - denn nun enthält die Spalte DEPTNO numerische Werte und ENAME ist kleingeschrieben. Mit einem Klick auf Load Data kann der Anwender die Daten nun in die Tabelle laden.

Abbildung 10: CSV-Upload für den Endanwender IV: Zusammenfassung

Zum Abschluß wird eine Zusammenfassung angezeigt, aus der hervorgeht, welche Sätze neu eingefügt, welche aktualisiert und welche nicht geladen werden konnten. Ein Klick auf Finish verzweigt dann auf die vom Entwickler in Abbildung 6 festgelegte Seite.

Anpassen des Prozesses an eigene Bedürfnisse

So weit, so gut. Der Assistent zum Data Loading erzeugt einen vom Endanwender bedienbaren, leicht verständlichen Dialog zum Hochladen von CSV-Daten. Wie der APEX-Entwickler kann auch der Endanwender entweder eine Datei hochladen oder mit Copy & Paste arbeiten. Allerdings bleiben in der Praxis Wünsche offen:

• Man möchte die Daten auf mehrere Tabellen verteilen
• Man möchte andere Transformationen anwenden
• ...

Als nächstes soll der Standardprozess ein wenig individualisiert werden. Als Beispiel soll die Spalte ENAME nicht in Kleinschreibung, sondern und Groß- und Kleinschreibung umgewandelt werden (Mixed Case). Allerdings bietet APEX hierfür keine Transformationsregel an. Also muss ein anderer Weg gefunden werden. Zunächst aber soll die vorhandene Transformationsregel für die Umwandlung in Kleinschreibung wieder gelöscht werden. Navigieren Sie dazu zu den Gemeinsamen Komponenten. Dort gibt es oben links den Eintrag Data Loading (Abbildung 11). Klicken Sie darauf.

Abbildung 11: Data Loading in den Gemeinsamen Komponenten

In der Übersicht über die vorhandenen Data Loading-Konfigurationen dürfte es im Moment nur eine geben. Nach Auswahl derselben sehen Sie die vorhin gemachten Einstellungen in einer Übersicht (Abbildung 12).

Abbildung 12: Übersicht über die Data Loading Konfiguration

Klicken Sie auf den Bleistift, um die Transformationsregel ENAME LC zu bearbeiten. Da es jedoch keine Auswahl für Mixed Case gibt, löschen Sie die Regel mit einem Klick auf die Schaltfläche Delete. Verfahren Sie genauso mit dem konfigurierten Table-Lookup, welcher den Ortsnamen in eine DEPTNO umwandelt.

CSV-Upload: Ein Blick unter die Motorhaube

Im folgenden schauen wir uns die Arbeitsweise des Data Loading etwas genauer an. Vorab soviel: Die hier vorgestellten Details sind nicht dokumentiert - aber mit APEX Bordmitteln (Betrachten des Session State) leicht erkenn- und nachvollziehbar. Es kann allerdings sein, dass das eine oder andere Detail sich in einer künftigen APEX-Version ändert. Navigieren Sie in Ihrer APEX-Anwendung zur Seite 1003 (in Abbildung 9 dargestellt). Dort werden die Daten - unmittelbar vor dem Laden in die Tabelle angezeigt. Diese Seite wollen wir uns nun etwas näher ansehen. Navigieren Sie zu den Eigenschaften der Seite 1003 (Abbildung 13).

Abbildung 13: Eigenschaften der Seite 1003 (Überprüfen der Daten vor dem Laden in die Tabelle)

Die Darstellung der Daten wird offensichtlich mit einem Bericht erledigt. Diesen sollten Sie sich nun mal näher ansehen (Abbildung 14).

Abbildung 14: Berichtsdefinition (Überprüfen der Daten vor dem Laden in die Tabelle)

Offensichtlich werden die hochgeladenen Daten in einer Collection (hier: LOAD_CONTENT) zwischengespeichert. Und tatsächlich verwendet APEX für das Data Loading nicht nur eine, sondern insgesamt 5 Collections.

• CLOB_CONTENT hält in der Spalte CLOB001 die hochgeladenen CSV-Daten
• SPREADSHEET_CONTENT hält die geparsten Daten C001 bis C050. Daher kommt auch die Begrenzung auf 50 Spalten. In der Spalte N001 ist die Zeilennummer abgelegt.
• PARSE_COL_HEAD enthält die beim Parsing der CSV-Datei in der ersten Zeile erkannten Spaltennamen. Die Spalte C001 enthält den Spaltennamen, die Spalte C002 informiert darüber, welche Spalte der Collection SPREADSHEET_CONTENT die Inhalte jener Spalte enthält.
• Nachdem die Lookup- und Transformationsprozesse gelaufen sind, sind die so modifizierten Daten in der Collection LOAD_CONTENT und die Zuordnung zu den Spalten der Zieltabelle in LOAD_COL_HEAD enthalten.

Das Parsen der CSV-Datei, das Mapping auf die Tabellenspalten sowie das Anwenden von Lookups und Transformationsregeln wird durch onSubmit-Prozesse gesteuert, die bei Klick auf die Schaltfläche Next ausgelöst werden. Allerdings können diese Prozesse nicht "manuell" einer anderen, beliebigen APEX-Seite hinzugefügt werden - das geht nur über den beschriebenen Assistenten zum Data Loading.

• Auf Seite 1001 ist ein Prozess vom Typ Parse Uploaded Data enthalten; dieser übernimmt das Parsing der CSV-Datei und die Übernahme der Daten in die Collections SPREADSHEET_CONTENT und PARSE_COL_HEAD. Die für den Parser wichtigen Angaben wie das Trennzeichen oder Dezimalzeichen werden beim Prozess konfiguriert.
  
  Die Namen der verwendeten Collections können allerdings nicht verändert werden.
• Auf Seite 1002 findet sich der Prozess Prepare Uploaded Data - dieser nimmt die Lookups vor und wendet die Transformationsregeln an. Im Prozess selbst nur der Name der Data Loading-Konfiguration eingestellt. Auch hier können die Namen der Collections nicht verändert werden. Es kann allerdings ein weiterer PL/SQL-Prozess eingerichtet werden ...
• Seite 1003 schließlich enthält einen Prozess vom Typ Load Uploaded Data. Wie man sich denken kann, führt dieser den tatsächlichen Ladevorgang in die Tabelle durch.

Im folgenden wird nun ein Ansatz vorgestellt, mit dem auch individuelle Transformationen und Ladevorgänge gestaltet werden können. Allerdings nehmen wir hierfür einen von APEX unabhängigen, datenbankzentrischen Ansatz mit einer View und einem INSTEAD OF Trigger.

Individueller CSV-Upload mit View und INSTEAD-OF Trigger

Die "eigenen" Transformationregeln werden in diesem Ansatz aus APEX herausgehalten. Zwar kennen wir seit unserem "Blick unter die Motorhaube" die Namen der verwendeten Collections und Prozesse; allerdings sind diese nicht dokumentiert - es ist insofern nicht sicher, sich auf Collection-Namen wie LOAD_CONTENT oder PARSE_COL_HEAD zu verlassen. In einer künftigen APEX-Version könnten diese sich ändern.

Die Lösung ist eine View mit INSTEAD-OF Trigger - die Daten werden von APEX in die View eingefügt; der INSTEAD-OF Trigger übernimmt die Daten in eine oder mehrere Zieltabellen und transformiert sie nach Bedarf. Das könnte in etwa so aussehen.

alter table emp2 rename to emp_target
/

create or replace view emp2 as select 
  e.empno, 
  upper(e.ename) ename,
  upper(e.job)   job,
  e.mgr,
  e.hiredate,
  e.sal,
  e.comm,
  d.loc deptno
from emp_target e join dept d on (e.deptno = d.deptno)
/

create sequence seq_deptno start with 50 increment by 1
/

create or replace trigger tr_process_emp2 
instead of insert or update or delete on emp2
for each row
declare
  v_deptno dept.deptno%type;
begin
  if inserting or updating then
    begin
      select deptno into v_deptno from dept where loc = :new.deptno and rownum = 1 ;
    exception
      when NO_DATA_FOUND then
         insert into dept values (seq_deptno.nextval, 'DEPT IN '||:new.deptno, :new.deptno) 
         returning deptno into v_deptno;
      when others then raise;
    end;
  end if;
  if inserting then
    insert into emp_target values (
       :new.empno, 
       initcap(:new.ename), 
       initcap(:new.job), 
       :new.mgr,
       :new.hiredate,
       :new.sal,
       :new.comm,
       v_deptno
    );
  elsif updating then
    update emp_target set
      ename    = initcap(:new.ename),
      job      = initcap(:new.job),
      mgr      = :new.mgr,
      hiredate = :new.hiredate,
      sal      = :new.sal,
      comm     = :new.comm,
      deptno   = v_deptno
    where empno = :new.empno;
  elsif deleting then
    delete from emp_target where empno = :old.empno;
  end if;
end;
/
sho err

Zunächst wird die Tabelle EMP2 nach EMP_TARGET umbenannt. Danach wird eine View mit dem Namen EMP2 so erzeugt, dass die hochzuladenden CSV-Dateien auf die View passen - Lookup- und Transformationsregeln in APEX werden nicht mehr benötigt - denn diese Aufgabe übernimmt der nachfolgende INSTEAD OF-Trigger. Für die INSERT, UPDATE oder DELETE-Operationen wird in PL/SQL programmiert, was auf der Basistabelle passieren soll. Im Trigger-Code sind die Abschnitte für die INSERT-, UPDATE- oder DELETE-Operationen leicht erkennbar. Für INSERT oder UPDATE wird zu Beginn noch die DEPTNO anhand des Ortes herausgesucht und ggfs. eine neue Zeile in die Tabelle DEPT eingefügt.

Damit ist man logischerweise völlig frei und nicht mehr durch den APEX-Funktionsumfang eingeschränkt. Im Trigger lässt sich, wie gesehen, sogar der Spezialfall abdecken, dass die Lookup-Regel fehlschlägt, weil es den in den hochgeladenen Daten enthaltenen Ort gar nicht in der Tabelle DEPT gibt. Durchläuft man nun die APEX-Seiten erneut (Achtung: Die Transformations- und Lookup-Regel muss, wie oben beschrieben, gelöscht sein), so finden sich anschließend folgende Daten in der Tabelle EMP_TARGET.

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7369 Smith      Clerk      7902 17.12.1980 00:00:00   800           20
 7499 Allen1     Salesman        20.02.1981 00:00:00  1600   300     30
 7521 Ward       Salesman        22.02.1981 00:00:00  1250   500     30
 7566 Jones      Manager         02.04.1981 00:00:00  2975           20
 7654 Martin     Salesman        28.09.1981 00:00:00  1250  1400     30
 7698 Blake      Manager         01.05.1981 00:00:00  2000           10
 7782 Clark      Manager         09.06.1981 00:00:00  2450           40
 7788 Scott      Analyst         09.12.1982 00:00:00  3000           20
 7839 King       President       17.11.1981 00:00:00  5000           50
 7844 Turner     Salesman        08.09.1981 00:00:00  1500     0     30
 7876 Adams      Clerk      7788 12.01.1983 00:00:00  1100           10
 7900 James      Clerk      7698 03.12.1981 00:00:00   950           30
 7902 Ford       Analyst    7566 03.12.1981 00:00:00  3000           10
 7934 Miller     Clerk      7782 23.01.1982 00:00:00  1300           10

Wenn in den CSV-Daten ein bislang unbekannter Ort enthalten war, legt der Trigger die Zeile in der Tabelle DEPT automatisch an ...

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 DEPT IN MUNICH MUNICH

Man sieht, dass (hier) sowohl JOB als auch ENAME erfolgreich im "Mixed Case" vorliegen. Dieser Ansatz hat zusätzlich den Charme, dass man ihn auch außerhalb von APEX nutzen kann.

Zurück zur Community-Seite