Oracle

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;