oracle - PL/SQL Get last line in file, avoid looping -

i have simple oracle database , logfile. each row in logfile unique. there lot of lines (~1 million). need use last line in file part of insert statement.

getting file works fine:

f1 := utl_file.fopen('user_dir','log.txt','r');  

putting inside loop works each row in file unique know if have added line yet or not:

loop begin utl_file.get_line(f1,vx);  -- action exception when no_data_found exit; end end loop; 

but seems horribly inefficient going read every line in file though know care last line.

something avoided loop , went:

utl_file.get_last_line(f1,vx) -- action 

would great. i'm sure there construct or can't find it. it's oracle11g if matters.

does article help?

utl_file - random access of files

the fgetattr procedure allows check file exists , return file length. read first line using get_line procedure normal. last line need skip end of file using fseek procedure , work backwards until hit line terminator. get_line procedure not return line terminators detect it's presence checking return of empty line. can display last line.

set serveroutput on size 1000000 declare   l_file         utl_file.file_type;   l_location     varchar2(100) := 'my_docs';   l_filename     varchar2(100) := 'temp';   l_exists       boolean;   l_file_length  number;   l_blocksize    number;   l_text         varchar2(32767); begin   utl_file.fgetattr(l_location, l_filename, l_exists, l_file_length, l_blocksize);    if l_exists     -- open file.     l_file := utl_file.fopen(l_location, l_filename, 'r', 32767);      -- read , output first line.     utl_file.get_line(l_file, l_text, 32767);     dbms_output.put_line('first line: |' || l_text || '|');     utl_file.fseek (l_file, l_file_length-1);      -- step backwards through file until reach start of last line.     in reverse 0 .. l_file_length-2 loop       utl_file.fseek (l_file, null, -2);       utl_file.get_line(l_file, l_text, 1);       exit when l_text null;     end loop;      -- read , output last line.     utl_file.get_line(l_file, l_text, 32767);     dbms_output.put_line('last line : |' || l_text || '|');      -- close file.     utl_file.fclose(l_file);   end if; end; /