Re: Writing lines > 32k

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Mon, 09 Apr 2012 23:08:15 +0200
Message-ID: <4f834f85$0$3118$ba620e4c_at_news.skynet.be>

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?
>
> 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;
> /
>

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. Received on Mon Apr 09 2012 - 16:08:15 CDT