Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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.
- Thanks
Add Comment
Please, Sign In to add comment