Writing lines > 32k

[ExecMan]

Hi,

I have a query which constructs a line that is > 32k. I need to write
that out to a file. Oracle does not seem to like what I'm doing
here. Does anyone have a good solution to solve the 32k issue?

Thanks!

DECLARE
CURSOR data_crs IS
SELECT
os.signature_name||'|'||os.signature_date||'|'||
TO_CHAR(os.date_of_last_update,'MM/DD/YYYY')||'|'||
f4.sequence||'|'||f4.url||'|'||f4.security_title||'|'||
TO_CHAR(f4.transaction_date,'MM/DD/YYYY')||'|'||
f4.deemed_execution_date||'|'||f4.transaction_form_type||'|'||
f4.transaction_code||'|'||
f4.equity_swap_involved||'|'||f4.transaction_timeliness||'|'||
f4.transaction_shares||'|'||
f4.transaction_price_per_share||'|'||
f4.trans_acquired_disposed_code||'|'||
f4.shares_owned_after_transaction||'|'||
f4.direct_or_indirect_ownership||'|'||f4.nature_of_ownership||'|'||
TO_CHAR(f4.date_of_last_update,'MM/DD/YYYY')||'|'||
fn.xml_location||'|'||fn.footnote_id||'|'||
fn.footnote_id_description||'|'||TO_CHAR(fn.date_of_last_update,'MM/DD/
YYYY') line
FROM footnotes fn, owner_signature os, non_derivative_form_4 f4
WHERE f4.transaction_code = 'P'
AND fn.url = os.url
AND fn.url = f4.url
AND f4.date_of_last_update > ADD_MONTHS(SYSDATE,-6);

v_file_id UTL_FILE.FILE_TYPE;

BEGIN
v_file_id := OPEN_FILES('/tmp','insider.csv','w');
FOR v_rec IN data_crs LOOP
UTL_FILE.PUT_LINE(v_file_id,v_rec.line);
END LOOP;
UTL_FILE.FCLOSE_ALL;
END;
/

[Gerard H. Pille]

Maybe you could win the ugliest piece of code contest with this!

Anyhow, I believe you can specify the line length when opening the file, but I think you'd
better use the functions that allow to write a CLOB or BLOB to a file.

[Mladen Gogala]

On Mon, 09 Apr 2012 13:12:34 -0700, ExecMan wrote:

> Hi,
>
> I have a query which constructs a line that is > 32k. I need to write
> that out to a file. Oracle does not seem to like what I'm doing here.
> Does anyone have a good solution to solve the 32k issue?

For things like that, there is always the pathologically eclectic rubbish
lister, lovingly known as "Perl". That's the thingy that is very, very
good ad writing things to files, among other things.

--
http://mgogala.freehostia.com

[Peter Schneider]

Am 09.04.2012 22:12, schrieb ExecMan:
> Hi,
>
> I have a query which constructs a line that is> 32k. I need to write
> that out to a file. Oracle does not seem to like what I'm doing
> here. Does anyone have a good solution to solve the 32k issue?

On Dec 20th, 2011 you asked a similar question in this group and I answered
and also gave a piece of code to do that. Don't you read the answers to your
postings?

Search for "Re: Writing to file".

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