Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Script output limit

Abu DinaJul 26 2014 — edited Jul 28 2014

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.

Could someone kindly advise on this?

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;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 25 2014
Added on Jul 26 2014
10 comments
668 views