Read text file from specified range usins pl/sql

This blogs will helps you to read text file from the specified range.

you have a text file like this:

103  smith     20   32000     new jersy

105  Anderson  30   32000     new jersy

108  jack      40   37000     capetown

107  jackie    30   24000     melborn

128  saudi     20   17000     auckland

131  ramesh    40   30000     ghaziabad

123  kalki     50   20000     noida  

153  ashish    50   20000     noida  

First of you should create directory:

CREATE OR REPLACE DIRECTORY DATA_FETCH AS '/usr/bin/bfile_dir';

then provide grant previllage to user:

GRANT read, write ON DIRECTORY DATA_FETCH TO user;

then use following source code:

DECLARE

   l_fileID_r           UTL_FILE.FILE_TYPE;

   l_fileID_w           UTL_FILE.FILE_TYPE;

   l_dirpath         VARCHAR2 (50) := 'DATA_FETCH';

   l_filename_r       VARCHAR2 (1000) := 'emp_details.txt';

   l_filename_w       VARCHAR2 (1000) := 'testwrite.txt';

   l_line_counter NUMBER := 1;

   l_buffer           VARCHAR2(32000);

   num number:= :num;

BEGIN

   /*Open file to read*/

   l_fileID_r := UTL_FILE.FOPEN (l_dirpath, l_filename_r, 'r', 32000);

   /*Open file*/

   l_fileID_w := UTL_FILE.FOPEN (l_dirpath, l_filename_w, 'w', 32000);

      LOOP

         /*Read and output the line until we reach the last line*/

         UTL_FILE.GET_LINE(l_fileID_r, l_buffer, 32000);

         dbms_output.put_line('Line' || l_line_counter ||'   '|| l_buffer);

          l_line_counter := l_line_counter + 1;

          /*PUT_LINE procedure writes the text string stored in the buffer to the open file*/

          if l_line_counter>num then

         UTL_FILE.PUT_LINE(l_fileID_w,l_buffer);

         dbms_output.put_line('Line' || l_line_counter ||'   '|| l_buffer);

         end if;

      END LOOP;

EXCEPTION

   WHEN UTL_FILE.INVALID_PATH THEN

      DBMS_OUTPUT.PUT_LINE('File location is invalid');

   WHEN NO_DATA_FOUND THEN /*EOF Reached*/

      UTL_FILE.fclose(l_fileID_r); /* Close the File Type*/

      UTL_FILE.fclose(l_fileID_w);

      NULL;

END;

now you enter num value and you get following output like this:

107  jackie    30   24000     melborn   

128  saudi     20   17000     auckland  

131  ramesh    40   30000     ghaziabad 

123  kalki     50   20000     noida     

153  ashish    50   20000     noida     

Email ID: kanuj241@gmail.com