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, '[,'||p_in_encapsulator||']', l_index);
EXIT WHEN l_comma_index = 0;

CASE
  WHEN SUBSTR(l_string,l_comma_index,1) = p_in_encapsulator AND l_quote_start_index = 0 THEN
    l_quote_start_index := l_comma_index;
  WHEN SUBSTR(l_string,l_comma_index,1) = p_in_encapsulator AND l_quote_start_index <> 0 THEN
    l_quote_end_index   := l_comma_index;
  ELSE
    NULL;
END CASE;

IF l_quote_start_index = 0 THEN

  l_tab(i) := SUBSTR(l_string, l_index, l_comma_index - l_index);
  i := i+1;

ELSIF l_quote_start_index <> 0 AND l_quote_end_index <> 0 THEN

  l_tab(i) := SUBSTR(l_string, l_quote_start_index+1, (l_quote_end_index - l_quote_start_index)-1);
  i := i+1;
  --
  -- Lets reset our quote check
  --
  l_quote_start_index := 0;
  l_quote_end_index   := 0;
  --
  -- We need to discard our end comma
  --
  l_comma_index := INSTR(l_string, ',', l_index);
END IF;Fungsi HEX_TO_DECIMAL
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END f_csv_convert_to_table;

Fungsi HEX_TO_DECIMAL (Detail)

CREATE OR REPLACE FUNCTION "HEX_TO_DECIMAL"
--this function is based on one by Connor McDonald
--http://www.jlcomp.demon.co.uk/faq/base_convert.html
( p_hex_str in varchar2 ) return number
is
v_dec   number;
v_hex   varchar2(16) := '0123456789ABCDEF';
begin
v_dec := 0;
for indx in 1 .. length(p_hex_str)
loop
v_dec := v_dec * 16 + instr(v_hex,upper(substr(p_hex_str,indx,1)))-1;
end loop;
return v_dec;
end hex_to_decimal;

Process Handler yang dipasang pada salah satu APEX page seperti pada gambar dibawah ini (event tombol "Finish" diklik):

detail prosesnya adalah sbb:

declare
l_mime_type APEX_APPLICATION_FILES.mime_type%type;
l_doc_size  APEX_APPLICATION_FILES.doc_size%type;
l_blob_data       BLOB;

v_blob_len        NUMBER;
v_position        NUMBER;
v_raw_chunk       RAW(10000);
v_char      CHAR(1);
c_chunk_len   number       := 1;
v_line        VARCHAR2 (32767)        := NULL;
v_data_array      wwv_flow_global.vc_arr2;
v_rows number;
v_sr_no number := 1;

begin

select
mime_type, doc_size, blob_content
into
l_mime_type, l_doc_size, l_blob_data
from  APEX_APPLICATION_FILES
where id = :P3_ITEM1;

  -- Just CSV Format will be process
if l_mime_type = 'text/csv' and l_doc_size > 0 then

v_blob_len := dbms_lob.getlength(l_blob_data);
v_position := 1;

-- Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(l_blob_data,c_chunk_len,v_position);
v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));
v_line := v_line || v_char;
v_position := v_position + c_chunk_len;
-- When a whole line is retrieved
IF v_char = CHR(10) THEN
v_data_array := f_csv_convert_to_table (v_line);

-- For Debugging purpose!
dbms_output.put_line( 
 '1#' || v_data_array(1) ||'#' || 
 '2#' || v_data_array(2) ||'#' ||
 '3#' || v_data_array(3) ||'#' ||
 '4#' || v_data_array(4) ||'#' ||
 '5#' || v_data_array(5) ||'#' ||
 '6#' || v_data_array(6) ||'#' ||
 '7#' || v_data_array(7) ||'#' ||
 '8#' || v_data_array(8)
);

-- Insert data into target table
EXECUTE IMMEDIATE '
INSERT INTO MSGP_PURCHASEITEM_ALL
( ID, ITEM_NUMBER, ITEM_DESCRIPTION, ITEM_TYPE,
VALUATION_METHOD, U_OF_M_SCHEDULE,
DECIMAL_PLACES_CURRENCY, DECIMAL_PLACES_QTYS,
INVENTORY_ACCOUNT_NUMBER
)
VALUES
( :1,
  :2,
  :3,
  :4,
  :5,
  :6,
  :7,
  :8,
  :9
)'
USING
v_sr_no,
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);

-- Clear out
v_line := NULL;
v_sr_no := v_sr_no + 1;

END IF;
END LOOP;

      -- Seharusnya di Archive bukan didelete
      delete APEX_APPLICATION_FILES where id = :P3_ITEM1;
      --
end if;

exception
when others then
null;
--    dbms_output.put_line(sqlerrm);
end;

Implementasi utama pada processing CSV tersebut ada pada baris ke-54 s/d baris ke-81,
dimana terlihat bagaimana Array (Collections) ditranspose dan dijadikan argumen dalam DDL INSERT.

Jika memahami bagaimana import Workspace & Application di dalam Oracle APEX silakan download prototype aplikasi ini disini

Mudah-mudahan bisa memberikan pencerahan pada yg mencari solusi yg sama.