Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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
   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);
   FILE_HANDLE := UTL_File.FOpen (location  => DIRECTORY,
                                  filename  => FILENAME,
                                  open_mode => 'r');
   WHILE (bMoreText) LOOP
         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);
              bMoreText := FALSE;
   FClose (file_handle => FILE_HANDLE);
END Read_Customer_Data;


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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter