Reading Text Files using Oracle PL/SQL and UTL_FILE - How to read a text file from a file system and display the contents using PL/SQL: discussion (Page 3 of 4 )
In this section, I shall explain the PL/SQL program given in the previous section. Let us go through it bit by bit:
f utl_file.file_type;
The above statement creates a simple variable "f" (can also be called the file handle), which is based on the data type "utl_file.file_type". This is not a simple data type like integer or varchar. It has its own in-depth meaning.
"UTL_FILE" is a built-in package, which comes with the Oracle database installation. Any package in Oracle (either user-defined or built-in) contains one or more members in it. These members (together as a unit) are generally used to constitute a complete system to facilitate a particular feature. Similarly, "UTL_FILE" is also a package which has several members to facilitate use of the features of accessing file system.
"utl_file.file_type" is a different data type which points to a particular file specified by the user. Once it successfully points, we can proceed with other operations like reading, writing, and so on. Further proceeding, we have the following:
s varchar2(200);
I declared a simple variable "s" to simply store a line (max. 200 chars) of information from the text file. Further we have the following:
f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
The above statement is the key to the whole program. It simply opens the file "sample1.txt" available in the "SAMPLEDATA" directory in readonly ("R") mode. Once PL/SQL opens the file, the handle to that file gets stored in the variable "f". Be aware that "SAMPLEDATA" is the logical directory name as discussed in the previous section (and not the physical directory existing in your operating system). Further proceeding we have the following:
utl_file.get_line(f,s);
The above line simply tries to read the first line available in the file (where "f" is pointing to) and places the line in "s" (declared previously). Once we finish all the operations with a file, we need to close it and release any memory resources (like file handles and more). The same is achieved using the following:
utl_file.fclose(f);
It is highly recommended (in fact compulsory) that you close any file after finishing our operations (even though it doesn't issue any error when not closed). And finally, we display the output back to screen using the following statement:
dbms_output.put_line(s);
Next: Improving it a bit with exception handling >>
More Oracle Articles
More By Jagadish Chatarji
