OraFAQ Forum: SQL & PL/SQL » utl_file.put will never flush?

Home » SQL & PL/SQL » SQL & PL/SQL » utl_file.put will never flush?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

utl_file.put will never flush? [message #202410]

Thu, 09 November 2006 11:26

annagel
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.

Andrew

Report message to a moderator

Re: utl_file.put will never flush? [message #202441 is a reply to message #202410]

Thu, 09 November 2006 18:04

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
CREATE OR REPLACE PROCEDURE dump_page (p_dir IN VARCHAR2, p_fname IN VARCHAR2)
IS
   l_thepage   HTP.htbuf_arr;
   l_output    UTL_FILE.file_type;
   l_lines     NUMBER             DEFAULT 99999999;
   l_piece1    VARCHAR2 (255);
   l_piece2    VARCHAR2 (255);
BEGIN
   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
   LOOP
      l_piece1    := NULL;
      l_piece2    := NULL;

      IF INSTR (l_thepage (i), CHR (10)) > 0
      THEN
         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);
      ELSE
         UTL_FILE.put (l_output, l_thepage (i));
      END IF;
   END LOOP;

   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)
  ** EXCEPTIONS
  **   invalid_filehandle - not a valid file handle
  **   invalid_operation  - file is not open for writing/appending
  **   write_error        - OS error occured during write operation
  */

Report message to a moderator

Re: utl_file.put will never flush? [message #202567 is a reply to message #202410]

Fri, 10 November 2006 07:12

annagel
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?

Andrew

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 15 22:59:51 CDT 2025