Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:oracle_apex_5_file_handling

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:oracle_apex_5_file_handling [2018/07/30 12:25] – gpipperrprog:oracle_apex_5_file_handling [2019/10/28 12:33] (aktuell) – gpipperr
Zeile 1: Zeile 1:
 +=====Oracle APEX 5 -  Dateien laden und parsen=====
  
 +**07/2018**
 +
 +<fc #800000>**Aufgabe:**</fc> 
 +
 +Eine CSV Datein in Apex per Weboberfläche laden und in der DB weiter verarbeiten
 +
 +<note>Ab Apex 19.1 kann das nun mit APEX Methoden viel einfacher gelöst werden , Stichwort  APEX_DATA_PARSER https://blogs.oracle.com/apex/quick-and-easy-data-loading-with-apex-191 </note>
 +
 +
 +==== Wie lädt Oracle Apex Dateien? ====
 +
 +Im Standard lädt Oracle APEX 5 Dateien temporär die die Tabelle **apex_application_temp_files**, diese Tabelle hat sich sich mit der Version 5 gegenüber den alten Version geändert (Name etc.).
 +
 +In der Seite mit dem Page Item "File Browse" kann gewählt werden ob die Datei in der Temp Tabelle nach dem Upload gleich wieder gelöscht wieder gelöscht werden soll oder erst beim Ende der Session.
 +
 +Details zu den Fähigkeiten von "File Browse" siehe => https://docs.oracle.com/database/apex-5.1/HTMDB/about-item-types.htm#HTMDB28625
 +
 +D.h. die hoch geladenen Daten müsse für die Weiterverarbeitung aus dieser Tabelle exportiert , weiter kopiert werden. Die Datei kann dazu in der Tabelle über den Dateinamen gefunden werden.
 +
 +Zum Beispiel für eine einzelne Datei:
 +
 +<code plsql>
 +..
 + select count(ID)
 +        into v_file_count
 +        from apex_application_temp_files
 +       where name = :P3100_FILENAME;
 +if v_file_count > 0
 +      then
 +         -- Read data from apex_application_temp_files
 +         select blob_content
 +           into v_blob_data
 +           from apex_application_temp_files
 +          where name = :P3100_FILENAME;
 +          
 +   ...             
 +</code>
 +
 +
 +
 + 
 +
 +----
 +
 +
 +
 +==== Mit dem Oracle APEX Data Loader ====
 +
 +
 +Über den Page Wizard lässt sich ein komfortabler und einfach zu bedienender Data Loader erstellen.
 +
 +Siehe dazu aus der Online Doku: http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/schema/50/Data_Load_Wizard/Data_Load_Wizard.html
 +
 +
 +
 +Fazit: Schöner Wizard mit guten Funktionen, ABER => nur für kleine Dateien geeignet, bei 10MB Dateien ist bereits Schluss.
 +
 +Das liegt aber auch an der Konfiguration der Apex Umgebung (Speicher!!), das lässt sich in Kundenprojekt aber oft nicht so einfach anpassen!
 +
 +
 +----
 +
 +==== Eigenbau Lösung für Images ====
 +
 +Siehe dazu ausführlich hier => [[prog:oracle_multimedia_12c|Multimedia in der Oracle Datenbank 12c - Bildbearbeitung in PL/SQL]]
 +
 +
 +----
 +
 +==== Eigenbau Lösung für CSV Dateien ====
 +
 +Da im aktuellen Projekt schnell eine robustere Lösung für das Laden von CSV Datein gefunden werden muss, nach ein paar Ideen im Internet gesucht.
 +
 +Danke für die ersten Iden von [[ https://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/| Advait Deo ]] und [[ http://oraexplorer.blogspot.com/2007/11/apex-to-upload-text-file-and-write-into.html |   Ittichai ]].
 +
 +Ziel ist es eine beliebige CSV Tabelle in eine Stage Tabelle mit 30 Spalten zu laden, die ersten 30 Spalten werden dabei bei der CSV Datei in die Tabelle geschrieben.
 +
 +Die Daten können auch Separator Zeichen enthalten, das heißt wenn ein Text mit "" umgeben ist, müssen die Separatoren innerhalb dieser Zeichenkette ignoriert werden.
 +
 +
 +=== Ablauf ===
 +
 +  * Leere Seite anlegen - in meine Fall mit der ID 3100
 +  * ITEM vom Typ "File Browse" hinzufügen, ( P3100_FILENAME, wird später im Process referenziert)
 +  * Button für den Submit hinzufügen
 +  * Process mit dem PL/SQL Code anlegen
 +
 +
 +===Code für den Page Submit Process ===
 +
 +
 +Ein Erster Test mit Lesen der Datei zeichen bei Zeichen war nicht erfolgreich, die Performance von **DBMS_LOB.SUBSTR** ist relativ schlecht => siehe  dazu auch https://stackoverflow.com/questions/10331912/performance-of-substr-on-clob  .
 +
 +Daher zerlege ich die Datei in einzelne Zeilen und verarbeitete dann die Zeilen.
 +
 +<code plsql upload_apex.sql>
 +declare
 +   v_blob_data    blob;
 +   v_clob_data    clob;
 +   v_blob_len     pls_integer;
 +   v_clob_len     pls_integer;
 +   v_position     pls_integer;
 +   v_line_end_pos pls_integer;
 +   v_chunk_len    pls_integer;
 +   v_loop_count   pls_integer:=0;
 +   
 +   v_line         varchar2(32767) := null;
 +   v_data_array   wwv_flow_global.vc_arr2;
 +   v_sr_no        number := 1;
 +   v_filename     varchar2(4000);
 +   v_file_count   pls_integer := 0;
 +
 +   v_mime_type    varchar2(512);
 +   v_charset      varchar2(32);
 +   v_char_sperator varchar2(1):=:P1_SEPERATOR ; -- ';';
 +
 +   v_starttime    timestamp := systimestamp;
 +  
 +   v_Seconds      number;
 +   v_rec_count   pls_integer;
 +
 +   v_message      varchar2(4000) := 'Prozessing File Insert' || '<br/>';
 +
 +   --
 +   v_l            pls_integer := 0;
 +   v_rchar        varchar2(10) := '-';
 +   v_rbefore      varchar2(10) := '-';
 +   v_match        boolean := false;
 +
 +
 +   --  PL/SQL function to convert a BLOB to a CLOB
 +
 +   function convertToClob(p_blob_in in blob, p_charset varchar2)
 +      return clob
 +   as
 +      v_clob           clob;
 +      v_dest_offset    number := 1;
 +      v_src_offset     number := 1;
 +      v_lang_context   number := 0;
 +      v_warning        number;
 +   begin
 +      dbms_lob.CREATETEMPORARY(v_clob, true);
 +
 +
 +
 +      dbms_lob.converttoclob(v_clob
 +                            ,p_blob_in
 +                            ,dbms_lob.lobmaxsize
 +                            ,v_dest_offset
 +                            ,v_src_offset
 +                            ,nls_charset_id(p_charset)
 +                            ,v_lang_context
 +                            ,v_warning);
 +
 +      return v_clob;
 +   end convertToClob;
 +   
 +   -- get the runtime information
 +   function getRunTime ( p_starttime timestamp)
 +    return number
 +   is
 +    
 +     v_endtime      timestamp;
 +     v_runtime      interval day to second;
 +      v_Seconds      number;
 +   begin
 +         -- get the runtime of the load process
 +      v_endtime := systimestamp;
 +      v_runtime := v_endtime - p_starttime;
 +
 +      select   extract(day from v_runtime) * 86400
 +          + extract(hour from v_runtime) * 3600
 +          + extract(minute from v_runtime) * 60
 +          + extract(second from v_runtime)
 +      into v_Seconds
 +      from dual;
 +   
 +     return v_Seconds;
 +   
 +  end getRunTime ;
 +   
 +begin
 +   -- check if parameter exits
 +   if :P1_FILENAME is not null
 +   then
 +      v_charset := nvl( :P1_CHARSET, 'WE8ISO8859P15');
 +      v_message :=
 +            v_message
 +         || ' Start load import file '
 +         || :P1_FILENAME
 +         || ' <br/> with the charset '
 +         || :P1_CHARSET
 +         || '<br/>';
 +
 +      -- check if the file exits
 +      select count(ID)
 +        into v_file_count
 +        from apex_application_temp_files
 +       where name = :P1_FILENAME;
 +
 +      -- check if the upload was sucessfull
 +      if v_file_count > 0
 +      then
 +                                
 +         -- clean the local stage table
 +         delete CSV_STAGE_TAB;
 +         commit;
 +
 +         -----------------------------------------------
 +
 +         -- Read data from apex_application_temp_files
 +         select blob_content, mime_type
 +           into v_blob_data, v_mime_type
 +           from apex_application_temp_files
 +          where name = :P1_FILENAME;
 +
 +         -- read the data into a clob
 +         dbms_lob.CREATETEMPORARY(v_clob_data, true);
 +         v_clob_data := convertToClob(p_blob_in => v_blob_data, p_charset => v_charset);
 +         v_blob_len := dbms_lob.getlength(v_blob_data);
 +         v_clob_len := dbms_lob.getlength(v_clob_data);
 +
 +         v_position := 1;
 +
 +         v_message :=
 +            v_message || ' Read ' || to_char(v_blob_len) || ' byte and  ' || to_char(v_clob_len) || ' characters <br/>';
 +            
 +         -- check again if stage table is empty     
 +         -- check that this table is empty
 +         select count(*) into v_rec_count from  CSV_STAGE_TAB;
 +         
 +         if v_rec_count > 0 then
 +            RAISE_APPLICATION_ERROR  (-20101, 'Stage Table is not empty!! Stop processing the file !');
 +         end if;
 +
 +        -- parse the file
 +        while (v_position <= v_clob_len)
 + loop
 +  
 + -- use bigger chunks to avoid problem with substr
 + -- reading on be one is to with DBMS_LOB.substr is very slow!
 +   
 + v_line_end_pos:=DBMS_LOB.instr(v_clob_data,chr(10),v_position,1)+1;
 +   
 + -- if v_line_end_pos is 0 then throw exception
 + --
 + if v_line_end_pos = 0 then
 +   raise_application_error( -20001 , '-- Error :: Can not upload CSV files with linesize >>32767 or without line end chr(10) Char' );  
 + end if;
 +   
 + v_chunk_len:= v_line_end_pos - v_position;
 +   
 + v_line := dbms_lob.substr(v_clob_data , v_chunk_len, v_position );
 +    
 +    -- auf die nächste Position weiterschalten
 + v_position := v_position + v_chunk_len;
 +           
 + --  v_message :=
 + --    v_message
 + -- || ' DEBUG  v_line_end_pos::' ||to_char(v_line_end_pos)||' - v_chunk_len::'||to_char(v_chunk_len)||' - v_position::'||to_char(v_position)||'</br>';
 +         
 +            -- get the lenght
 +            v_l := length(v_line);
 +
 +            -- read the string
 +            for i in 1 .. v_l
 +               loop
 +                  v_rchar := substr(v_line, i, 1);
 +
 +                    if v_rchar ='"' and v_rbefore=v_char_sperator then
 +                       v_match:=true;
 +                    end if;
 +                    if v_rchar =v_char_sperator and v_rbefore='"' then
 +                       v_match:=false;
 +                    end if;
 +
 +                    if  v_rchar =v_char_sperator and v_match 
 +                    then
 +                       v_line:=substr(v_line,1,i-1)||'^'||substr(v_line,i+1,10000);
 +                    end if;    
 +                    v_rbefore:=v_rchar;
 +                    
 +            end loop;
 +
 +            -- replace all "
 +            v_line := replace(v_line, '"', '');
 +
 +            -- Convert comma to : to use the wwv_flow_utilities
 +            v_line := replace(v_line, v_char_sperator, ':');
 +
 +            -- get ',' in text fields back
 +            v_line := replace(v_line, '^', ',');
 +
 + -- Convert each column separated by : into array of data </span>
 +            v_data_array := wwv_flow_utilities.string_to_table(v_line);
 +
 +            -- fill the missing records to get the 30 values
 +            for y in 1 .. 30
 +               loop
 +                if v_data_array.exists(y)
 +                then
 +                     null;
 +                else
 +                    -- I use this strange sign to filter out the data later in this ETL process
 +                     v_data_array(y) := '#';
 +                    --debug
 +                    --v_data_array(29) := v_mime_type;
 +                 end if;
 +               end loop;
 +
 +               -- insert into the stage table  DWH_OPERATION.COMET_CONTRACT_INPUT3@DEDWHOPERATION
 +               insert into CSV_STAGE_TAB(ID
 +                                                  ,IMPORTFILENAME
 +                                                  ,FIELD01
 +                                                  ,FIELD02
 +                                                  ,FIELD03
 +                                                  ,FIELD04
 +                                                  ,FIELD05
 +                                                  ,FIELD06
 +                                                  ,FIELD07
 +                                                  ,FIELD08
 +                                                  ,FIELD09
 +                                                  ,FIELD10
 +                                                  ,FIELD11
 +                                                  ,FIELD12
 +                                                  ,FIELD13
 +                                                  ,FIELD14
 +                                                  ,FIELD15
 +                                                  ,FIELD16
 +                                                  ,FIELD17
 +                                                  ,FIELD18
 +                                                  ,FIELD19
 +                                                  ,FIELD20
 +                                                  ,FIELD21
 +                                                  ,FIELD22
 +                                                  ,FIELD23
 +                                                  ,FIELD24
 +                                                  ,FIELD25
 +                                                  ,FIELD26
 +                                                  ,FIELD27
 +                                                  ,FIELD28
 +                                                  ,FIELD29
 +                                                  ,FIELD30)
 +                    values (v_sr_no
 +                           ,:P1_FILENAME
 +                           ,v_data_array(1)
 +                           ,v_data_array(2)
 +                           ,v_data_array(3)
 +                           ,v_data_array(4)
 +                           ,v_data_array(5)
 +                           ,v_data_array(6)
 +                           ,v_data_array(7)
 +                           ,v_data_array(8)
 +                           ,v_data_array(9)
 +                           ,v_data_array(10)
 +                           ,v_data_array(11)
 +                           ,v_data_array(12)
 +                           ,v_data_array(13)
 +                           ,v_data_array(14)
 +                           ,v_data_array(15)
 +                           ,v_data_array(16)
 +                           ,v_data_array(17)
 +                           ,v_data_array(18)
 +                           ,v_data_array(19)
 +                           ,v_data_array(20)
 +                           ,v_data_array(21)
 +                           ,v_data_array(22)
 +                           ,v_data_array(23)
 +                           ,v_data_array(24)
 +                           ,v_data_array(25)
 +                           ,v_data_array(26)
 +                           ,v_data_array(27)
 +                           ,v_data_array(28)
 +                           ,v_data_array(29)
 +                           ,v_data_array(30));
 +
 +
 +               -- reset line
 +               v_line := null;
 +               -- rember line count
 +               v_sr_no := v_sr_no + 1;
 +
 +               if mod(v_sr_no, 100) = 0
 +               then
 +                  commit;
 +               end if;
 +
 +               -- free the collecio
 +               v_data_array.DELETE;
 +            
 +         end loop;
 +
 +         commit;
 +         
 +         v_seconds:=getRunTime ( p_starttime => v_starttime);
 +   
 +         v_message := v_message || '<br/> Insert into stage ' || to_char(v_sr_no) 
 +                                || ' rows  after '|| to_char(v_Seconds, '999G990D099') 
 +                                || ' Seconds</br>';
 +
 +
 +        
 +      else
 +         v_message := v_message || ' Can not find file with the name :: ' || v_filename;
 +      end if;
 +   end if;
 +
 +   -- get the runtime of the load process
 +   -- get the runtime of the load process
 +   --
 +   v_seconds:=getRunTime ( p_starttime => v_starttime);
 +
 +   v_message := v_message || '<br/> File load needs ' || to_char(v_Seconds, '999G990D099') || ' sec';
 +
 +   apex_application.g_print_success_message := '<span style="color:yellow">' || v_message || '</span>';
 +exception
 +   when others
 +   then
 +      v_message := v_message || ' ' || sqlerrm;
 +      apex_application.g_print_success_message := '<span style="color:red">' || v_message || '</span>';
 +end;
 +</code>
 +
 +Um nun mit dem Upload Pattern eine richtige XLS Datei zu verarbeiten ist dann wohl dieser Code dienlich => https://technology.amis.nl/wp-content/uploads/2013/01/as_read_xlsx10.txt
 +
 +----
 +
 +
 +==== Wieder als CSV auslesen ====
 +siehe => [[prog:plsql_spool_csv_apex|Oracle PL/SQL - APEX - CSV Datei aus der DB mit DBMS_XMLGEN.CONVERT erzeugen]]
 +
 +----
 +
 +==== Quellen ====
 +
 +
 +  * https://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
 +
 +
 +HEX
 +  * http://www.jlcomp.demon.co.uk/faq/base_convert.html
prog/oracle_apex_5_file_handling.txt · Zuletzt geändert: 2019/10/28 12:33 von gpipperr