PL/SQL: Sample code for UTL_FILE

In this post I will a sample code to write to a file from PL/SQL.

• Create a folder/directory (ex:- C:\test )manually in the file system.
• Create a directory object in the database by connecting as SYSDBA.

 CONN / as SYSDBA;  
 create or replace directory TEST as 'C:\test';  
 

• Grant execute privilege on UTL_FILE to the user/schema (ex:- HR) you are going to use, using SYSDBA.

 connect / as SYSDBA;  
 grant execute on utl_file to HR;  

• Now you can use the below code to write a file into that directory.

 declare
      v_file   utl_file.file_type;  
      v_line   varchar2(1000);  
      v_file_name constant varchar2(15) := 'aj_test.txt';  
 begin  
      v_file := utl_file.fopen('TEST', v_file_name, 'w', 5000);
  
      v_line := 'This is the first line!!!!';  
      utl_file.put_line(v_file, v_line);  

      v_line := 'This is the last line!!!!';  
      utl_file.put_line(v_file, v_line);  

      utl_file.fclose(v_file);  
 exception  
      when others then  
           raise;  
 end;  

Refer:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS069

Feel free to point out if anything is missing/wrong in this blog.