1 Reply Latest reply: Dec 3, 2012 3:47 AM by Rajanjai RSS

    CSV Upload

    Rajanjai
      Hi ,
      Im Using apex 3.2 , I have a table named filetest with columns A,B,C,D of varchar2 datatype .I Need to upload a csv file with two columns in it to filetest table
      for columns selected from checkbox which contains column names of filetest table.Im using the following code but im landed with too many values error .
      Wat Can I Do.

      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_ses NUMBER;
      V_INPUTS VARCHAR2(200);

      BEGIN
      SELECT DS_FILE_UPLOAD_SEQ.nextval into v_ses from dual;

      SELECT REPLACE(:P1_INPUTS,':',',') INTO V_INPUTS FROM DUAL;
      -- Read data from wwv_flow_files</span>
      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;

      -- 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, ',', ':');
      v_line := REPLACE (v_line, '"', '');

      -- Convert each column separated by : into array of data </span>
      v_data_array := wwv_flow_utilities.string_to_table (v_line);
      -- Insert data into target table </span>
      EXECUTE IMMEDIATE 'insert into filetest ('||V_INPUTS||')
      values (:1,:2,:3,:4)'
      USING
      v_data_array(1),
      v_data_array(2),
      v_data_array(3),
      v_data_array(4);
      -- Clear out
      v_line := NULL;
      v_sr_no := v_sr_no + 1;
      END IF;

      END LOOP;
      END;