Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== Oracle APEX 19.2 - APEX_DATA_PARSER für das Laden von Excel und CSV Datein einsetzen =====


<fc #800000>**Aufgabe:**</fc>

Eine Datei, möglichst natives Excel, soll in eine Tabelle in der Datenbank geladen werden.

Vor 19.1 war zwar schon ein Data Load Wizard in APEX implementiert, allerdings war dieser sehr schnell bei Dateien mit mehr als teilweise nur 1000 Zeilen komplett überfordert und konnte nur CSV Dateien verarbeiten. 

In einer 5 APEX Umgebung daher mit eigenen Mitteln diese Aufgaben umständlich umgesetzt.

  * Daten in die DB über APEX laden =>  [[prog:oracle_apex_5_file_handling|Oracle APEX 5 -  Dateien laden und parsen]]

  * Daten wieder auslesen =>  [[prog:plsql_spool_csv_apex|Oracle PL/SQL - APEX - CSV Datei aus der DB mit DBMS_XMLGEN.CONVERT erzeugen]]
 

<fc #008080>**Lösung ab 19.1:**</fc>


Ab 19.1 kann mit dem APEX APEX_DATA_PARSER das ganze viel einfacher und vor allem auch ohne Plugins für natives Excel umgesetzt werden.
 

=> API => https://docs.oracle.com/en/database/oracle/application-express/19.2/aeapi/APEX_DATA_PARSER.html



Hierzu nun ein paar Anmerkungen mit den wichtigsten SQL Statements für diese Aufgabe.

----

==== Ablauf ====


  * Formular um eine CSV / Excel Datei in eine Tabelle zu laden
  * Den Inhalt der Datei nach dem Laden auf der Seite anzeigen
  * Laden der Daten aus der Datei in die passende Datenbank Tabelle
  * Anzeige der erfolgreich geladenen Daten und der fehlerhaften Daten



----

==== Laden der Datei in eine eigene Stage Tabelle ====


Die Stage Tabelle ist in diesem Projekt fachlich notwendig um die Dateien gleichzeitig zu archivieren.

Alternativ kann natürlich einfach die Datei per Standard APEX Verhalten in die APEX_APPLICATION_TEMP_FILES geladen und dann vor dort ausgelesen werden.


=== Stage Tabelle in der DB anlegen ===


Eine paassende Tabelle für den Datei Upload in der Datenbank anlegen, wie:

<code sql>

create table T_IMPORT_FILES 
  (  
    DDE_SK                 number(15) NOT NULL 
  , NAME                   VARCHAR2(400)  NOT NULL
  , FILENAME               VARCHAR2(400) 
  , MIME_TYPE              VARCHAR2(255) 
  , CREATED_ON             TIMESTAMP(6) default systimestamp          
  , BLOB_CONTENT           BLOB      
  --
  , SETTINGS_DEF_BY_USER   VARCHAR2(32 CHAR)      DEFAULT NVL(nvl(SYS_CONTEXT('APEX$SESSION','APP_USER'),SYS_CONTEXT('USERENV','PROXY_USER')),user)
  , CREATED_AT             TIMESTAMP(6)           DEFAULT systimestamp
  , SETTINGS_CHAN_BY_USER  VARCHAR2(32 CHAR)      DEFAULT NVL(nvl(SYS_CONTEXT('APEX$SESSION','APP_USER'),SYS_CONTEXT('USERENV','PROXY_USER')),user)
  , EDIT_AT                TIMESTAMP(6)           DEFAULT systimestamp
  , CONSTRAINT T_KDE_EXCLUDE_IMPORT_PK PRIMARY KEY (
		DDE_SK 
	  )
  ENABLE
)  
</code>


In APEX 


=== APEX Maske zum Laden der Daten ===


  * In APEX ein ITEM vom Typ "File Browser" anlegen
    * Es kann auch gleich die passende Tabelle hier angeben werden, dann kann ein eigener Prozess da für entfallen.
  * Button um den Lade Prozess zu starten

Hier ein Beispiel für ein Prozess um Datei in eigene Temporäre Tabelle laden, falls mehr Logik notwendig ist als der APEX Default liefert:

<code plsql>

declare

  v_file_count pls_integer:=0;

  v_file blob;

  v_message varchar2(4000):='Prozessing File Insert'||'<br/>';
 
  v_filename varchar2(512);

  v_id number;
  
