File Handling using PL/SQL

An Oracle directory is a database object pointing to a operating system directory on the database server machine for reading and writing files.

1. Create Oracle Directory.
   CREATE OR REPLACE DIRECTORY scott_dir as '/home/scott';
2. Grant Privileges.
   GRANT read, write ON DIRECTORY scott_dir TO scott;
3. Read file.
   create or replace PROCEDURE SP_READ_FILE ( as_file_dir IN VARCHAR2, /* Oracle Directory */ as_file_name IN VARCHAR2 /* File Name */ ) AS lh_file_handle      UTL_FILE.FILE_TYPE; ls_line             VARCHAR2(1000); BEGIN lh_file_handle := UTL_FILE.FOPEN(as_file_dir, as_file_name, 'R'); LOOP UTL_FILE.GET_LINE(lh_file_handle,ls_line); dbms_output.put_line(ls_line); END LOOP; UTL_FILE.FCLOSE(lh_file_handle); END;
4. Write file.
   CREATE OR REPLACE PROCEDURE SP_WRITE_TO_FILE ( as_file_dir VARCHAR2, /* Oracle Directory of the Output Text */ as_file_name VARCHAR2 /* File Name of the Output Text */ ) AS lh_file_handle    UTL_FILE.FILE_TYPE; BEGIN lh_file_handle := UTL_FILE.FOPEN(as_file_dir, as_file_name, 'W', 32000); UTL_FILE.PUT_LINE(lh_file_handle, 'Hello World!!!'); UTL_FILE.FCLOSE(lh_file_handle); EXCEPTION WHEN UTL_FILE.INVALID_OPERATION THEN dbms_output.put_line('Invalid operation Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.INVALID_FILEHANDLE THEN dbms_output.put_line('Invalid File Handler Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.READ_ERROR THEN dbms_output.put_line('Read Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.WRITE_ERROR THEN dbms_output.put_line('Write Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.INVALID_PATH THEN dbms_output.put_line('Invalid Path Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.INVALID_MODE THEN dbms_output.put_line('Invalid Mode Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN UTL_FILE.INTERNAL_ERROR THEN dbms_output.put_line('Internal Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN VALUE_ERROR THEN dbms_output.put_line('Value Error: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); WHEN OTHERS THEN dbms_output.put_line('Other errors: '||SQLCODE||' -ERROR- '||SQLERRM); UTL_FILE.FCLOSE(lh_file_handle); END SP_WRITE_TO_FILE;