This procedure returns the next line from a file in buffer. If the line is empty, NULL is returned. If the line does not fit in buffer, VALUE_ERROR is raised. After the end of the file is reached, NO_DATA_FOUND is raised.

Here is a simple example of how UTL_FILE works:

-- Lets mess around a bit with UTL file.

DECLARE



Directory VARCHAR2(50);

Filename  VARCHAR2(30);

Message   VARCHAR2(255);

Garbage   VARCHAR2(255);

ReturnedLine VARCHAR2(255);

Counter NUMBER;



-- You must declare a variable of the following type to hold the file

identifier

-- returned by UTL_FILE.FOPEN



FileHandle UTL_FILE.FILE_TYPE;



BEGIN

    Directory := ‘/u01/app/oracle/logs’;

    Filename := ‘test.log’;

    Message := ‘Maybe this will do it.’;

    Garbage :=

‘lfkdsafldgjfhfdkldsakfjlkjsalkdaosskfwlamffkmewkfmwlmfmfkewmkefmwkm’;



    FileHandle := UTL_FILE.FOPEN(Directory, Filename, ‘w’);



    UTL_FILE.PUT(FileHandle, ‘This thing actually seems to work!!’);

    UTL_FILE.PUT(FileHandle, ‘I wonder if I can break it.’);

    UTL_FILE.NEW_LINE(FileHandle, 2);

    UTL_FILE.PUT_LINE(FileHandle, Message);

    UTL_FILE.PUTF(FileHandle, ‘Still not broken eh?  Well take this! %s!

And this! %s!\n\n’, Garbage, Garbage);



    -- Remember to close the file



    UTL_FILE.FCLOSE(FileHandle);



        -- We’ve written to a file, now let’s see if we can get stuff back

out of it.

    -- First we have to open it in read mode.



    FileHandle := UTL_FILE.FOPEN(Directory, Filename, 'r’);



    -- Then we read the first line.



        UTL_FILE.GET_LINE(FileHandle, ReturnedLine);



    -- Close the file and open it again in append mode.



        UTL_FILE.FCLOSE(FileHandle);

    FileHandle := UTL_FILE.FOPEN(Directory, Filename, 'a’);



    -- Write the line we got back to the file 10 times - for proof it

worked.



    Counter := 0;

        WHILE Counter < 10 LOOP

        UTL_FILE.PUT_LINE(FileHandle, ReturnedLine);

            Counter := Counter + 1;

    END LOOP;

    UTL_FILE.FCLOSE(FileHandle);



EXCEPTION



    WHEN UTL_FILE.INVALID_PATH THEN

        RAISE_APPLICATION_ERROR(-20100, 'Invalid Path’);



    WHEN UTL_FILE.INVALID_MODE THEN

        RAISE_APPLICATION_ERROR(-20101, 'Invalid Mode’);



    WHEN UTL_FILE.INVALID_FILEHANDLE THEN

        RAISE_APPLICATION_ERROR(-20102, 'Invalid Filehandle’);



    WHEN UTL_FILE.INVALID_OPERATION THEN

        RAISE_APPLICATION_ERROR(-20103, 'Invalid Operation’);



    WHEN OTHERS THEN

        UTL_FILE.FCLOSE(FileHandle);

        RAISE;

END;

/

The following is the contents of the file the preceding code would generate:

This thing actually seems to work!!  I wonder if I can break it.



Maybe this will do it.

Still not broken eh?  Well take this!

lfkdsafldgjfhfdkldsakfjlkjsalkdaosskfwlamffkmewkfmwlmfmfkewmkefmwkm! And

this! lfkdsafldgjfhfdkldsakfjlkjsalkdaosskfwlamffkmewkfmwlmfmfkewmkefmwkm!



This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

This thing actually seems to work!!  I wonder if I can break it.

Working with LOB Data

Large objects, or LOBs, are designed to hold up to four gigabytes of RAW, binary data. In Oracle 7, the LONG RAW data type could only be accessed serially through PL/SQL or the Oracle Call Interface (OCI). This presented a unique challenge when it came to searching for a specific bit of information within such an object, because Oracle did not provide any piecewise operations for reading, writing, or copying LOB data. Parsing LOB data was left to applications outside Oracle. Oracle8 changes all that by providing new internal and external LOB data types along with OCI and PL/SQL support for piecewise reading, writing, and comparison of LOB data.