Using UTL_FILE

Using UTL_FILE In Oracle

It is a oracle supplied package used to read and write operating system file that are located on oracle database server.

We must create directory in Oracle from where the file will be read or write.

CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\data';

GRANT read ,write ON DIRECTORY to ext_dir to User;

=> Setting in init.ora file

Utl_file_dir=c:\data

=>UTL_FILE.FILE_TYPE :- The datatype that can handle UTL_FILE type variable.

=>UTL_FILE.FOPEN :- Function to open a file for read/write operation. FOPEN takes 4 paramter

File_location(ext_dir)

File_name(emp.csv)

File_mode('R','W')

Max_linesize() :- optional field , accepts binary integer defining the line size of read or write. Defualt is NULL.

=>UTL_FILE.FOPEN_NCHAR :- Function to open multibyte character file for read or write. Same as FOPEN

=>UTL_FILE.FCLOSE :- Close the file, accepts only one argument.

=>UTL_FILE.FCLOSE_ALL :- close all files.

=>UTL_FILE.GET_LINE :- Reads a line from file. It accepts 2 arguments.

  1. File :- utl_file variable

  2. Len :- string variable to store the line read from file.

=>UTL_FILE.GET_LINE_NCHAR :- Same as UTL_FILE.GET_LINE but from mutibyte file.

=>UTL_FILE.PUT :- Write a string to a file. It accepts 3 arguments.

  1.file :- utl_file type variable.

  2.str :- string variable to write into the file.

  3.autoflush :-boolean variable, default is false.

=>UTL_FILE.PUT_NCHAR :- Same as UTL_FULE.PUT but write a unicode string into multibtye file.

=>UTL_FILE.PUT_LINE :- Write a line to a file and appends a newline character. It accepts 3 parameter.

  1.file :- utl_file type variable.

  2.str :- String variable to write to a file.

=>UTL_FILE.PUT_LINE_NCHAR :- Write a unicode line and appends a new line. Same as UTL_FILE.PUT_LINE.

=>UTL_FILE.NEW_LINE :- Writes one or more new line character to a file. It accepts 2 parameters.

  1.file :- utl_file type variable.

  2.lines :- number of new line character.

=>UTL_FILE.IS_OPEN :-  Returns true if file is open. Otherwise false. It takes one argument.

  1. File :- utl_file type variable.

=>UTL_FILE.FFLUSH :-Writes pending data to the file. Takes one argument

  1.file :- utl_file type variable.

Exception related to UTL_FILE

1. Utl_file.invalid_filename

2. Utl_file.invalid_path

3. Utl_file.access_denied

4. Utl_file.invalid_operation

5. Utl_file.read_error

6. Utl_file.write_error

Example

DECLARE

L_fileid UTL_FILE.FILE_TYPE;

L_dirpath VARCHAR2(30):='ext_dir';

L_filename VARCHAR2(30):='emp.csv';

L_linec NUMBER :=1;

L_buffer VARCHAR2(32000);

BEGIN

L_fileid.FOPEN(l_dirpath,l_filename,'R' , 32000);

LOOP

 UTL_FILE.GET_LINE(l_fileid,l_buffer);

 L_linec:=l_linec+1;

 Dbms_output.put_line(l_buffer);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

UTL_FILE.FCLOSE(l_fileid);

END;

NOTE : For random access we have to use FSEEK.