begin 
    if :P3400_IMPORT_FILE is not null then
    
    --- Put your own logic in here
   
     v_filename:=:P3400_IMPORT_FILE;
    
     v_message:= v_message||' Name of P3400_IMPORT_FILE is '||v_filename||'<br/>';

        select count(ID) into v_file_count
            from apex_application_temp_files
            where name = v_filename;

        if v_file_count > 0  then
              for rec in (select
                    id,
                    application_id,
                    name,
                    filename,
                    mime_type,
                    created_on,
                    blob_content
                from apex_application_temp_files where name = v_filename ) loop               
                   
                      v_message:= v_message||' Read File  with filename '||rec.filename||'<br/>'; 
                       insert into T_IMPORT_FILES (
                            DDE_SK       
                          , NAME         
                          , FILENAME     
                          , MIME_TYPE    
                          , CREATED_ON   
                          , BLOB_CONTENT 
                          , SETTINGS_DEF_BY_USER
                          , CREATED_AT
                       ) 
                        values (  T_IMPORT_FILE_seq.nextval -- DDE_SK
                                , rec.NAME                                -- NAME
                                , rec.FILENAME                            -- FILENAME
                                , rec.mime_type                           -- MIME_TYPE
                                , rec.CREATED_ON                          -- CREATED_ON                            
                                , rec.blob_content                        -- BLOB_CONTENT
                                , :APP_USER                               -- SETTINGS_DEF_BY_USER
                                , systimestamp)                           -- CREATED_AT
                        RETURNING DDE_SK INTO v_id;        
                end loop; 
                
                -- clean the tempfile
                begin
                    delete apex_application_temp_files  where name = v_filename;
                    commit;
                EXCEPTION    
                    when others then
                       v_message:=v_message||' clean tempfile '|| SQLCODE || ' - '||SQLERRM||'<br/>';    
                 end;
        end if;
    end if; 
    
    apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
end;
</code>


----


==== Daten mit dem Parser auslesen =====

Eine Report auf der Seite anlegen und mit Hilfe von **apex_data_parser.parse** die Datei parsen und anzeigen.

=== Das passende Sheet aus der Excel Datei auslesen mit apex_data_parser.get_xlsx_worksheets ===

In Excel kann sogar das einzelne Sheet selektiert werden aus dem die Daten geladen werden sollen!

<code sql>
select sheet_display_name, sheet_file_name
  from T_IMPORT_FILES f,
       table( apex_data_parser.get_xlsx_worksheets( p_content => blob_content ) ) p
 where f.name = :P3400_IMPORT_FILE
</code>


=== Bericht anlegen mit apex_data_parser.parse ===

SQL für den Report

<code sql>

select  apex_item.checkbox2(2,line_number) import  -- only if you like to implement a filter for the imported data
    ,  col001
    ,  col002
    ,  col003
    ,  col004
    , col005
    , col006
     -- .. up to 300 columns can be selected
    , col300
 
  from T_IMPORT_FILES f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_xlsx_sheet_name             => :P3400_XLSX_WORKSHEET,
                  p_max_rows                    => 1000,
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = nvl(:P3400_IMPORT_FILE,:P3400_LAST_IMPORTS)
and line_number > 1
</code>

Beispiel für eine reine CSV Datei:

<code sql>
SELECT col001
    ,  col002
    ,  col003
    ,  col004 
  FROM apex_application_temp_files f, 
       TABLE( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_csv_col_delimiter             => '	',      -- hier tab!           
                  p_file_name                     => replace(f.filename,'.tab','.csv') -- muss csv heißen!
           )
       ) p
 WHERE f.name = :P400_LOADED_FILE

</code>

Die Dateiendung ist wichtig für den Parser! 


----


=== Daten aus der Datei in die Zieltabelle laden ===


== Error Log ==

Error Log zuvor auf der Ziel-Tabelle anlegen um Fehler mitschreiben zu können.

<code sql>
-- +====================================================+	
-- Error Log table
 
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'T_EXCLUDE_KDE');
END;
/
</code>

=== Daten laden mit  apex_data_parser.parse ===

Prozess für den Übertrag in die Zieltabelle:
<code sql>

declare
    v_error_log_exists boolean := false;
    v_message varchar2(4000):='Prozessing Data submit to DDE '||'<br/>';
