3 Replies Latest reply: Feb 9, 2013 5:49 AM by Senthilkumar S RSS

    Creating CSV file

    Senthilkumar S
      Hi ,

      When we try to create the CSV file using UTL_FILE, line size is exceeding 32767 length.So we are not able to create the files.

      We are having so many CLOB fields which needs to be come into CSV. so line size is exceeding.

      do we have any fix to add line which is more than 32767 length into file using utl_file?


      PS: All the fields data should come in the single line not to next line.

      Sample code as below
      declare 
         l_ftype             UTL_FILE.FILE_TYPE;
         l_c clob:='a';
         l_line clob;
         
      begin
      
       l_ftype :=
               UTL_FILE.fopen ('XXITM_ERP_MDF_DIR',
                               'test.csv',
                               'W',
                               32767);
      for j in 1..3 loop               
      for i in 1..50000
      
      loop
      l_c:=l_c||','||l_c;
               
      end loop;
       UTL_FILE.put_line (l_ftype, l_line);
       end loop;
       UTL_FILE.FCLOSE;
      
      end;
      /
        • 1. Re: Creating CSV file
          Herald ten Dam
          Hi,

          you can have a look at the procedure dbms_xslprocessor.clob2file, it can write clobs to a file system. Have a look in the documentation: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xslpro.htm

          Herald ten Dam
          http://htendam.wordpress.com
          • 2. Re: Creating CSV file
            rp0428
            >
            When we try to create the CSV file using UTL_FILE, line size is exceeding 32767 length.So we are not able to create the files.

            We are having so many CLOB fields which needs to be come into CSV. so line size is exceeding.

            do we have any fix to add line which is more than 32767 length into file using utl_file?

            PS: All the fields data should come in the single line not to next line.
            >
            Why would you want lines that are that long?

            You can only buffer 32k of data so you can't use put_line for data larger than that.

            Modify your code to use the 'put' method and flush the buffer before it gets to the 32k boundary.

            See the UTL_FILE doc for the 'put' procedure
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_file.htm#i997504
            >
            PUT Procedure
            PUT writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. No line terminator is appended by PUT; use NEW_LINE to terminate the line or use PUT_LINE to write a complete line with a line terminator. See also "PUT_NCHAR Procedure".
            . . .
            Usage Notes

            The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
            . . .
            FFLUSH Procedure
            FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.
            • 3. Re: Creating CSV file
              Senthilkumar S
              tHANKS...i HAVE USED dbms_xslprocessor.clob2filE