Load data from a flat file into an Oracle table
Post date: 18-May-2010 07:24:52
Introduction
The following procedure will read the file containing the data and load the data into the table. The example contains only three data fields. It can be modified to add more data fields.
[edit]
Steps
PROCEDURE load_example IS log VARCHAR2 (100) := 'username/password'; v_file UTL_FILE.file_type; err_file UTL_FILE.file_type; v_dir VARCHAR2 (50) := '/utl_file_dir/'; l_file VARCHAR2 (100) := 'data_file.txt'; e_file VARCHAR2 (100) := 'err_file.txt'; v_how VARCHAR2 (1) := 'r'; v_text VARCHAR2 (2000); v_size NUMBER := 32767; TYPE data_rec IS RECORD ( field1 VARCHAR2 (10), field2 VARCHAR2 (100), field3 VARCHAR2 (100), val VARCHAR2 (100), rst VARCHAR2 (2000), cnt NUMBER ); l data_rec; BEGIN DBMS_OUTPUT.ENABLE (1000000); DBMS_APPLICATION_INFO.set_client_info ('1'); v_file := UTL_FILE.fopen (v_dir, l_file, v_how, v_size); err_file := UTL_FILE.fopen (v_dir, e_file, 'w', v_size); BEGIN LOOP l := NULL; l.cnt := 0; BEGIN UTL_FILE.get_line (v_file, v_text, v_size); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; v_text := REPLACE (REPLACE (REPLACE (v_text, CHR (13)), CHR (10)), CHR (9) ); l.rst := v_text; WHILE LENGTH (l.rst) > 0 LOOP BEGIN l.cnt := l.cnt + 1; l.val := SUBSTR (l.rst, 1, INSTR (l.rst, ',', 1, 1) - 1); l.rst := SUBSTR (l.rst, INSTR (l.rst, ',', 1, 1) + 1); IF l.cnt = 1 THEN BEGIN l.field1 := l.val; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while processing field1: ' || v_text || ':' || SQLERRM ); EXIT; END; ELSIF l.cnt = 2 THEN BEGIN l.field2 := l.val; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while processing field2: ' || v_text || ':' || SQLERRM ); EXIT; END; ELSIF l.cnt = 3 THEN BEGIN l.field3 := l.val; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while processing field3: ' || v_text || ':' || SQLERRM ); EXIT; END; END IF; IF INSTR (l.rst, ',', 1, 1) = 0 THEN BEGIN l.field3 := l.rst; EXIT; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while processing field3: ' || v_text || ':' || SQLERRM ); EXIT; END; END IF; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while processing line: ' || v_text || ':' || SQLERRM ); EXIT; END; END LOOP; l.field1 := UPPER (LTRIM (RTRIM (l.field1))); l.field2 := UPPER (LTRIM (RTRIM (l.field2))); l.field3 := UPPER (LTRIM (RTRIM (l.field3))); BEGIN INSERT INTO table1 VALUES (l.field1, l.field2, l.field3 ); UPDATE TABLE2 SET fld1 = l.field1 WHERE UPPER (fld2) = l.field2 AND UPPER (fld3) = l.field3; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err while inserting/updating: ' || l.field1 || ':' || l.field2 || ':' || l.field3 || ':' || SQLERRM ); END; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN UTL_FILE.put_line (err_file, 'Err : ' || SQLERRM); END; UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); EXCEPTION WHEN UTL_FILE.invalid_path THEN DBMS_OUTPUT.put_line ('Invalid Path'); UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); WHEN UTL_FILE.invalid_mode THEN DBMS_OUTPUT.put_line ('Invalid Mode'); UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); WHEN UTL_FILE.write_error THEN DBMS_OUTPUT.put_line ('Write Error'); UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); WHEN UTL_FILE.invalid_operation THEN DBMS_OUTPUT.put_line ('Invalid Operation'); UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLCODE || ': ' || SQLERRM); UTL_FILE.fclose (v_file); -- Close File UTL_FILE.fclose (err_file); END load_example;
[edit]
Examples
create table table1( f1 varchar2(100),
f2 varchar2(100),
f3 varchar2(100));
data_file.txt: 1, aaaaaaa, zzzzzzz
2, bbbbbbbb, xxxxxxxxx
3, ccccccccc, yyyyyyy
4, dddddddd, wwwwwwwww
[edit]
Conclusion
No sql loader or oracle 9i is required.