Create .csv File and placed on Server using PL/SQL

 To create a file, we need to create a directory and have the read write permission as

1) create or replace directory XXDIR as '/home/oracle/mycsv'

Note: /home/oracle/xxdir has to be physical location on disk.

2) grant read, write on directory XXDIR to user

Following is the pl/sql sample code to create CSV file

DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT xxcolumn from xxtable;
BEGIN
    F := UTL_FILE.FOPEN('XXDIR','EMP_DEPT.CSV','w',32767);
    FOR i IN C1
    LOOP
        UTL_FILE.PUT(F,i.xxcolumn);
        UTL_FILE.NEW_LINE(F);
    END LOOP;
    UTL_FILE.FCLOSE(F);
END;
/

Read .csv File from Server using PL/SQL

 procedure xxt1 (p_batch_name  in varchar2,

                    p_file_name   in varchar2,

                    p_action      in varchar2,

                    p_hold_name   in varchar2,

                    p_hold_reason in varchar2,

                    p_release_name in varchar2,

                    p_release_reason in varchar2)

is

      l_file_type      utl_file.file_type;

      l_string         varchar2 (32765);

      l_batch_number   number;

      l_count          number;

      l_address        varchar2 (150);

      l_file_name      varchar2 (150);

      type fieldvalue is table of varchar2 (4000) index by binary_integer;

      t_field          fieldvalue;

      function getstring (source_string    in varchar2,

                          field_position   in number,

                          unterminated     in boolean default false,

                          delimiter        in varchar2 default ',')

         return varchar2

      is

         iptrend           pls_integer := 0;

         iptrstart         pls_integer := 0;

         vcsourcestrcopy   varchar2 (4000) := source_string;

      begin

         if unterminated then

            vcsourcestrcopy := vcsourcestrcopy || delimiter;

         end if;

         if field_position > 1 then

            iptrstart :=instr (vcsourcestrcopy,delimiter,1,field_position - 1)+ length (delimiter);

         else

            iptrstart := 1;

         end if;

         iptrend :=instr (vcsourcestrcopy,delimiter,1,field_position);

         return substr (vcsourcestrcopy, iptrstart, (iptrend - iptrstart));

      end getstring;

begin

      l_batch_number := to_char(sysdate,'DDMMYYHHMMSS');

      l_count := 0;

          begin

             l_file_name := p_file_name || '.csv';

             l_file_type := utl_file.fopen ('INV_HOLD_RELEASE', l_file_name, 'r');--INV_HOLD_RELEASE This is directory where .csv file placed

          exception

             when others then

                begin

                    l_file_name := p_file_name || '.CSV';

                    l_file_type := utl_file.fopen ('INV_HOLD_RELEASE', l_file_name, 'r');

                exception

                when others then

                    fnd_file.put_line(fnd_file.log,'Error to open file : '||sqlerrm);

                end;

          end;

         loop

            utl_file.get_line (l_file_type,l_string);

            l_count := l_count + 1;

            l_string := l_string || ',';

            for n in 1 .. regexp_count (l_string, ',')

            loop

               t_field (n) :=getstring (l_string,n,false,',');

            end loop;

            if l_count > 1  then

                              null;

                              --Do busines activity here

            end if;

             commit;

         end loop;

         utl_file.fclose (l_file_type);

     

   exception

      when others then

         if utl_file.is_open (l_file_type)

         then

            utl_file.fclose (l_file_type);

         end if;

         dbms_output.put_line ('SQL ERR2'|| sqlerrm|| 'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE'|| dbms_utility.format_error_backtrace);

end xxt1;