 |
|
The Get_Line() Function
Oracle Tips by
Burleson
|
The Get_Line() function reads the next line of text from an
open file and writes that text to a local buffer. The text can then be
processed like any local text variable. The function definition is as
follows:
FUNCTION Get_Line (file_handle IN FILE_TYPE,
buffer OUT varchar2)
The buffer parameter indicates the name of the local text
variable that will hold the line of text that is read. When there are
no more lines of text to read in an open file, a NO_DATA_FOUND
exception is raised.
The Is_Open() Function
The Is_Open() function is a boolean function that returns
TRUE if the specified file is currently open for processing and
FALSE if the specified file isn’t open for processing. The
function definition is as follows:
FUNCTION Is_Open (file_handle IN FILE_TYPE) RETURN boolean
The New_Line() Procedure
The New_Line() procedure is used to write a specified number
of new line characters to a file. The procedure definition is as
follows:
PROCEDURE New_Line (file_handle IN FILE_TYPE,
lines IN natural)
The lines parameter indicates the number of new line
characters that should be written to the specified file.
The Put() Procedure
The Put() procedure writes a string to a file. The procedure
definition is as follows:
PROCEDURE Put (file_handle IN FILE_TYPE,
buffer IN varchar2)
The buffer parameter can be either a text variable or a
character literal.
The PutF() Procedure
The PutF() procedure is used to write text to a file in a
specified format. The procedure definition is as follows:
PROCEDURE PutF (file_handle IN FILE_TYPE,
format IN varchar2,
arg1 IN varchar2 := NULL,
arg2 IN varchar2 := NULL,
arg3 IN varchar2 := NULL,
arg4 IN varchar2 := NULL,
arg5 IN varchar2 := NULL)
The format parameter is used to specify a format model for
the output. This string can include two special formatting characters:
%s and \n. Each %s is replaced with one of the
five optional arguments; thus, there can be up to five %s
characters in the format parameter. All occurrences of \n
are replaced with a new line character. There can be any number of
\n characters in the format parameter.
The Put_Line() Procedure
The Put_Line() procedure writes a specified string to a file
and starts a new line in the file. The procedure definition is as
follows:
PROCEDURE Put_Line (file_handle IN FILE_TYPE,
buffer IN varchar2)
Using the UTL_File Package
Let’s put together a simple example of using the UTL_File
package to access a file. This procedure will read data from a text
file that contains customer information and then write the information
to the CUSTOMERS table. Listing 9.4 illustrates how this
procedure might call the UTL_File package.
Listing 9.4 A procedure that uses the UTL_File
package.
PROCEDURE Read_Customer_Data
IS
FILE_HANDLE UTL_FILE.FILE_TYPE%TYPE;
DIRECTORY CONSTANT varchar2 (100) := '/users/imports/';
FILENAME CONSTANT varchar2 (10) := 'Customers';
DELIMITER CONSTANT varchar2 (1) := '^';
vLineOfText varchar2 (2000);
bMoreText boolean := TRUE;
vCustomerName varchar2 (50);
vCustomerPhone varchar2 (20);
BEGIN
FILE_HANDLE := UTL_File.FOpen (location => DIRECTORY,
filename => FILENAME,
open_mode => 'r');
WHILE (bMoreText) LOOP
BEGIN
UTL_File.Get_Line (file_handle => FILE_HANDLE,
buffer => vLineOfText);
iDelimiterPos := instr (vLineOfText, DELIMITER);
vCustomerName := substr (vLineOfText, 1, (iDelimiterPos - 1));
vCustomerPhone := substr (vLineOfText, iDelimterPos + 1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
bMoreText := FALSE;
END;
END LOOP;
FClose (file_handle => FILE_HANDLE);
END Read_Customer_Data;
Summary
This chapter covers the basics of using many of the packages
provided by Oracle; however, a complete examination of the packages
could be a book in itself! Use the examples provided here as a basis
to set up and investigate problems that you need to handle. As with
any language, PL/SQL has nuances that are best experienced rather than
described.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