begin
  
    delete from ERR$_T_CSV_DETAILS where ORA_ERR_TAG$ like :APP_USER||'%';
    commit;    
    
    
    
    insert into T_CSV_DETAILS (
                DDE_SK                
              , KUNDENNUMMER           
              , KUNDENNUMMER_2
			  , INSTITUTSZUORDNUNG   
			  , CASENUMBER           
			  , TRIGGER_TYPE  
              , INSERT_AT          
			  , INSERT_REASON      
			  , IS_ACTIVE         
    ) select  T_CSV_DETAILS_seq.nextval
            , col002 as KUNDENNUMMER
            , col003 as KUNDENNUMMER_2
            , col001 as INSTITUTSZUORDNUNG
            , col002as CASENUMBER
            , col006 as  TRIGGER_TYPE   
            , to_date(col005,'yyyy-mm-dd') as INSERT_AT
            , col007 as INSERT_REASON
            , 'Y'    as IS_ACTIVE
      from T_IMPORT_FILES f, 
           table( apex_data_parser.parse(
                      p_content                     => f.blob_content,
                      p_add_headers_row             => 'Y',
                      p_xlsx_sheet_name             => :P3400_XLSX_WORKSHEET,
                      p_max_rows                    => 1000,
                      p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                      p_file_name                   => f.filename ) ) p
     where f.name = :P3400_IMPORTED_FILE
    and line_number > 1  -- not the first line of the dokument!
    log errors into ERR$_T_CSV_DETAILS ( :APP_USER|| ' - ' ||' von ' || to_char(sysdate,'dd.mm.yyyy hh24:mi') ||' - '||:P3400_LOADED_FILE )
    reject limit unlimited;
   
   :P3500_ROWS_LOADED := sql%rowcount;
   
   select count(*) 
     into :P3400_ROWS_FAILED
     from ERR$_T_CSV_DETAILS where ORA_ERR_TAG$ like :APP_USER||'%';
     
   v_message:='Load '||:P3400_ROWS_LOADED||' Records to T_CSV_DETAILS with '||:P3400_ROWS_FAILED||' Errors';

   apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
  
end;


</code>

Beispiel für das Laden einer reinen CSV Datei:

<code sql>
declare
    v_message varchar2(4000):='Merge der Modbus Daten in BMA_MODBUS'||'<br/>';
begin
  
  delete from ERR$_BMA_MODBUS where ORA_ERR_TAG$ like :APP_USER||'%';
  commit;
           
  merge into BMA_MODBUS m 
         using ( select 
                 col001 as TYP
               , col002 as NUMMER
               , col003 as SUB_NUMMER
               , col004 as register
       FROM apex_application_temp_files f, 
         TABLE( apex_data_parser.parse(
                  p_content               => f.blob_content,
                  p_add_headers_row       => 'Y',
                  p_csv_col_delimiter     => '	',                
                  p_file_name             =>  replace(f.filename,'.tab','.csv')  ) 
              ) p
         WHERE f.name = :P400_LOADED_FILE           
         )  i 
         on ( i.TYP=m.typ and i.NUMMER=m.NUMMER and nvl(i.SUB_NUMMER,-1)=nvl(m.SUB_NUMMER,-1)  )        
     WHEN matched
    THEN
        update set m.register=i.register
     WHEN NOT matched
    THEN
       insert ( BMA_MODBUS_ID
            , TYP
            , NUMMER
            , SUB_NUMMER
            , REGISTER 
        ) values ( 
            BMA_MODBUS_seq.nextval
          , i.TYP
          , i.NUMMER
          , i.SUB_NUMMER
          , i.register       
        )
    log errors into ERR$_BMA_MODBUS ( :APP_USER|| ' - ' ||' von ' || to_char(sysdate,'dd.mm.yyyy hh24:mi') ||' - '||:P400_LOADED_FILE )
    reject limit unlimited;
   
   :P400_ROWS_LOADED := sql%rowcount;
   
   commit;
   
   select count(*) 
     into :P400_ROWS_FAILED
     from ERR$_BMA_MODBUS where  ORA_ERR_TAG$ like :APP_USER||'%';
     
   v_message:=v_message||'Konnte '||:P400_ROWS_LOADED||' Zeilen in  BMA_MODBUS mit '||:P400_ROWS_FAILED||' Fehlern laden';

   :P400_LOADED_FILE:=null;


   apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
  
   
end;


</code>

----

----


==== Quellen ====

Carsten Czarski


  * https://blogs.oracle.com/apex/quick-and-easy-data-loading-with-apex-191
  * https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

Web:


  * https://explorer.co.uk/loading-excel-into-apex-19-1/
  * https://www.jmjcloud.com/blog/apex-data-parser-data-load-made-easy