sql - Match upload spreadsheet columns to table columns -

sql - Match upload spreadsheet columns to table columns -

i have spreadsheet upload alternative in apex application, , have code parses through info , uploads table. trying find way match spreadsheet columns table columns in case user switches columns around. below code utilize parse through data, have been unable figure out how match spreadsheet column1 table column1.

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; v_rows number; v_sr_no number := 1; v_first_line_done boolean := false; begin delete temp_update; -- read info wwv_flow_files</span> select blob_content v_blob_data wwv_flow_files name = :p2_file_upload; /* last_updated = (select max(last_updated) wwv_flow_files updated_by = :app_user) , id = (select max(id) wwv_flow_files updated_by = :app_user); */ v_blob_len := dbms_lob.getlength(v_blob_data); v_position := 1; -- read , convert binary 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 whole line retrieved </span> if v_char = chr(10) -- convert comma : utilize wwv_flow_utilities </span> v_line := replace (v_line, ',', ':'); -- convert each column separated : array of info </span> v_data_array := wwv_flow_utilities.string_to_table (v_line); -- insert info target table </span> execute immediate 'insert temp_update (one,two,three,four,five,six,seven) values (:1,:2,:3,:4,:5,:6,:7)' using 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) ; -- clear out v_line := null; v_sr_no := v_sr_no + 1; end if; end loop; delete wwv_flow_files name = :p2_file_upload; --delete temp_update pcfn 'p%'; --update temp_update set username = replace(username, ' ', ''); end;

can help out this?

thanks steven

i forgot post final code after figured out how @ column order first:

procedure mm_in_parse begin 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; v_rows number; v_sr_no number := 1; v_first_line_done boolean := false; v_error_cd number :=0; v_quote_pos1 number; v_quote_pos2 number; v_enclosed_str varchar2(200); v_errmsg varchar2(4000); begin delete temp_mm_update username = v('p1_user_id'); -- read info wwv_flow_files</span> select blob_content v_blob_data wwv_flow_files name = v('p2_file_upload'); v_blob_len := dbms_lob.getlength(v_blob_data); v_position := 1; -- read , convert binary char</span> while ( v_position <= v_blob_len ) loop --begin 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 whole line retrieved </span> if v_char = chr(10) v_line := replace (v_line, '>', null); v_line := replace (v_line, '<', null); loop --make sure there's replace if instr(v_line, '"', 1, 1) = 0 exit; -- if nil replace, exit loop , don't seek end if; --find position of first , sec quotes in line of text v_quote_pos1 := instr(v_line, '"', 1, 1); v_quote_pos2 := instr(v_line, '"', 1, 2); --extract inner string v_enclosed_str := substr(v_line, v_quote_pos1 + 1, v_quote_pos2 - v_quote_pos1 - 1); --perform replacement v_line := substr(v_line, 0, v_quote_pos1 - 1) || replace(v_enclosed_str, ',', '<') || substr(v_line, v_quote_pos2 + 1); end loop; -- convert comma : utilize wwv_flow_utilities </span> v_line := replace (v_line, ':', null); v_line := replace (v_line, ',', ':'); v_line := replace (v_line, '<', ','); v_line := replace (trim(v_line), '-', null); v_line := replace (trim(v_line), '', null); --v_line := replace (trim(v_line), '"', null); -- convert each column separated : array of info </span> v_data_array := wwv_flow_utilities.string_to_table (v_line); --check see if row of column headers has been parsed through if(v_first_line_done != true)then v_first_line_done := true; --check column order in spreadsheet if(v_data_array(1) '%username%' , v_data_array(2) '%ndn%' , v_data_array(3) '%pcfn%' , v_data_array(4) '%tcn%' , v_data_array(5) '%primary sea/air%' , v_data_array(7) '%customer%') v_error_cd := 0; v_line := null; else v_error_cd := 1; end if; --if first line done , column order right elsif(v_first_line_done = true , v_error_cd = 0 , v_data_array(1) not null) -- insert info target table </span> execute immediate 'insert temp_mm_update (username, rpt_flag, pcfn, tcn, customer) values (:1,:2,:3,:4,:7)' using v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(7); -- clear out v_line := null; v_sr_no := v_sr_no + 1; end if; end if; /*exception when others v_errmsg := sqlerrm; v_errmsg := replace(v_errmsg, 'ora-12899: value big column "apex01"."temp_mm_update".', ''); v_errmsg := replace(v_errmsg, '"', null); v_errmsg := replace(v_errmsg, '(actual:', ' value large. (current length:'); v_errmsg := replace(v_errmsg, 'maximum:', 'maximum length:'); v_errmsg := replace(v_errmsg, 'ora-01438: value larger specified precision allowed column', 'numeric value big column.'); v_errmsg := replace(v_errmsg, 'ora-01722: invalid number', 'invalid value in column expecting number.'); v_errmsg := replace(v_errmsg, 'ora-01858: non-numeric character found numeric expected', 'check date column values non-date value.'); v_errmsg := replace(v_errmsg, 'ora-01830: date format image ends before converting entire input string', 'check date column values.'); v_errmsg := replace(v_errmsg, 'ora-01847: day of month must between 1 , lastly day of month', 'not valid date day.'); v_errmsg := replace(v_errmsg, 'ora-01843: not valid month', 'not valid date month.'); v_errmsg := replace(v_errmsg, 'ora-01861: literal not match format string', 'invalid value format.'); insert temp_ndn_update (username,pcfn,tcn,error_desc) values (v('p1_user_id'), v_data_array(3),v_data_array(4), v_errmsg); v_line := null; v_sr_no := v_sr_no + 1; end;*/ end loop; delete wwv_flow_files name = v('p2_file_upload'); if(v_error_cd = 1) insert temp_mm_update (username, error_desc) values (v('p1_user_id'), 'error. please check column order in spreadsheet.'); end if; /*exception when no_data_found insert temp_mm_update (username,error_desc) values (v('p1_user_id'), 'no info found.');*/ end;

sql oracle upload spreadsheet apex