310-313

Previous

Table of Contents

Next

The Get_Line() Function

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.

Previous

Table of Contents

Next