Re: Writing to file

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Tue, 20 Dec 2011 23:33:05 +0100
Message-ID: <jcr2em$l21$1_at_online.de>

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

Received on Tue Dec 20 2011 - 16:33:05 CST