Read Data from text file using UTL_FILE in PL/SQL

Using UTL_FILE you can read the data from text/csv file in PL/SQL. You can follow the below steps to read the data from text file .

Step 1:
Prepare a text file which is delimited by comma (,)

Step 2:
Find the valid UTL directory file path using below script.
SELECT value FROM v$parameter WHERE name = 'utl_file_dir';

Step 3:
Place the text file at server location. The patch would be the valid patch which you get from Step 2.

Step 4:
Write the below PL/SQL block to read the data from text file. You can create procedure also to perform this. (To copy the code select+ctrl+C)
DECLARE f_line VARCHAR2(2000); f_type utl_file.file_type;--declare utl file type variable f_dir VARCHAR2(250); fname VARCHAR2(50); Comma1 VARCHAR(10); Comma2 VARCHAR(10); l_id VARCHAr2(10); l_val VARCHAr2(10); BEGIN --Directory path where the file will be located f_dir := '/usr/tmp'; --file name with file extention fname := 'test_file.txt'; --open the file in read mode f_type := utl_file.fopen(f_dir,fname,'r'); dbms_output.put_line('The data in text file'); LOOP BEGIN --Get each line of the file utl_file.get_line(f_type,f_line); EXCEPTION WHEN no_data_found THEN EXIT; END; --Get the posistion & occurence of the , delemeter Comma1 := INSTR(f_line, ',' ,1 , 1); Comma2 := INSTR(f_line, ',' ,1 , 2); --Fetch the values of each field from the line l_id :=to_number(SUBSTR(f_line, 1, Comma1-1)); l_val := SUBSTR(f_line, Comma1+Comma2+1); --you can perform DML operations . Here we are just displaying the data dbms_output.put_line(l_id||'=>'||l_val); --delete from test_csv where id=l_id; END LOOP; utl_file.fclose(f_type); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('error'); utl_file.fclose(f_type); ROLLBACK; END;

Output: