Oracle PL SQL Tutorial day 8
Oracle PL/SQL is not just limited to database-centric logic. It also enables you to interact with the file system through the built-in package UTL_FILE, which allows reading from and writing to files located on the database server. This feature is particularly useful for logging, report generation, data export/import, and integration with other systems.
Remember the file create on database folders can be accessable via ftp in linux or file browser in windows
In this comprehensive guide, we will cover:
1. What is UTL_FILE?
2. Key Procedures and Functions
3. Setting Up UTL_FILE in Oracle
4. Writing to a File
5. Reading from a File
6. Handling Exceptions
7. File Manipulation Examples
8. Best Practices
9. Common Errors and Troubleshooting
10. Conclusion
1. What is UTL_FILE?
UTL_FILE is an Oracle-supplied package that allows PL/SQL programs to interact with operating system files located on the server. It supports both text and raw data formats and is frequently used for batch processing, exporting data, and logging.
Key features:
- Read/write access to server-side files
- File pointer support
- Exception handling for file operations
2. Key Procedures and Functions
Here are some of the most commonly used subprograms:
- FOPEN: Opens a file for input or output
- PUT_LINE: Writes a line to a file
- GET_LINE: Reads a line from a file
- FCLOSE: Closes a file
- IS_OPEN: Checks if a file handle is open
- FCLOSE_ALL: Closes all open files
3. Setting Up UTL_FILE in Oracle
Before you can use UTL_FILE, you must:
a. Set the Directory in Oracle
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/app/oracle/files';
b. Grant Access
GRANT READ, WRITE ON DIRECTORY my_dir TO hr;
c. Check Initialization Parameters
Ensure UTL_FILE_DIR (older) or DIRECTORY object is configured correctly.
Note: Using UTL_FILE_DIR is deprecated in favor of directory objects.
4. Writing to a File
Let’s create a PL/SQL block that writes employee data to a file using the HR schema:
DECLARE
file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN('MY_DIR', 'employees.txt', 'W');
FOR emp_rec IN (SELECT first_name, last_name, salary FROM employees) LOOP
UTL_FILE.PUT_LINE(file_handle, emp_rec.first_name || ' ' || emp_rec.last_name || ' - ' || emp_rec.salary);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid path.');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Write error occurred.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
This will create a file named employees.txt in the MY_DIR directory, listing employee names and salaries.
5. Reading from a File
Suppose we have a file with employee bonus information. Let’s read and display it:
DECLARE
file_handle UTL_FILE.FILE_TYPE;
line_buffer VARCHAR2(4000);
BEGIN
file_handle := UTL_FILE.FOPEN('MY_DIR', 'bonuses.txt', 'R');
LOOP
BEGIN
UTL_FILE.GET_LINE(file_handle, line_buffer);
DBMS_OUTPUT.PUT_LINE('Read Line: ' || line_buffer);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('Invalid file handle.');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Read error occurred.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
This block reads each line until the end of the file.
6. Handling Exceptions
UTL_FILE operations can raise various exceptions:
- INVALID_PATH Path is invalid or not accessible
- INVALID_MODE Mode is not 'R', 'W', or 'A' |
- INVALID_OPERATION Operation not allowed on file
- READ_ERROR Error occurred during read |
- WRITE_ERROR Error occurred during write
- INTERNAL_ERROR Unexpected internal error
Always wrap UTL_FILE calls with BEGIN ... EXCEPTION ... END blocks for safety.
7. File Manipulation Examples
a. Append Data to File
file_handle := UTL_FILE.FOPEN('MY_DIR', 'log.txt', 'A');
UTL_FILE.PUT_LINE(file_handle, 'New log entry: ' || SYSDATE);
UTL_FILE.FCLOSE(file_handle);
b. Export Employee Emails
DECLARE
f UTL_FILE.FILE_TYPE;
BEGIN
f := UTL_FILE.FOPEN('MY_DIR', 'emails.csv', 'W');
FOR r IN (SELECT first_name, last_name, email FROM employees) LOOP
UTL_FILE.PUT_LINE(f, r.first_name || ',' || r.last_name || ',' || r.email);
END LOOP;
UTL_FILE.FCLOSE(f);
END;
c. Read and Insert File Data Into Table
Assume a file with format: first_name,last_name,email
DECLARE
f UTL_FILE.FILE_TYPE;
line VARCHAR2(4000);
v_fname VARCHAR2(100);
v_lname VARCHAR2(100);
v_email VARCHAR2(100);
BEGIN
f := UTL_FILE.FOPEN('MY_DIR', 'new_employees.csv', 'R');
LOOP
BEGIN
UTL_FILE.GET_LINE(f, line);
v_fname := REGEXP_SUBSTR(line, '[^,]+', 1, 1);
v_lname := REGEXP_SUBSTR(line, '[^,]+', 1, 2);
v_email := REGEXP_SUBSTR(line, '[^,]+', 1, 3);
INSERT INTO temp_employees (first_name, last_name, email)
VALUES (v_fname, v_lname, v_email);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(f);
END;
8. Best Practices
- - Use Oracle directory objects over UTL_FILE_DIR
- - Always check and close file handles
- - Use exception handling liberally
- - Limit file size and line length (default 1024 bytes, configurable)
- - Validate file contents before using them in DML
- - Avoid hardcoding directory names
9. Common Errors and Troubleshooting
- ORA-29280: Invalid directory path
- Ensure the directory object exists and is accessible
- ORA-29283: Invalid file operation
- Check file mode, path, and name
- ORA-06512 at UTL_FILE line
- Use exception block to pinpoint issues
- Permission denied
- Make sure Oracle has read/write permissions on the OS folder
10. Conclusion
Oracle PL/SQL’s UTL_FILE package is a powerful tool for integrating file operations into your database applications. From exporting reports to reading configuration files and logging application behavior, file operations extend the usefulness of PL/SQL far beyond the database.
By following the practices and examples outlined in this guide, you'll be equipped to handle file operations confidently and effectively in any PL/SQL-based application.
Explore further:
- - DBMS_LOB for large binary/text files
- - External Tables for structured data imports
- - UTL_MAIL for email-based alerts in combination with logs
0 Comments