UTL File Concept

When we are going to work with UTL Files, we have to follow four steps,

1. Declare a File Variable
2. Open a File using File Variable
3. Write the data into the file using File Variable
4. Close the file using File Variable.

Note: To create a file in the server the file directory path should be defined or registered.  To check the defined file directory path below query can be used,
        select value from v$parameter where name like 'utl_file_dir'
If the above select statement doesn't return any records, that means there is no defined file directory path.  This will be taken care by DBA's.  Else if you want to create a directroy path follow the below steps

• Connect as SYSDBA
• CREATE OR REPLACE DIRECTORY AS '/PATH'
• GRANT READ, WRITE  ON DIRECTORY TO SCOTT;
• ALTER SYSTEM SET UTL_FILE_DIR = '/PATH' SCOPE=SPFILE 

UTL File Steps:

• Declare a File Variable:

         Syn:   file_var_name UTL_FILE.FILE_TYPE;
           Ex:  fv UTL_FILE.FILE_TYPE;

• Open a File using File Variable

            Syn: file_var_name  := UTL_FILE.FOPEN(,,);
             Ex:  fv := UTL_FILE.FOPEN('C:/TEMP','SAM.txt','W');

• Write the data into the File

             Syn: UTL_FILE.PUT_LINE(file_var_name,'Message' / variable);
               Ex: UTL_FILE.PUT_LINE(fv,'Hello');

• Close the File

              Syn: UTL_FILE.FCLOSE('file_var_name);
                Ex: UTL_FILE.FCLOSE(fv);

Sample piece of UTL File Code,

create or replace procedure utl_sample
is
  l_file_var utl_file.file_type;
begin
  l_file_var := utl_file.fopen('C;/temp','SAM.txt','W');
  utl_file.put_line(l_file_var,'This is a sample prog');
  utl_file.fclose(l_file_var);
end;