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

Am 20.12.2011 19:25, schrieb ExecMan:
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

On Dec 20, 2:00*pm, Peter Schneider <pschneider1... (AT) googlemail (DOT) com>
wrote:

Sounds feasible. I've never used that package, so I'll need to find
an example, and one that works with PL/SQL variables.

On Tue, 20 Dec 2011 12:48:58 -0800, ExecMan wrote:

You can also try this:

http://mgogala.byethost5.com/dump2csv.zip

It's not a PL/SQL package, but works. You will also need Text::CSV
package from CPAN (Comprehensive Python Archive Network). It uses array
interface in DBD::Oracle, so it's fast, probably faster than a PL/SQL
script.



--
http://mgogala.byethost5.com

Am 20.12.2011 21:48, schrieb ExecMan:
[...]

[...]


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

On Dec 20, 5:33*pm, Peter Schneider <pschneider1... (AT) googlemail (DOT) com>
wrote:
Use DBMS_XSLPROCESSOR.clob2file(...);
Thomas

Am 21.12.2011 14:33, schrieb Thomas Olszewicki:
[code]

Hmm nice one, didn't know that yet. But it requires installation of XML DB,
methinks!?

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

On Dec 21, 2:47*pm, Peter Schneider <pschneider1... (AT) googlemail (DOT) com>
wrote:
Yes, it does require XMLDB, but it is standard part of 11gXE
T.

On Dec 21, 3:42*pm, Thomas Olszewicki <Thom... (AT) cpas (DOT) com> wrote:
XDB is in fact installed by default will 11g (EE, STD, XE) when DBCA
is used to create the database.

HTH -- Mark D Powell --

Am 22.12.2011 16:56, schrieb Mark D Powell:

DBA I know seems to enchant some "CREATE DATABASE..." magic and then runs
dictionary scipts according to his personal taste... ;-)

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