How to use the UTL_FILE package in Oracle to read from and write to files on the server's file system

First, you need to ensure that the directory object is created in the Oracle database and granted appropriate permissions. Let's assume you have a directory object named DATA_FILES pointing to the directory where your files are located.

1. Creating Directory Object:

CREATE DIRECTORY DATA_FILES AS '/path/to/files';

2. Granting Permissions:

GRANT READ, WRITE ON DIRECTORY DATA_FILES TO <your_user>;

Now, let's proceed with a simple example:

DECLARE file_handle UTL_FILE.FILE_TYPE; file_name VARCHAR2(100) := 'DATA_FILES/example.txt'; file_data VARCHAR2(100); BEGIN -- Open file for reading file_handle := UTL_FILE.FOPEN('DATA_FILES', 'example.txt', 'R'); -- Read data from the file LOOP UTL_FILE.GET_LINE(file_handle, file_data); DBMS_OUTPUT.PUT_LINE('Data from file: ' || file_data); END LOOP; -- Close file UTL_FILE.FCLOSE(file_handle); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('Invalid directory path.'); WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('Invalid file mode.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('Invalid file handle.'); WHEN UTL_FILE.READ_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error reading from file.'); WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error writing to file.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /

In this example:

• We declare a file handle (file_handle) of type UTL_FILE.FILE_TYPE.
• We specify the file name and path (file_name) we want to read from.
• We open the file for reading using UTL_FILE.FOPEN.
• We use a loop to read each line from the file using UTL_FILE.GET_LINE.
• We handle exceptions that may occur during file operations.
• Finally, we close the file using UTL_FILE.FCLOSE.

You can adapt this example to write to a file by changing the mode parameter in the UTL_FILE.FOPEN function to 'W' (write mode) and using UTL_FILE.PUT_LINE to write data to the file.

Here's an example demonstrating how to write data to a file using the UTL_FILE package in Oracle:

DECLARE file_handle UTL_FILE.FILE_TYPE; file_name VARCHAR2(100) := 'DATA_FILES/output.txt'; BEGIN -- Open file for writing file_handle := UTL_FILE.FOPEN('DATA_FILES', 'output.txt', 'W'); -- Write data to the file UTL_FILE.PUT_LINE(file_handle, 'This is line 1.'); UTL_FILE.PUT_LINE(file_handle, 'This is line 2.'); UTL_FILE.PUT_LINE(file_handle, 'This is line 3.'); -- Close file UTL_FILE.FCLOSE(file_handle); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('Invalid directory path.'); WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('Invalid file mode.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('Invalid file handle.'); WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error writing to file.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /

In this example:

• We declare a file handle (file_handle) of type UTL_FILE.FILE_TYPE.
• We specify the file name and path (file_name) we want to write to.
• We open the file for writing using UTL_FILE.FOPEN, specifying 'W' as the mode parameter.
• We use UTL_FILE.PUT_LINE to write data to the file. Each call to PUT_LINE writes a new line to the file.
• We handle exceptions that may occur during file operations.
• Finally, we close the file using UTL_FILE.FCLOSE.

Make sure to adjust the directory path and file name according to your setup. Additionally, ensure that the directory object has been created and granted appropriate permissions, as mentioned in the previous example.