GET_LINE procedure - Get a line from a file
The GET_LINE procedure gets a line of text from a specified file. The line of text does not include the end-of-line terminator. When there are no more lines to read, the procedure throws a NO_DATA_FOUND exception.
Syntax
Procedure parameters
- file
- An input argument of type UTL_FILE.FILE_TYPE that contains the file handle of the opened file.
- buffer
- An output argument of type VARCHAR(32672) that contains a line of text from the file.
Authorization
EXECUTE privilege on the UTL_FILE module.
Example
Read through and display the records in the file empfile.csv.
SET SERVEROUTPUT ON@
CREATE PROCEDURE proc1()
BEGIN
DECLARE v_empfile UTL_FILE.FILE_TYPE;
DECLARE v_dirAlias VARCHAR(50) DEFAULT 'empdir';
DECLARE v_filename VARCHAR(20) DEFAULT 'empfile.csv';
DECLARE v_empline VARCHAR(200);
DECLARE v_count INTEGER DEFAULT 0;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLSTATE1 CHAR(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET SQLSTATE1 = SQLSTATE;
SET v_empfile = UTL_FILE.FOPEN(v_dirAlias,v_filename,'r');
loop1: LOOP
CALL UTL_FILE.GET_LINE(v_empfile, v_empline);
IF SQLSTATE1 = 'ORANF' THEN -- NO DATA FOUND
LEAVE loop1;
END IF;
CALL DBMS_OUTPUT.PUT_LINE(v_empline);
SET v_count = v_count + 1;
END LOOP;
CALL DBMS_OUTPUT.PUT_LINE('End of file ' || v_filename || ' - ' || v_count
|| ' records retrieved');
CALL UTL_FILE.FCLOSE(v_empfile);
END@
CALL proc1@
This example results in the following output:
10,CHRISTINE,I,HAAS,A00,3978,1/1/1965,PRES,18,F,8/24/1933,52750,1000,4220
20,MICHAEL,L,THOMPSON,B01,3476,10/10/1973,MANAGER,18,M,2/2/1948,41250,800,3300
30,SALLY,A,KWAN,C01,4738,4/5/1975,MANAGER,20,F,5/11/1941,38250,800,3060
50,JOHN,B,GEYER,E01,6789,8/17/1949,MANAGER,16,M,9/15/1925,40175,800,3214
60,IRVING,F,STERN,D11,6423,9/14/1973,MANAGER,16,M,7/7/1945,32250,500,2580
70,EVA,D,PULASKI,D21,7831,9/30/1980,MANAGER,16,F,5/26/1953,36170,700,2893
90,EILEEN,W,HENDERSON,E11,5498,8/15/1970,MANAGER,16,F,5/15/1941,29750,600,2380
100,THEODORE,Q,SPENSER,E21,972,6/19/1980,MANAGER,14,M,12/18/1956,26150,500,2092
End of file empfile.csv - 8 records retrieved