Writing to file

From: ExecMan <artmerar_at_yahoo.com>
Date: Tue, 20 Dec 2011 10:25:01 -0800 (PST)
Message-ID: <3da09476-44cb-4bf0-8257-bfe15286dac5_at_32g2000yqp.googlegroups.com>



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 Received on Tue Dec 20 2011 - 12:25:01 CST

Original text of this message