This script can be used to read data from a flat file and insert into a table.
UTL_FILE Read
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number) AS v_line VARCHAR2(2000); — Data line read from input file v_file UTL_FILE.FILE_TYPE; — Data file handle v_dir VARCHAR2(250); — Directory containing the data file v_filename VARCHAR2(50); — Data filename v_1st_Comma number; v_2nd_Comma number; v_3rd_Comma number; v_4th_Comma number; v_5th_Comma number; v_empno sample_emp.empno%type; v_ename sample_emp.ename%type; v_job sample_emp.job%type; v_mgr sample_emp.mgr%type; v_hiredate sample_emp.hiredate%type; –v_sal sample_emp.sal%type; BEGIN v_dir := ‘/usr/tmp’; v_filename := ‘sample.dat’; v_file := UTL_FILE.FOPEN(v_dir, v_filename, ‘r’); — ——————————————————– — Loop over the file, reading in each line. GET_LINE will — raise NO_DATA_FOUND when it is done, so we use that as — the exit condition for the loop. — ——————————————————– LOOP BEGIN UTL_FILE.GET_LINE(v_file, v_line); EXCEPTION WHEN no_data_found THEN exit; END; — ———————————————————- — Each field in the input record is delimited by commas. We — need to find the locations of the two commas in the line, — and use these locations to get the fields from v_line. — ———————————————————- v_1st_Comma := INSTR(v_line, ‘,’ ,1 , 1); v_2nd_Comma := INSTR(v_line, ‘,’ ,1 , 2); v_3rd_Comma := INSTR(v_line, ‘,’ ,1 , 3); v_4th_Comma := INSTR(v_line, ‘,’ ,1 , 4); v_5th_Comma := INSTR(v_line, ‘,’ ,1 , 5); v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1)); v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1); v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1); v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1)); v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),’DD-MON-YYYY’); — v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),’99999′); DBMS_OUTPUT.PUT_LINE(v_empno ||’ ‘|| v_ename || ‘ ‘ || v_job || ‘ ‘ || v_mgr ||’ ‘ || v_hiredate); — —————————————— — Insert the new record into the DEPT table. — —————————————— INSERT INTO sample_emp VALUES (v_empno,v_ename,v_job,v_mgr,v_hiredate); END LOOP; UTL_FILE.FCLOSE(v_file); COMMIT; END; |