Home » SQL & PL/SQL » SQL & PL/SQL » utl_file.put will never flush?
utl_file.put will never flush? [message #202410] Thu, 09 November 2006 11:26 Go to next message
Messages: 220
Registered: April 2006
Senior Member
I have been using the utl_file.put procedure to dump data from the sys.source$ table into an html formatted file. I used put instead of put_line because each entry in the sys.source$ table ends with a new line character and so I thought it woudl be easier to just dump it all using put rather than remove the newline and use put_line.

This works fine for smaller packages, but when I am trying to dump larger packages I found that the code output would at some point get truncated, but my footers all still showed up fine. My output code looked something like this:

utl_file.put_line(fle, doc_header);
for src in line loop
     utl_file.put(src.line || ' ' || src.source);
end loop;
utl_file.put_line(fle, doc_footer);

Both the header and footer sections show up completely, but in the code section I get chopped off after about 600-700 lines. I replaced this code with the following:

utl_file.put_line(fle, doc_header);
for src in line loop
     utl_file.put(src.line || ' ' || replace(src.source, chr(10)));
end loop;
utl_file.put_line(fle, doc_footer);

This successfully dumps all the lines in longer packages. I am assuming this has something to do with buffer sizes, but it seems like the utl_file package should keep track of how much space it has in its buffers so it knows when the flush rather than making me implicitly do it with a put_line, or flush call.

So the question I guess, has anyone else seen this? Is it a known issue? Intended functionality?

More curious than anything as a workaround is not exactly brain surgery.

Re: utl_file.put will never flush? [message #202441 is a reply to message #202410] Thu, 09 November 2006 18:04 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
sys.source$.source is varchar2(4000 byte). I'm not sure what UTL_FILE.fopen default max line length is, but you can specify it up to 32k. One approach I've found useful is to flush every time you hit a chr(10) similar to this html to file dump.

-- based on http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:347617533333
-- the 255 was fine for my pages being dumped
   l_thepage   HTP.htbuf_arr;
   l_output    UTL_FILE.file_type;
   l_lines     NUMBER             DEFAULT 99999999;
   l_piece1    VARCHAR2 (255);
   l_piece2    VARCHAR2 (255);
   l_output    := UTL_FILE.fopen (p_dir, p_fname, 'w');
    --l_output := UTL_FILE.fopen (p_dir, p_fname, 'w', 32767 );
   OWA.get_page (l_thepage, l_lines);

   FOR i IN 1 .. l_lines
      l_piece1    := NULL;
      l_piece2    := NULL;

      IF INSTR (l_thepage (i), CHR (10)) > 0
         l_piece1    := SUBSTR (l_thepage (i), 1, INSTR (l_thepage (i), CHR (10)) - 1);
         l_piece2    := SUBSTR (l_thepage (i), INSTR (l_thepage (i), CHR (10)));
         UTL_FILE.put_line (l_output, l_piece1);
         UTL_FILE.put (l_output, l_piece2);
         UTL_FILE.put (l_output, l_thepage (i));
      END IF;

   UTL_FILE.fclose (l_output);
END dump_page;

You could also try UTL_FILE.fflush. Closing the file automatically flushes.
  ** FFLUSH - Force physical write of buffered output
  ** IN
  **   file - File handle (open in write/append mode)
  **   invalid_filehandle - not a valid file handle
  **   invalid_operation  - file is not open for writing/appending
  **   write_error        - OS error occured during write operation
Re: utl_file.put will never flush? [message #202567 is a reply to message #202410] Fri, 10 November 2006 07:12 Go to previous message
Messages: 220
Registered: April 2006
Senior Member
I get that I COULD manually flush it, when I find confusing is why put will never flush on it's own. When I use put_line it flushes either every time the call is made or at the very least when it needs to. Why would put allow itself to overflow like this, loosing data along the way?

Previous Topic: unique constraint violated when update record
Next Topic: If you have used QP_Price_List_PUB.Process_Price_List plz send me sample code
Goto Forum:

Current Time: Mon Jul 24 15:55:18 CDT 2017

Total time taken to generate the page: 0.17124 seconds