Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:oracle_apex_5_file_handling

Oracle APEX 5 - Dateien laden und parsen

07/2018

Aufgabe:

Eine CSV Datein in Apex per Weboberfläche laden und in der DB weiter verarbeiten

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

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:

..
 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;
 
   ...             

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

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 Advait Deo und 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.

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;

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

Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
"Autor: Gunther Pipperr"
prog/oracle_apex_5_file_handling.txt · Zuletzt geändert: 2019/10/28 12:33 von gpipperr