Re: Writing to file

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Wed, 21 Dec 2011 05:33:59 -0800 (PST)
Message-ID: <8cc99506-5061-41cb-8853-f056a43731d7_at_v13g2000yqc.googlegroups.com>

On Dec 20, 5:33 pm, Peter Schneider <pschneider1..._at_googlemail.com> wrote:
> Am 20.12.2011 21:48, schrieb ExecMan:
>
> > On Dec 20, 2:00 pm, Peter Schneider<pschneider1..._at_googlemail.com>
> > wrote:
> >> Am 20.12.2011 19:25, schrieb ExecMan:
>
> >>> Hi,
>
> >>> I need to write out a .csv file.  Problem is, the lines in the file
> >>> will be VERY long.  Longer than 32k I believe.  So, I tried using a
>
> [...]
>
>
>
> >> Hi,
>
> [...]
>
> >> If you are on 10.x you can open the file with mode "wb" and buffer size 32K,
> >> and then use put_raw. 9.2 has a bug in put_raw that still requires LFs every
> >> 32K, but 10.2 is fine. I.e. you take 32K chunks of your CLOB and with
> >> UTL_RAW.CAST_TO_RAW make a 32K raw buffer out of it, which you can write out
> >> in binary mode. No character set conversion stuff will be done, but usually
> >> that's fine when DB and DB server OS use the same character set by default. If
> >> not, you can still use iconv on the output file afterwards, if necessary.
>
> > Sounds feasible.  I've never used that package, so I'll need to find
> > an example, and one that works with PL/SQL variables.
>
> OK, so here's a quick one, just because I don't have anything better to do
> right now ;-)
>
> On my Windows box with 11g XE in SQL*Developer (sorry german messages ;-)
>
> As SYS:
>
> create directory xe_out as 'D:\Oracle\XE_output';
>
> grant read, write on directory xe_out to ps;
>
> grant execute on sys.utl_file to public;
>
> As PS:
>
> create table test_text (txt varchar2(100) not null);
>
> table TEST_TEXT erstellt.
>
> desc test_text
>
> Name Null     Typ
> ---- -------- -------------
> TXT  NOT NULL VARCHAR2(100)
>
> insert into test_text(txt)
> select dbms_random.string('a', trunc(dbms_random.value(10, 40)))
> from (select 1 from dual connect by level <= 20000);
>
> 20.000 Zeilen eingefügt.
>
> commit;
>
> select count(*), sum(length('"' || txt || '";')) from test_text
>
> 20000   547830
>
> DECLARE
>    fh UTL_FILE.FILE_TYPE;
>    my_raw_buf    RAW(32767);
>    total_bytes   NUMBER := 0;
>    buffer_bytes  NUMBER := 0;
> BEGIN
>    fh := UTL_FILE.FOPEN('XE_OUT', 'testfile.txt', 'wb', 32767);
>    FOR r_text IN
>       (SELECT '"' || txt || '";' AS txt
>          FROM test_text
>         ORDER BY txt)
>    LOOP
>      my_raw_buf := UTL_RAW.CAST_TO_RAW(r_text.txt);
>      buffer_bytes := buffer_bytes + LENGTH(my_raw_buf);
>      total_bytes := total_bytes + LENGTH(my_raw_buf);
>      UTL_FILE.PUT_RAW(fh, my_raw_buf, FALSE);
>      IF (buffer_bytes > 32000)
>      THEN
>        buffer_bytes := 0;
>        UTL_FILE.FFLUSH(fh);
>      END IF;
>    END LOOP;
>    UTL_FILE.FCLOSE(fh);
> END;
>
> Gives me a file D:\Oracle\XE_output\testfile.txt with exactly 547830 bytes
> (see above SUM(LENGTH(...)) and everything in a single line.
>
> In the PUT_RAW call, you should not use "TRUE" i.e. flush buffer after every
> write, but instead collect well around 32K before flushing, otherwise it's
> slow as hell if you flush for every some 20 or 30 bytes.
>
> This way it took under a second.
>
> Regards
> Peter
>
> --
> The only way to keep your health is to eat what you don't want, drink what
> you don't like, and do what you'd rather not. -- Mark Twain

Use DBMS_XSLPROCESSOR.clob2file(...);
Thomas Received on Wed Dec 21 2011 - 07:33:59 CST