FOPEN function - Open a file

The FOPEN function opens a file for I/O.

Syntax

Return value

This function returns a value of type UTL_FILE.FILE_TYPE that indicates the file handle of the opened file.

Function parameters

location

An input argument of type VARCHAR(128) that specifies the alias of the directory that contains the file.

filename

An input argument of type VARCHAR(255) that specifies the name of the file.

open_mode

An input argument of type VARCHAR(10) that specifies the mode in which the file is opened:

a: append to file
r: read from file
w: write to file

max_linesize

An optional input argument of type INTEGER that specifies the maximum size of a line in characters. The default value is 1024 bytes. In read mode, an exception is thrown if an attempt is made to read a line that exceeds max_linesize. In write and append modes, an exception is thrown if an attempt is made to write a line that exceeds max_linesize. End-of-line character(s) do not count towards the line size.

Authorization

EXECUTE privilege on the UTL_FILE module.

Examples

Open a file, write some text to the file, and then close the file.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE proc1()
BEGIN
  DECLARE  v_filehandle    UTL_FILE.FILE_TYPE;
  DECLARE  isOpen          BOOLEAN;
  DECLARE  v_dirAlias      VARCHAR(50) DEFAULT 'mydir';
  DECLARE  v_filename      VARCHAR(20) DEFAULT 'myfile.csv';  
  CALL UTL_DIR.CREATE_OR_REPLACE_DIRECTORY('mydir', '/tmp');
  SET v_filehandle = UTL_FILE.FOPEN(v_dirAlias,v_filename,'w');
  SET isOpen = UTL_FILE.IS_OPEN( v_filehandle );
    IF isOpen != TRUE THEN
      RETURN -1;
    END IF;
  CALL DBMS_OUTPUT.PUT_LINE('Opened file: ' || v_filename);
  CALL UTL_FILE.PUT_LINE(v_filehandle,'Some text to write to the file.');
  CALL UTL_FILE.FCLOSE(v_filehandle);
END@

CALL proc1@

This example results in the following output.

Opened file: myfile.csv