I have the following script which essentially loops through a record set of just over 2.5k records and outputs an xml format result.
The problem that I have is when I run the below I get an error message saying error at line 13 and the output is truncated.
However, if I comment out when of the printed lines (the one with the most characters) the output is generated in full.
I have changed the Max Rows to print in a script and Max lines in Script output to 50000000 and 1001000000 respectively but to no avail.
Getting so frustrated with SQL Developer / Oracle in general. Simple things that I could do in a flash in SQL Server are nothing but a pain the backside these days.
ALTER SESSION SET CURRENT_SCHEMA = nucleus;
SET SERVEROUTPUT ON SIZE UNLIMITED;
SET FEEDBACK OFF;
BEGIN
dbms_output.put_line('<FSAHSFFeedRI xmlns="http://www.fsa.gov.uk/XMLSchema/FSAHSFFeedRI-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.fsa.gov.uk/XMLSchema/FSAHSFFeedRI-v1-2 \\Noss\Common\Talisman\Projects\PSD\Development\FSAREF~1\FSAHSFFeedRI-v1-2.xsd" >');
dbms_output.put_line('<FSAFeedHeader xmlns="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2 \\Noss\Common\Talisman\Projects\PSD\Development\FSAREF~1\FSAFeedCommon-v1-2.xsd">');
dbms_output.put_line('<FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion>');
dbms_output.put_line('<Submitter>');
dbms_output.put_line('<SubmittingFirm>110397</SubmittingFirm>');
dbms_output.put_line('</Submitter>');
dbms_output.put_line('<ReportDetails>');
dbms_output.put_line('<ReportCreationDate>' || to_char(sysdate, 'YYYY-MM-DD') || '</ReportCreationDate>');
dbms_output.put_line('<ReportIdentifier>' || to_char(sys_guid) || 'Sanl</ReportIdentifier>');
dbms_output.put_line('</ReportDetails>');
dbms_output.put_line('</FSAFeedHeader>');
FOR CITI_PSD_WORKING IN (
select row_number() over (order by aclk_account_number ) AS row_num
,'NFG' AS Provider
,aclk_account_number AS PolicyNumber
,prlk_short_name AS ProductLine
,to_char(aclk_commence_date, 'YYYY/MM/DD') AS AccountCommenceDate
,to_char(clnt_date_of_birth, 'YYYY/MM/DD') AS PayerDOB
,addr_post_code AS PayerPostcode
,NVL((SELECT actr_gross_amount
FROM tscm_account_transaction
WHERE actr_id =(Select min(actr_id)
from tscm_account_transaction
where actr_transaction_type_id IN (157,158,159,335,336,694,150993,151003,150996,151729)
and actr_account_id = tscm_account_link.aclk_id
)
), 0) AS SingleContrib
,pdtl_contribution_amount AS RegContrib
,Case pdtl_contrib_frequency_id when 1 then 'Monthly' else 'Single' end AS ContributionFrequency
,brsp_outlet_id AS TalismanOutletIDRef
,to_char(sysdate, 'YYYY/MM/DD') AS DateExtracted
,(select schm_short_name
From tscm_scheme_product_bridge,
tscm_scheme
Where spbx_scheme_id = schm_id
and spbx_id = tscm_account_link.aclk_scheme_product_id
) AS scheme
,' ' AS transtype
from tscm_account_link,
tscm_product_link,
tscm_client_acct_rltnshp,
tscm_client,
tscm_address,
tsmf_payment_detail,
tscm_broker_rltnshp_group,
tscm_broker_rltnshp_split
where aclk_product_link_id = prlk_id
and prlk_short_name in ('ISA')
and aclk_commence_date >= to_date('2014-01-01', 'YYYY-MM-DD')
and aclk_commence_date <= to_date('2014-03-31', 'YYYY-MM-DD')
and aclk_id = carb_account_id
and carb_relationship_type_id = 2
and carb_client_id = clnt_id
and addr_client_id = clnt_id
and addr_is_primary = 'Y'
and pdtl_car_id = carb_id
and brgp_table_id = aclk_id
and brgp_table_code_id = 509
and brgp_id = brsp_broker_rltnshp_group_id
and brgp_effective_from_date = (select max(bg.brgp_effective_from_date)
from tscm_broker_rltnshp_group bg
where bg.brgp_table_id = tscm_account_link.aclk_id
AND brgp_table_code_id = 509)
and exists (select *
from tscm_account_transaction t
where t.actr_account_id = tscm_account_link.aclk_id) order by policynumber)
LOOP
dbms_output.put_line('<FSAHSFFeedRIMsg>');
dbms_output.put_line('<CoreItems xmlns="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2 \\Noss\Common\Talisman\Projects\PSD\Development\FSAREF~1\FSAFeedCommon-v1-2.xsd">');
dbms_output.put_line('<FirmFSARef>' || CITI_PSD_WORKING.row_num || '</FirmFSARef>');
dbms_output.put_line('<TransRef>' || CITI_PSD_WORKING.PolicyNumber || '</TransRef>');
dbms_output.put_line('</CoreItems>');
dbms_output.put_line('<RetailInvestment>');
dbms_output.put_line('<TypePolicy>' || '</TypePolicy>');
dbms_output.put_line('<AdvisedSale>' || '</AdvisedSale>');
dbms_output.put_line('<CustPostCode>' || CITI_PSD_WORKING.PayerPostcode || '</CustPostCode>');
dbms_output.put_line('<PremPaymentMethod>' || '</PremPaymentMethod>');
dbms_output.put_line('<TotalPremAmt>' || CITI_PSD_WORKING.SingleContrib || '</TotalPremAmt>');
dbms_output.put_line('<CustDOB>' || REPLACE(CITI_PSD_WORKING.PayerDOB, '/', '-') || '</CustDOB>');
dbms_output.put_line('<DateInForce>' || REPLACE(CITI_PSD_WORKING.AccountCommenceDate, '/', '-') || '</DateInForce>');
dbms_output.put_line('</RetailInvestment>');
dbms_output.put_line('</FSAHSFFeedRIMsg>');
END LOOP;
dbms_output.put_line('</FSAHSFFeedRI>');
END;