OUTBOUND-UTL PACKAGE

UTL_FILE PACKAGE



The UTL_FILE package provides text file I/O from within PL/SQL. The init.ora file, the initialization parameter UTL_FILE_DIR is set to the accessible directories desired.
UTL_FILE_DIR = directory_name

Function & Procedure Description

• FOPEN A function that opens a file for input or output and returns a file handle used in subsequent I/O operations
• IS_OPEN A function that returns a Boolean value whenever a file handle refers to an open file
• GET_LINE A procedure that reads a line of text from the opened file and places the text in the Output buffer parameter (the maximum size of an input record is 1,023 bytes unlessyou specify a larger size in the overloaded version of FOPEN)
• PUT, PUT_LINE A procedure that writes a text string stored in the buffer parameter to the opened file (no line terminator is appended by put; use new_line to terminate the line, or use PUT_LINE to write a complete line with a terminator)
• PUTF A formatted put procedure with two format specifiers: %s and \n (use %s to substitute a value into the output string. \n is a new line character)
• NEW_LINE Procedure that terminates a line in an output file
• FFLUSH Procedure that writes all data buffered in memory to a file
• FCLOSE Procedure that closes an opened file
• FCLOSE_ALL Procedure that closes all opened file handles for the session
• Exceptions to the UTL_FILE Package Exception Name Description
• INVALID_PATH The file location or filename was invalid.
• INVALID_MODE The OPEN_MODE parameter in FOPEN was invalid.
• INVALID_FILEHANDLE The file handle was invalid.
• INVALID_OPERATION The file could not be opened or operated on as requested.
• READ_ERROR An operating system error occurred during the read operation.
• WRITE_ERROR An operating system error occurred during the write operation.
• INTERNAL_ERROR An unspecified error occurred in PL/SQL.

UTL_FILE procedures can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or
VALUE_ERROR.


SQL * Loader


D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'