Re: Writing to file

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Tue, 20 Dec 2011 21:00:36 +0100
Message-ID: <jcqpgo$e6l$1_at_online.de>

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
> CLOB to hold the data while the script runs with no luck. Does
> anyone have a better idea? I just need to string all of these values
> in a delimited line and write it to file. There are 13,500 records in
> the table, so, we are talking a VERY long line.
>
> DECLARE
> CURSOR ticker_crs IS
> SELECT ticker FROM master_table
> WHERE type = 'S';
>
> v_tickers CLOB;
> v_file_id UTL_FILE.FILE_TYPE;
>
> BEGIN
> FOR v_rec IN ticker_crs LOOP
> v_tickers := v_tickers || v_rec.ticker || ',';
> END LOOP;
> v_tickers := RTRIM(v_tickers,',');
>
> v_file_id := OPEN_FILES('/tmp','tickers.txt','w');
> UTL_FILE.PUT_LINE(v_file_id,v_tickers);
> UTL_FILE.FCLOSE_ALL;
> END;
> /
>
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at line 16

Hi,

the error message comes from the fact that your call implicitly typecasts v_tickers from CLOB to VARCHAR2 because this is the parameter data type for the buffer parameter in UTL_FILE.PUT_LINE. If CLOB is longer than 32K this gives the error.

OTOH, if you read 32K chunks from your CLOB and try to write the chunks with UTL_FILE.PUT, then you'll get "ORA-29285: file write error" if the are no line feeds at least every 32K.

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.

HTH
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 - 14:00:36 CST