Working with UTL_FILE

UTL

UTL_FILE is available for both client-side and server-side PL/SQL. Both the client (text I/O) and server implementations are subject to server-side file system permission checking.

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However,UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default

CREATE DIRECTORY test_dir AS 'c:\';

-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE

  fileHandler UTL_FILE.FILE_TYPE;

BEGIN

  fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');

  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');

  UTL_FILE.FCLOSE(fileHandler);

EXCEPTION

  WHEN utl_file.invalid_path THEN

     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');

END;

/

DECLARE 

  V1 VARCHAR2(32767); 

  F1 UTL_FILE.FILE_TYPE; 

BEGIN 

  -- In this example MAX_LINESIZE is less than GET_LINE's length request 

  -- so the number of bytes returned will be 256 or less if a line terminator is seen. 

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); 

  UTL_FILE.GET_LINE(F1,V1,32767); 

  UTL_FILE.FCLOSE(F1); 

  -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, 

  -- so the number of bytes returned will be 1024 or less if a line terminator is seen. 

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); 

  UTL_FILE.GET_LINE(F1,V1,32767); 

  UTL_FILE.FCLOSE(F1); 

  -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to 

  -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. 

  -- So the number of bytes returned will be 1024 or less if a line terminator is seen. 

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); 

  UTL_FILE.GET_LINE(F1,V1); 

  UTL_FILE.FCLOSE(F1); 

END;

INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.