File handling with PL/SQL

[sameer]

I will give a very simple example of how to read from and write to a file with PL/SQL code block.

WRITE TO A FILE

UTL_FILE package is used for writing to a file. To begin writing to a file of a specific directory first you will need to have permission from your DBA.

If you are a DBA, you can create a directory as below and give permission to "user" as below

CONNECT / AS DBA
CREATE OR REPLACE DIRECTORY STUDENT AS '/tmp';
GRANT read, write ON DIRECTORY STUDENT TO user;
GRANT EXECUTE ON UTL_FILE TO user;

Once permission is granted to you, just follow the code below to write to a file.

DECLARE
  fHandler UTL_FILE.FILE_TYPE;
BEGIN
  fHandler := UTL_FILE.FOPEN('STUDENT', 'Details', 'w');
  UTL_FILE.PUTF(fHandler, Hello sir! How are you? \n');
  UTL_FILE.FCLOSE(fHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/

READ FROM A FILE

UTL_FILE package is again used for reading from a file. To begin reading from a file of a specific directory you will again need to have permission from DBA in the same manner as shown above.

Once you have read permission follow the code below to read from a file.

DECLARE
  fHandler UTL_FILE.FILE_TYPE;
  buf      varchar2(4000);
BEGIN
  fHandler := UTL_FILE.FOPEN('STUDENT', 'Details', 'r');
  UTL_FILE.GET_LINE(fHandler, buf);
  dbms_output.put_line('DATA FROM FILE: '||buf);
  UTL_FILE.FCLOSE(fHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/

Note how FOPEN and FCLOSE are used to open and close the file and GET_LINE and PUTF are used to read from and write to a file.