How to write into a text file from PL/SQL - Oracle

Until now, we have read text files using PL/SQL and copied that information into Oracle tables.  Now, we shall take a look at writing to text files using PL/SQL.  Let us first go through the following program.

declare
    f utl_file.file_type;
    s varchar2(200) := 'this is some info';
begin
    f := utl_file.fopen('SAMPLEDATA','sample2.txt','W');
    utl_file.put_line(f,s);
    utl_file.fclose(f);
end;

You can observe that there is not much difference apart from the following line:

utl_file.put_line(f,s);

Instead of using “get_line,” I am now using “put_line.”  You should also observe that I placed some string within the variable “s.”  As we are writing to the text file, we cannot use “R” anymore.  Instead we need to replace it with “W” (which stands for “Write” mode).

If no file exists in the host operating system, PL/SQL runtime automatically creates the text file (with the same file name we specify) for us and starts writing into that.  If the same file already exists in the operating system, then it erases the entire contents of that file and makes it a new one.  Therefore, you should be a bit careful when using this.

There exists one more mode, “A.” This generally stands for the “Append” mode.  It is very similar to the “W” mode except that it would never remove existing information from the text file.  In some cases, this is the best way to write (or add) information to the text file.