We have already seen how to write to files from the previous section.  Now, we shall see how to copy information from a table into the text file. 

Let us go through the following code first:

declare
    f utl_file.file_type;
begin
    f := utl_file.fopen('SAMPLEDATA','sample2.txt','W');
    for s in (select remarks from sampletable)
    loop
        utl_file.put_line(f,s.remarks);
    end loop;
    utl_file.fclose(f);
end;

For the above code, I am using the same table defined in the previous sections.  Let us go through the most important bits of the above program.

for s in (select remarks from sampletable)

The above statement goes through every row in the table “sampletable” and retrieves the value of the column “remarks” and places it in the variable “s.”  The variable “s” would contain a new value for every iteration of the “for” loop.  Further proceeding, we have the following:

utl_file.put_line(f,s.remarks);

The above statement is a bit different from any of the previous ones.  You must consider that “s” is no longer a simple “varchar2” variable.  It is being managed by PL/SQL automatically.  And “s” alone cannot maintain a single value.  In fact, it maintains a RECORD type of value.  That means the variable “s” may contain more than one column based on the SELECT statement we provide.  And thus to retrieve any value from “s,” we need to specify the column value also along with the variable “s.”