Skip to main content

Memproses CSV yg di upload menggunakan APEX

Setelah mencari-cari cara untuk memenuhi kebutuhan seperti judul blog ini beberapa waktu,...

Akhirnya pencarian mendapatkan setitik pencerahan dari 2 link berikut:

http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
dan
http://application-express-blog.e-dba.com/?p=1728

Dari 2 tips tersebut tampaknya jika langsung memproses binary file XLS (MS Excel) belum berhasil tetapi karena kebutuhan nya sudah bisa dicukupi dgn mengubah format file ke CSV (Coma Separated Value), akhir Saya putuskan untuk mencobanya.

Mengenai bagaimana menggunakan fitur dasar File Upload dgn APEX bisa dipelajari di sini.

Sekarang yg akan dijelaskan adalah post upload process dari APEX tersebut.

Semua file yg diupload dgn APEX akan tersimpan di tabel APEX_APPLICATION_FILES yg merupakan alias/synonym ke tabel WWV_FLOW_FILES.

Struktur Tabel APEX_APPLICATION_FILES:
SQL> desc apex_application_files
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FLOW_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(90)
FILENAME VARCHAR2(400)
TITLE VARCHAR2(255)
MIME_TYPE VARCHAR2(48)
DOC_SIZE NUMBER
DAD_CHARSET VARCHAR2(128)
CREATED_BY VARCHAR2(255)
CREATED_ON DATE
UPDATED_BY VARCHAR2(255)
UPDATED_ON DATE
LAST_UPDATED DATE
CONTENT_TYPE VARCHAR2(128)
BLOB_CONTENT BLOB
LANGUAGE VARCHAR2(30)
DESCRIPTION VARCHAR2(4000)
FILE_TYPE VARCHAR2(255)
FILE_CHARSET VARCHAR2(128)

SQL>
Insight yg didapatkan adalah:

Jika CSV file yang diupload tersebut (tersimpan dalam bentuk BLOB di kolom BLOB_CONTENT) dibaca baris demi baris, kemudian dipindahkan kedalam array (dalam PL/SQL istilahnya Collections) setelah itu dimasukkan kedalam tabel tujuan dengan mengambil nilai Array tersebut tentunya bisa dilakukan bukan? Hanya saja sudah barang tentu harus dalam format yg Common dan terbuka seperti CSV.
Dengan contoh dan pola yg dijelaskan oleh 2 tips diatas,... Here is what I've acomplished,..

Target Table dari Upload CSV adalah MSGP_PURCHASEITEM_ALL dengan struktur sbb:


XLS File yg disesuaikan dengan Target Table seperti berikut ini:



APEX Page yang disiapkan (belum dirapihkan jadi jangan dikomentari mengenai kualitas layout)


Dan hasilnya adalah,....


Untuk bagaimana handling back end dan Behind the Scene Scenario nya,... read my next post.

Comments

Popular posts from this blog

Configure Reverse Proxy Apache 2 for Oracle APEX 4.0

Just taking note reminder for myself, it's about how to configuring Apache 2 HTTP Server (Not OHS come with Compl CD of Oracle Database) as a front end HTTP Server for APEX 4.0 installed in Oracle XE 10g. Just follow the step as explain below: Install Apache 2.2 Server (Source can be downloaded from http://httpd.apache.org/download.cgi) installed with listening port configure at 8888 Install Oracle XE 10g Database (Download from http://www.oracle.com/technetwork/database/express-edition/downloads/index.html) installed with listening port configure at 7777 Install APEX 4.0 (Download from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html) After those development tools installed last thing to do is Configure Apache2 to become front server and static images server for APEX 4.0 then do the following steps: Enable/Activate module mod_proxy, proxy_http_module with LoadModule Directive. Configuration example: LoadModule proxy_module /usr/

Intel 3945 Wifi Card Problem Easy Troubleshoot in Ubuntu 10.10

Recently just change my mind to switch back OS to pure Ubuntu (not its derivative). I've been using Mint version of Ubuntu derivative for 2 years. Gradually move with upgrade from Mint 5, Mint 8 to Mint 9 (KDE). Last upgrade (Mint 9 KDE) was to 64 Bit version about 8 months ago. Today just upgrade to 10.10 Ubuntu and find out that I couldn't access office wireless network. After googling, I find out a simple solution from this links So just type this in your terminal: sudo rfkill unblock wifi Type 'dmesg | grep iwl' to see the result that should inform something like this: iwl3945 0000:07:00.0: loaded firmware version 15.32.2.9 And of course available Wifi Network now should appear in Network Manager applet drop down. See picture below for more verbose outcome. After this then I'll applied PAE Kernel to utilized unused 1 GB RAM in my laptop, since 32-bit linux without PAE can only used maximum 3 GB of RAM (mine is 4 GB),... hehehe I'm back to 32-bit world again,

Behind The Scene of Processing CSV in Oracle Database using PL/SQL

Melanjutkan postingan sebelumnya: Memproses CSV yg di upload menggunakan APEX dimana penjelasan back end process dan behind the scene Saya coba paparkan semudah mungkin. Langkah-langkah mengkonversi konten CSV menjadi baris-baris tabel di Oracle Database bisa dilakukan dengan tahapan berikut init. Pasang dulu 2 fungsi yang sangat dibutuhkan yaitu: Fungsi F_CSV_CONVERT_TO_TABLE Fungsi HEX_TO_DECIMAL Fungsi F_CSV_CONVERT_TO_TABLE (Detail) CREATE OR REPLACE FUNCTION "F_CSV_CONVERT_TO_TABLE" ( p_in_string IN VARCHAR2 , p_in_encapsulator IN VARCHAR2 DEFAULT '"' ) RETURN wwv_flow_global.vc_arr2 AS l_string VARCHAR2(32767) := p_in_string || ','; l_quote_start_index PLS_INTEGER := 0; l_quote_end_index PLS_INTEGER := 0; l_comma_index PLS_INTEGER; l_index PLS_INTEGER := 1; l_tab wwv_flow_global.vc_arr2; i PLS_INTEGER := 1; BEGIN LOOP l_comma_index := REGEXP_INSTR(l_string, '[,&#