Read Text File from Oracle PL/SQL Program

Read Text File from Oracle PL/SQL Program


PROCEDURE get_file_contents(filename varchar2,Nxt in out number ) IS in_file CLIENT_Text_IO.File_Type;
linebuf VARCHAR2(3000);
c number:=0;
Nxt number:=0;
pipe varchar2(35);
BEGIN

--opening the file in READ ONLY Mode 'r'

Select nvl(max(Record_no),0) into Nxt from Qapadmin.Labdata;
in_file := CLIENT_Text_IO.Fopen(filename, 'r');

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading titles

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading seperators

--navigating to data block

go_block('data');

---clear block if contents required to replaced

clear_block;
first_record;

LOOP

---this logic depends on the structure of ur Text file.....play with linebuf variable.

--I am reading the file as Comma seperated and no of column are known in my example.

Nxt:=Nxt+1;

/*:empid and empname is block's item*/

:empid := ltrim(rtrim(substr(linebuf, 1, instr(linebuf,' ',1,1)-1)));
:empname := ltrim(rtrim(substr(linebuf, instr(linebuf,' ',1,1)+1)));
End if;
C:=C+1;
next_record;
CLIENT_Text_IO.New_Line;--to move to next line in file
END LOOP;
EXCEPTION
WHEN no_data_found THEN ---when no line remains this exception will raise...
CLIENT_Text_IO.Fclose(in_file);
first_record;
END;