Re: Writing to file
From: ExecMan <artmerar_at_yahoo.com>
Date: Tue, 20 Dec 2011 12:48:58 -0800 (PST)
Message-ID: <bac60016-2bce-4a71-9138-a1eb442474c4_at_f33g2000yqh.googlegroups.com>
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
> > 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
Date: Tue, 20 Dec 2011 12:48:58 -0800 (PST)
Message-ID: <bac60016-2bce-4a71-9138-a1eb442474c4_at_f33g2000yqh.googlegroups.com>
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
> > 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
Sounds feasible. I've never used that package, so I'll need to find an example, and one that works with PL/SQL variables. Received on Tue Dec 20 2011 - 14:48:58 CST