plsql utl_file put_line, get_line

UTL_FILE
==================================================================
-> get data from file
-> upload data to file.
==================================================================
Pre-Req
===================================================================
1. using system user, grant user to create dir.  [login to sys]
grant create any directory to xxdbuser

2. create a alias directory [logical dir]
 create or replace directoty daily_rate as '/u01/ebs/rate';
 create or replace directoty daily_rate as 'C:\';

3. give read and write priv on directory [from sys as sysdba]
  grant read, write on directory daily_rate to xxdbuser;

=================================================================

1. write data into file
    a. putf()
    b. put_line()
 
        1. use file pointer variable : declare file_type [declare section] this var will hold the file
            fv_rate_file utl_file.file_type;
        2. open file: fopen(file_pointer,,)  [under begin]
            fv_rate_file := utl_file.fopen(<alias_dir>,<file_name>,<mode: r,w>)
            fv_rate_file := utl_file.fopen(daily_rate,gl_rate.csv,w)
        3. write data into file put_line or putf []    fp,'%s\n',<data>
            utl_file.putf(fv_rate_file,'content')
        4. close the file.
            utl_file.fclose(fv_rate_file)


2.  Read data from file
    a. get_line()
        1. define a file pointer variable.
            fv_rate_file  utl_file.file_type;
            v_line        varchar2(2000);
        2. open file fopen
            fv_rate_file := utl_file.fopen(<alias_dir>,<file_name>,<mode: r>)
        3. fetch data from file.
            utl_file.get_line(fv_rate_file,v_line)
        4.  close the file.
            utl_file.fclose(fv_rate_file) 
         

Common Error for long text in single line:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148

This error appear when output string in a line is very long.  The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. Recommendation is, use "max_linesize => 32767" for each utl_file.fopen. In the above case I have added the maximum value to overcome the error.
 fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (v_location, v_filename , 'W', 32767);