Guest User

Untitled

a guest
Apr 24th, 2018
63
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.72 KB | None | 0 0
  1. The following code i have used in process:
  2.  
  3. DECLARE
  4. v_blob_data BLOB;
  5. v_blob_len NUMBER;
  6. v_position NUMBER;
  7. v_raw_chunk RAW(10000);
  8. v_char CHAR(1);
  9. c_chunk_len number := 1;
  10. v_line VARCHAR2 (32767) := NULL;
  11. v_data_array wwv_flow_global.vc_arr2;
  12. v_line_count number := 0;
  13. BEGIN
  14. -- Read data from wwv_flow_files
  15. select blob_content into v_blob_data
  16. from wwv_flow_files
  17. where CREATED_ON = (select max(CREATED_ON) from wwv_flow_files where lower(CREATED_BY) =lower(:APP_USER));
  18.  
  19. v_blob_len := dbms_lob.getlength(v_blob_data);
  20. v_position := 1;
  21.  
  22. -- Read and convert binary to char
  23. WHILE ( v_position <= v_blob_len ) LOOP
  24. v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
  25. v_char := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
  26. v_line := v_line || v_char;
  27. v_position := v_position + c_chunk_len;
  28. -- When a whole line is retrieved
  29. IF v_char = CHR(10) THEN
  30. v_line_count := v_line_count + 1;
  31. -- Convert comma to : to use wwv_flow_utilities
  32. v_line := REPLACE (v_line, ',', ':');
  33. -- Convert each column separated by : into array of data
  34. v_data_array := wwv_flow_utilities.string_to_table (v_line);
  35. -- Insert data into target table
  36. IF v_line_count > 1 THEN
  37. EXECUTE IMMEDIATE 'insert into NON_DYNAMIC_USER_GROUPS_TEMP (ID,WORKSPACES,GROUP_NAME,MEMBERS) values (NON_DYNAMIC_GROUPS_TEMP_SEQ.NEXTVAL,(:1),(:2),(:3))'
  38. USING
  39. v_data_array(1), v_data_array(2), v_data_array(3);
  40. end if;
  41. -- Clear out
  42. v_line := NULL;
  43. END IF;
  44. END LOOP;
  45. END;
  46.  
  47. 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.
  48.  
  49. Thanks
Add Comment
Please, Sign In to add comment