=====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 ====
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.
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' || '
';
--
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
|| '
with the charset '
|| :P1_CHARSET
|| '
';
-- 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
';
-- 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)||'';
-- 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
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 || '
Insert into stage ' || to_char(v_sr_no)
|| ' rows after '|| to_char(v_Seconds, '999G990D099')
|| ' Seconds';
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 || '
File load needs ' || to_char(v_Seconds, '999G990D099') || ' sec';
apex_application.g_print_success_message := '' || v_message || '';
exception
when others
then
v_message := v_message || ' ' || sqlerrm;
apex_application.g_print_success_message := '' || v_message || '';
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 ====
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