APEX

Hello,

Can anyone please help me out.

I have created a page with browse item and a button which runs a plsql process. The plsql process basically imports the CSV data into a table emp1. When I tried executing the process through the submit button by selecting a file; it show an error message no data found. I checked the view wwv_flow_files, the file is not getting uploaded to this table.

http://apex.oracle.com/pls/apex/f?p=63967:7

DECLARE
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      wwv_flow_global.vc_arr2;
l_cnt             BINARY_INTEGER := 0;
l_stepid          NUMBER := 10;

BEGIN
-- Read data from wwv_flow_files  

select blob_content into v_blob_data  
from wwv_flow_files  
where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)  
and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);  

v_blob_len := dbms_lob.getlength(v_blob_data);  

v_position := 1;  

--Evaluate and skip first line of data
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
IF v_char = CHR(10) THEN
-- Clear out  
v_line := NULL;
EXIT;
END IF;
END LOOP;


-- Read and convert binary to char
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
IF v_char = CHR(10) THEN
-- Convert comma to : to use wwv_flow_utilities
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);

EXECUTE IMMEDIATE 'insert into EMP1(Empno,Ename,Job,Hiredate,sal,comm,deptno,comments)  
values (:1,:2,:3,:4,:5,:6,:7,:8)' 
USING
TRIM(v_data_array(1)),
TRIM(v_data_array(2)),
TRIM(v_data_array(3)),
to_date(TRIM(v_data_array(4)),'DD-MON-RR'),
TRIM(v_data_array(5)),
TRIM(v_data_array(6)),
TRIM(v_data_array(7)),
TRIM(v_data_array(8));

-- Clear out  
v_line := NULL;  
l_cnt := l_cnt + SQL%ROWCOUNT;
END IF;
END LOOP;

--delete from wwv_flow_files  
--where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)  
--and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
COMMIT;
l_stepid  := 20;
IF l_cnt = 0 THEN
apex_application.g_print_success_message := apex_application.g_print_success_message || '<p><span style="font-size:14;font-weight:bold">Please select a file to upload.</span></p>' ;
ELSE
apex_application.g_print_success_message := apex_application.g_print_success_message || '<p><span style="font-size:14;font-weight:bold;color:green">File uploaded and processed ' || l_cnt || ' record(s) successfully.</span></p>';
END IF;
l_stepid  := 30;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
apex_application.g_print_success_message := apex_application.g_print_success_message || '<p><span style="font-size:14;font-weight:bold;color:red">Failed to upload the file. '||REGEXP_REPLACE(SQLERRM,'[('')(<)(>)(,)(;)(:)(")('')]{1,}', '') ||'</span></p>';
END;
{code}