File Handling in PL/SQL

Here is a very simple example for 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

Code:

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.

Code:

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.

Code:

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.

Advertisements