How to insert Multiple value with comma seperator into table from csv file

Question

I have created a process code to import the data from CSV File to Apex table.

The following code i have used in process:

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_line_count number := 0;
BEGIN
-- Read data from wwv_flow_files
select blob_content into v_blob_data
 from wwv_flow_files 
 where CREATED_ON = (select max(CREATED_ON) from wwv_flow_files where lower(CREATED_BY) =lower(:APP_USER));

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

-- 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
 v_line_count := v_line_count + 1;
-- 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);
-- Insert data into target table
   IF v_line_count > 1 THEN
   EXECUTE IMMEDIATE 'insert into NON_DYNAMIC_USER_GROUPS_TEMP (ID,WORKSPACES,GROUP_NAME,MEMBERS) values (NON_DYNAMIC_GROUPS_TEMP_SEQ.NEXTVAL,(:1),(:2),(:3))'
   USING  
   v_data_array(1), v_data_array(2), v_data_array(3);    
   end if;
-- Clear out
   v_line := NULL;
  END IF;
 END LOOP;
END;

Here for 3rd column i have to insert multiple values with comma separator from csv file, for ex in v_data_array(3) i have to entered ABINNAYA,BARATH,CHELLA this has to insert in table as ABINNAYA,BARATH,CHELLA.

insert into table is working fine but only the multiple values are not getting inserted if the 3rd column is having multiple values means.

Thanks

Answer 1

There is a much more elegant way to insert CSV data to a table if you use OraOpenSource Utils set of packages.

Using it, you could insert your data into the table using a block code similar to this:

declare 
    v_blob_data blob;
    v_clob_data clob;
  -- variables needed for blob-clob convertion
    v_dest_offset integer := 1;
    v_src_offset integer := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning integer := 0;
    -- one line of the CSV data
    v_line oos_util.tab_vc2_arr;
begin
    -- load CSV data into blob var
    select blob_content into v_blob_data
    from wwv_flow_files 
    where CREATED_ON = (select max(CREATED_ON) from wwv_flow_files where lower(CREATED_BY) =lower(:APP_USER));

    -- converting blob to clob
    dbms_lob.createtemporary(v_clob_data, true);
    dbms_lob.converttoclob(v_clob_data, v_blob_data, DBMS_LOB.LOBMAXSIZE, v_dest_offset, v_src_offset, nls_charset_id('UTF8'), v_lang_context, v_warning);

    -- splitting the CSV to strings and then inserting each row attribute by attribute
    for x in (
        select column_value val from table(oos_util_string.listunagg(p_str => v_clob_data, p_delim => chr(10)))
    ) loop
      v_line := oos_util_string.string_to_table(x.val);
        -- you can apply some datatype conversion here if needed (for example with to_date() in case there are date columns)
      insert into ttt values (v_line(1), v_line(2), v_line(3));
    end loop;
    commit;
end;

First it loads your data into a BLOB variable, then converts in into a CLOB one (mind the codepage) and after it using OOS Utils splits the data into lines first and finally attribute by attribute inserts the data into the target table.

Be accurate with the datatypes conversion when do so.

P.S. In your original post do you really need to use the dynamic SQL to fulfil your task? It does not seem so.

I am not sure if I answered your question, cause it was not totally clear to me what was your problem.