APEX

Hi All,

I thought I would just throw this out there..be kind.
I have a requirement to build a page that allows the user to upload a excel (well I'll have them save it as a .csv) and upate an existing table in their schema.
I have successfully created a similar page for inserting into an existing table however my update is not working. Then I realized what the Oracle Application Express Tool does that for me on, LOAD DATA page. The feature I like best is that after browsing for the file and clicking next it shows the column header and the row data underneath. I would really like to duplicate the functionality of this page.

I looked into the flows schema found related procedures , for example wwv_flow_load_excel_data,but I'm lost on how to use them.
Is there a way to duplicate the LOAD DATA Page and fit it to meet the users requirement?

Also I have included my coding attempt :

CREATE OR REPLACE PROCEDURE exceltotable2
AS
/******************************************************************************
NAME: exceltotable
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 11/17/2008 1. Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: exceltotable
Sysdate: 11/17/2008
Date and Time: 11/17/2008, 2:02:03 PM, and 11/17/2008 2:02:03 PM
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)

******************************************************************************/
v_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 APEX_APPLICATION_GLOBAL.VC_ARR2;
v_rows NUMBER;
v_asset_id NUMBER;
v_new_location GINOS_LOCATION.ID_LOCATION%TYPE ;
sql_stmt VARCHAR2 (2000);

--delete from data_upld;
BEGIN
-- Read data from wwv_flow_files</span>
SELECT blob_content
INTO v_blob_data
FROM wwv_flow_file_objects$
WHERE NAME = 'F32700/scannedforrdc.csv';
--(used this for testing)

-- WHERE last_updated = (select max(last_updated) from WWV_FLOW_FILE_OBJECTS$ where UPDATED_BY
-- = 'ADMIN')
--and id = (select max(id) from WWV_FLOW_FILE_OBJECTS$ where UPDATED_BY = 'ADMIN');

v_blob_len := DBMS_LOB.getlength (v_blob_data);
v_position := 1;

-- Read and convert binary to char</span>
WHILE (v_position <= v_blob_len)
LOOP
v_raw_chunk := DBMS_LOB.SUBSTR (v_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 </span>
IF v_char = CHR (10)
THEN
-- Convert comma to : to use wwv_flow_utilities </span>
v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data </span>
v_data_array := APEX_UTIL.string_to_table (v_line);

-- Update assets with RDC location
-- First get the current location id of the RDC
-- Next take each serial number from the spreadsheet and update the location and rack details </span>

Select id_location into v_new_location from ginos_location gl, ginos_site gs, ginos_agency ga
where nm_office = 'OCFS Resource Distribution Center' and gl.id_site = gs.id_site
And gl.id_agency = ga.id_agency And ga.nm_agency ='Office of Children and Family Services';

-- test with only the serial number/location
UPDATE GINOS_ASSET SET ID_LOCATION = v_new_location WHERE NM_SERIAL = v_data_array(1);

-- Clear out
v_line := NULL;
v_rows := v_rows + 1;
END IF;
END LOOP;
END;
/

Any advice/assistance is always appreciated
Thanks
Moe