Writing to Text Files in Oracle PL/SQL

This is the second article in a series focusing on file input/output using Oracle PL/SQL packages.

If you are new to working with UTL_FILE, I strongly suggest you go through my first article in this series.  It gives you all the necessary steps for creating and developing PL/SQL with UTL_FILE for beginners.

How to read multiple lines from the text file using PL/SQL

In the previous article, we have seen how to work with only the first line in the text file. But no one would ever be happy working with only a single line.  In this section, we shall work on more than one line (or read all lines) from the text file.

We will also need to work with exceptions in this article.  If you are new to exception handling in PL/SQL, I strongly suggest you refer to my contributions at:

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Predefined-Exceptions/

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-User-
defined-Exceptions-Nested-Blocks/

Now, let us follow the sample code:

declare
    f utl_file.file_type;
    s varchar2(200);
begin
    f := utl_file.fopen(‘SAMPLEDATA’,’sample1.txt’,’R’);
    loop
        utl_file.get_line(f,s);
        dbms_output.put_line(s);
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
end;

Since I explained much of the above program in my previous article, I don’t want to go through every line.  The most important issue from the above program is that I used “utl_file.get_line” in an infinite loop (a loop that would never finish). 

When does the loop terminate?  It all depends on the “exception.”  When the PL/SQL run-time successfully reads a line from the text file, it simply places that information into the variable “s” (which gets displayed directly onto the screen). 

When it is unable to read any more lines from the text file, it simply raises an exception (NO_DATA_FOUND), which gets handled in the “exception” section.  Once an exception gets raised, the control will automatically terminate the loop abnormally.

{mospagebreak title=How to count the number of lines from the text file using PL/SQL}

In the previous section, you learned how to display all the lines available in a text file.  Now, let us see how to count the number of lines in a text file.  I modified the program in the previous section to meet the new requirement.  The program is as follows: 

declare
    f utl_file.file_type;
    s varchar2(200);
    c number := 0;
begin
    f := utl_file.fopen(‘SAMPLEDATA’,’sample1.txt’,’R’);
    loop
        utl_file.get_line(f,s);
        dbms_output.put_line(s);
        c := c + 1;
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
        dbms_output.put_line(‘Number of lines: ‘ || c);
end;

According to the above program, I am defining a simple variable to maintain the count.  That variable always gets incremented within the loop, thus maintaining the number of lines read!

You can also observe that I am handling the last part of my program only in the “exception” area.  The exception part gets executed only when no lines are available to read.  In my upcoming articles, I shall explain how to deal with these types of scenarios very professionally.  Till then, I shall continue in this fashion.

{mospagebreak title=How to copy the information from a text file into a table using PL/SQL}

In the previous section, we simply counted the number of lines available in a text file.  Now, let us further extend the same program by transferring the information available in the text file into an Oracle database using PL/SQL.

Before going to the PL/SQL program, we need to create a simple table named “sampletable” as follows:

create table SampleTable
(
    remarks varchar2(200)
)

You can understand that I created a table “SampleTable” with only one column, “remarks” (max. up to 200 chars).  Now, we need to insert rows into that table using PL/SQL.

The PL/SQL code to achieve the desired is the following:

declare
    f utl_file.file_type;
    s varchar2(200);
    c number := 0;
begin
    f := utl_file.fopen(‘SAMPLEDATA’,’sample1.txt’,’R’);
    loop
        utl_file.get_line(f,s);
        insert into sampletable values (s);
        c := c + 1;
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
        dbms_output.put_line(‘No. of rows inserted : ‘ || c);
end;

The only extra statement I used from the above statement is the “INSERT” statement.  You may also use the statement “COMMIT” either at the INSERT statement or after you close the file handle (in the exception section).  I just left it without committing.  But it is recommended that you commit once you finish the work. 

{mospagebreak title=How to write into a text file from PL/SQL}

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.

{mospagebreak title=How to copy from a table into the text file from PL/SQL}

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.”

{mospagebreak title=Other ways to write the information into a text file from PL/SQL}

Until now we have seen how to read and write text files from within PL/SQL.  This section simply gives you a few more tips on writing text files efficiently.

Everyone by now would know what the “utl_file.put_line” is doing.  It simply adds a new line to the text file.  In fact, it types the entire line that we asked into the text file and also writes a “carriage return line feed” character as well.  Put simply, the “carriage return line feed” character is very similar to the character of the “enter” key, when you press it in Notepad. 

We also have another option we can use to write into the text file, which is “utl_file.put.”  It is very similar to the “utl_file.put_line” except that it does not write a “carriage return line feed” character any more.  This can be helpful if you wanted to write a single line to the text file from multiple PL/SQL statements.  You can even manually go to the next line using “utl_file.new_line.”

All the examples in this article have been tested only with Oracle 10g version 2 on Windows Server 2003 standard edition. The examples would be a bit different in  non-Windows environments.  I request that you refer to your DBA or manuals covering the specific platform on which you are going to work. 

I am confident enough that all of the examples should give no problem when worked with Oracle version 7.3 or above (on Windows).  You can drop (or post) me a line if you do experience an issue.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any change.

In my upcoming article I may also introduce you to some of the professional ways to deal with the same issues.  Don’t forget to keep an eye on this website for the next article (or simply sign up for a newsletter).  Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

[gp-comments width="770" linklove="off" ]

chat