UTL_FILE

UTL_FILE

DECLARE

V1 VARCHAR2(32767);

F1 UTL_FILE.FILE_TYPE;

BEGIN

— In this example MAX_LINESIZE is less than GET_LINE’s length request

— so the number of bytes returned will be 256 or less if a line terminator is seen.

F1 := UTL_FILE.FOPEN(‘MYDIR’,’MYFILE’,’R’,256);

UTL_FILE.GET_LINE(F1,V1,32767);

UTL_FILE.FCLOSE(F1);

— In this example, FOPEN’s MAX_LINESIZE is NULL and defaults to 1024,

— so the number of bytes returned will be 1024 or less if a line terminator is seen.

F1 := UTL_FILE.FOPEN(‘MYDIR’,’MYFILE’,’R’);

UTL_FILE.GET_LINE(F1,V1,32767);

UTL_FILE.FCLOSE(F1);

— In this example, GET_LINE doesn’t specify a number of bytes, so it defaults to

— the same value as FOPEN’s MAX_LINESIZE which is NULL in this case and defaults to 1024.

— So the number of bytes returned will be 1024 or less if a line terminator is seen.

F1 := UTL_FILE.FOPEN(‘MYDIR’,’MYFILE’,’R’);

UTL_FILE.GET_LINE(F1,V1);

UTL_FILE.FCLOSE(F1);

END;

Name Description
FCLOSE Closes the specified files
FCLOSE_ALL Closes all open files
FFLUSH Flushes all the data from the UTL_FILE buffer
FOPEN Opens the specified file
GET_LINE Gets the next line from the file
IS_OPEN Returns TRUE if the file is already open
NEW_LINE Inserts a newline mark in the file at the end of the current line
PUT Puts text into the buffer
PUT_LINE Puts a line of text into the file
PUTF Puts formatted text into the buffer

UTL_FILE exceptions

INVALID_PATH

The file location or the filename is invalid. Perhaps the directory is not listed as a utl_file_dir parameter in the INIT.ORA file (or doesn’t exist as all), or you are trying to read a file and it does not exist.

INVALID_MODE

The value you provided for the open_mode parameter in UTL_FILE.FOPEN was invalid. It must be “A,” “R,” or “W.”

INVALID_FILEHANDLE

The file handle you passed to a UTL_FILE program was invalid. You must call UTL_FILE.FOPEN to obtain a valid file handle.

INVALID_OPERATION

UTL_FILE could not open or operate on the file as requested. For example, if you try to write to a read-only file, you will raise this exception.

READ_ERROR

The operating system returned an error when you tried to read from the file. (This does not occur very often.)

WRITE_ERROR

The operating system returned an error when you tried to write to the file. (This does not occur very often.)

INTERNAL_ERROR

Uh-oh. Something went wrong and the PL/SQL runtime engine couldn’t assign blame to any of the previous exceptions. Better call Oracle Support!

programs in UTL_FILE may also raise the following standard system exceptions:

NO_DATA_FOUND

Raised when you read past the end of the file with UTL_FILE.GET_LINE.

VALUE_ERROR

Raised when you try to read or write lines in the file which are too long. The current implementation of UTL_FILE limits the size of a line read by UTL_FILE.GET_LINE to 1022 bytes.

INVALID_MAXLINESIZE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s