Following is the script
PROCEDURE load_csv_2_cust_table ( p_file_name IN VARCHAR2, p_file_path IN VARCHAR2 ) IS
l_file UTL_FILE.file_type;
l_file_line VARCHAR2 (4000);
temp_line VARCHAR2(4000);
csv_col VARCHAR(4000);
comma_pos1 NUMBER(5);
colon_pos1 NUMBER(5);
comma_pos2 NUMBER(5);
colon_pos2 NUMBER(5);
temp_str VARCHAR(4000);
table_counter NUMBER :=0;
BEGIN
l_file := UTL_FILE.fopen (p_file_path, p_file_name, 'r',32767);
IF UTL_FILE.is_open (l_file) THEN
LOOP
BEGIN
l_file_line := NULL;
UTL_FILE.get_line (l_file, l_file_line);
-- parse the delimited line
IF l_file_line IS NOT NULL
THEN gv_data_tbl.DELETE;
temp_line := l_file_line;
table_counter := 0;
LOOP
-- if temp line is null , exit out of for loop
IF trim(temp_line) IS NULL then EXIT ; END IF;
comma_pos1 := NVL(INSTR(temp_line,','),0);
colon_pos1 := NVL(INSTR(temp_line,'"'),0);
comma_pos2 := NVL(INSTR(temp_line,',',1,2),0);
colon_pos2 := NVL(INSTR(temp_line,'"',1,2),0);
-- only one column . no comma , colon
IF comma_pos1 = 0 THEN
temp_str := temp_line;
temp_line := null;
END IF;
-- no colon , but comma is there
IF colon_pos1 = 0 AND comma_pos1 <> 0
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;
-- comma and colon , comma comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 > comma_pos1
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;
-- comma and colon , colon comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 < comma_pos1
THEN
temp_str := SUBSTR(temp_line,2,colon_pos2-2);
temp_line := SUBSTR(temp_line,colon_pos2+2);
END IF;
table_counter := table_counter +1;
gv_data_tbl(table_counter):=temp_str;
END LOOP;
INSERT INTO custom_table(col1, col2, col3, col4) VALUES
(gv_data_tbl(1),gv_data_tbl(2),gv_data_tbl(3),gv_data_tbl(4));
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN EXIT;
END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE;
END ;
1 comment:
Nice article and explanation is good,Thank you for sharing your experience on oracle Apps and fusion articles.you have clearly explained about the process thus it is very much interesting and i got more information from your blog.
Oracle Fusion Training Institute
Post a Comment