PLSQL Utl_File Package

Utl_File

Utl_File : This is the Oracle supplied package which is used for extracting data from Oracle queries into csv file or in other format files.

 

Note:- Before using utl_file package, we need to create a directory in Database. Also need to read and write permission on that directory.

SQL>CREATE DIRECTORY test_dir AS 'c:\'; (For Window OS)
SQL>CREATE DIRECTORY test_dir AS '/tmp'; (For Linux OS)

 

Note:-Directory should be created on OS as well.

 

SQL>grant read,write on directory test_dir to scott;

OPEN a file FOR read operations:-

UTL_FILE.fopen(
file_location IN VARCHAR2,
file_name     IN VARCHAR2,
open_mode     IN VARCHAR2,
max_linesize  IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;


Example:- Reading line from file and print

DECLARE
 vInHandle UTL_FILE.file_type;
 vNewLine  VARCHAR2(250);
BEGIN
  vInHandle := UTL_FILE.fopen('TEST_DIR', 'test_file.txt', 'R');
  LOOP
    BEGIN
      UTL_FILE.get_line(vInHandle, vNewLine);
      DBMS_OUTPUT.put_line(vNewLine);
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
  UTL_FILE.fclose(vInHandle);
END fopen;
/

Open a File in Write Mode:-

UTL_FILE.fopen(, file_name, 'w') ;

 

Open a File in Read Mode:-

UTL_FILE.fopen(, file_name, 'r') ;

Close a File:-

UTL_FILE.fclose(file IN OUT  RECORD );

CLOSE ALL FILES:-

PROCEDURE 

fclose_all ();

Example:- Writing a line in file 

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
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen('TEST_DIR', 'test_file.txt' , 'w');
  utl_file.put(fhandle, 'Hello world!'|| CHR(10));
  utl_file.put(fhandle, 'Hello again!');
  utl_file.fclose(fhandle);
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE || ' - ' || SQLERRM);
    raise;
end;
/

Example:- Creating a report from SQL Query

declare
   fileID UTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN ('TEST_DIR', 'emp.csv', 'W');
   FOR emprec IN (SELECT * FROM emp)
   LOOP
      UTL_FILE.PUT (fileID,TO_CHAR (emprec.empno) || ',' ||
          emprec.ename || ',' ||TO_CHAR (emprec.deptno)|| CHR(10));
   END LOOP;
   UTL_FILE.FCLOSE (fileID);
END;
/