So the code for copying the blob into a clob and populating the collection can be seen below.
declare --variables to do with the copying the blob into a clob v_blob BLOB; v_clob CLOB; v_dest_offset integer := 1; v_src_offset integer := 1; v_lang_context integer := dbms_lob.default_lang_ctx; v_warning integer; --variables to do with iterating over each row of the clob v_new_line_pos NUMBER; v_start_pos NUMBER := 1; v_current_line varchar2(4000); v_total_len NUMBER; v_curr_row apex_application_global.vc_arr2; --constants c_collection_name constant varchar2(20) := 'COURSE_UNIT_MAPPING'; begin select blob_content into v_blob from apex_application_files where name = :P30_FILE; dbms_lob.createtemporary(v_clob, TRUE); dbms_lob.converttoclob( dest_lob => v_clob, src_blob => v_blob, amount => dbms_lob.lobmaxsize, dest_offset => v_dest_offset, src_offset => v_src_offset, blob_csid => dbms_lob.default_csid, lang_context => v_lang_context, warning => v_warning ); APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => c_collection_name); v_total_len := dbms_lob.getlength(v_clob); while (v_start_pos <= v_total_len) LOOP v_new_line_pos := instr(v_clob, chr(10), v_start_pos); IF v_new_line_pos = 0 THEN v_new_line_pos := v_total_len+1; END IF; v_current_line := substr(v_clob, v_start_pos, v_new_line_pos-v_start_pos); v_curr_row := apex_util.string_to_table(v_current_line, ','); if v_curr_row.COUNT = 2 THEN --dont want to touch it if it doesn't have exactly 2 elements APEX_COLLECTION.ADD_MEMBER( p_collection_name => c_collection_name, p_c001 => v_curr_row(1), p_c002 => v_curr_row(2)); END IF; v_start_pos := v_new_line_pos+1;--need to add one so it doesn't search the same range END LOOP; delete from apex_application_files where name = :P30_BULK_FILE; commit;--I couldn't see the point in keeping this file around since all the data has now been populated into the collection end;
To avoid any issues incase someone tries to upload a bogus csv file, I have made sure there are only 2 elements in the row, before adding them to the collection. Of course, there could be a lot more in-depth error checking, but this is the basics and I feel it will do for the time being. Once I have reviewed what's in the collection, and I am sure I want to upload that data, I can then go on to insert the data into my table.
insert into type_range (type, range) select c001 type, c002 range from apex_collections where collection_name = c_collection_name;
There is a caveat - if you expect there to be a large volume of rows, I would avoid using the collections framework, but rather your own temp table, as collections are too in-efficient with large volumes of data.