Read whole file into a string using utl_file in PL/SQL

declare

  V_FileHandle  utl_file.file_type;

  V_FileDirName VARCHAR2(100);

  V_FileName    VARCHAR2(100);

  V_LineString  CLOB;

  V_OutString   CLOB;

begin

  V_FileDirName  := 'DirName';

  V_FileName := 'FileName.txt';

  V_FileHandle := utl_file.fopen(V_FileDirName, V_FileName, 'R');

  loop

    begin

      utl_file.get_line(V_FileHandle, V_LineString);

   

      V_OutString := V_OutString || V_LineString || chr(10);

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        EXIT;

    end;

  end loop;

  --dbms_output.put_line(V_OutString);

  utl_file.fclose(V_FileHandle);

end;

--Create directory in  database server

create directory DirName as 'D:\Test';

grant read on directory DirName to dba;

grant write on directory DirName to dba;

'D:\Test' is a file path on the Server, where the file is located.

--Drop directory from database server

drop directory DirName;

--To Get file directory name run following query

SELECT * FROM ALL_DIRECTORIES

--Note

You require DB Administrator right or DB Administrator has given you a right to access the file directory path from database server to read the file.

If you don't have right, you will get the error "invalid directory path"

You can use other appropriate data type instead of CLOB, If your file data is lesser to read.

Chr(10) is use for New Line.