My Oracle Support Banner

SQL Script Fails With ORA-01704: String Literal Too Long (Doc ID 2450967.1)

Last updated on NOVEMBER 10, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

The following query is failing with ora-01704: string literal too long:

SELECT xdkowner.dbms_xmlquery.getxml
('SELECT c.kyc_score,c.kyc_category,c.cust_lastname,c.cust_id,c.cust_firstname,c.cust_initial,c.cust_title,c.cust_address,c.cust_aptno,c.cust_city,
c.cust_prov,c.cust_pcode,c.cust_country,c.home_branch_transit,c.cust_current_address_years,c.cust_current_address_months,c.cust_location,
c.cust_language,c.cust_gender,c.cust_homephone,c.cust_workphone,c.cust_workphone_ext,c.cust_primary_contact,c.bns_customer,c.cust_dob,c.cust_sin,
c.cust_marital_status,c.cust_citizenship,c.cust_scotiacardno,c.cust_emp_name,c.cust_emp_street,c.cust_emp_apt,c.cust_emp_city,c.cust_emp_prov,
c.cust_emp_pcode,c.cust_emp_phone,c.cust_emp_bustype,c.cust_emp_title,c.cust_emp_status,c.cust_emp_lengthyears,c.cust_emp_lengthmonths,
c.cust_spouse_lastname,c.cust_spouse_fname,c.cust_spouse_title,c.cust_spouse_sin,c.cust_spouse_dob,c.cust_spouse_emp_name,
c.cust_spouse_emp_title,c.cust_spouse_emp_phone,c.cust_emp_spouse_lengthyears,c.cust_emp_spouse_lengthmonths,CID,
h.hst_id as history_id,to_char(h.call_date,''MM/DD/YYYY'') as transaction_date,h.gdtm as transaction_duration,h.op_id as compliance_agent_id,h.io as call_type,
h.disp_text as disposition_code,to_char(h.call_time, ''HH24:MI:SS'') as starttime,
h.notes1||h.notes2||h.notes3||h.notes4||h.notes5||h.notes6||h.notes7||h.notes8 as postcall_comments,h.channel_id AS channel_id,
decode(h.marketing,9,''OTHER''||''-''||h.mrket_othr,h.marketing) as mark_howhear,
i.inv_id as refno,i.op_id as agent_id,i.registered as plan_type,i.mf_acct as account_status,i.sef_inv as transaction_amount,
i.mf_amt_c as fund_amount_cash,mf_amt_i as fund_amount_income,mf_amt_g as fund_amount_growth,
i.c_fcm_fnd as fund_code_cash,i.i_fcm_fnd as fund_code_income,i.g_fcm_fnd as fund_code_growth,
i.c_hold as cust_current_holdings_cash,i.i_hold as cust_current_holdings_income,i.g_hold as cust_current_holdings_growth,
i.ttl_hold as cust_current_holdings_total,i.grand_hold as cust_current_holdings_grand,i.plan_no as account_number,
i.v_amt as variance_amount,i.v_pct as variance_percent,i.chq_amt as sof_cheque_amount,i.sb_acct as sof_bns_accountno,
i.sb_trans as sof_bns_transit,i.sb_amt as sof_bns_amount,i.sdbi_acct as sof_sdbi_accountno,i.sdbi_amt as sof_sdbi_amount,
i.loan_amt as sof_loan_amount,i.t2033_amt as sof_tari_amount,i.newfnd_src as new_source_of_funds,
i.sef_inv as funding_required,i.fcm_fnd as fund_code_fc,i.solo as have_solo,i.solo_op as solo_action,i.ef_date as effectivedate,
i.nsef_pay as nrsp_income_distribution,i.nsef_inscd as nrsp_institution_code,i.nsef_acct as nrsp_account_number,
i.nsef_trans as nrsp_transit,i.ben_lname as ben_lastname,i.ben_fname as ben_firstname,i.ben_initl as ben_initial,
i.ben_title,i.ben_addr1 as ben_address,i.ben_city,i.ben_prov as ben_province,i.ben_pcode as ben_postalcode,
i.ben_rlate as ben_relationship,decode(i.curr_prod||i.prodstatus,''RSEF1'',''COMPLETE'',''RSEF2'',''SUSPENDED'',
''RSEF3'',''PENDING'',''RSEF4'',''PENDING'',''RSEF5'',''PENDING'',''RSEF9'',''DECLINED'',''SUB.RSEF1'',''COMPLETE'',
''SUB.RSEF2'',''SUSPENDED'',''SUB.RSEF3'',''PENDING'',''SUB.RSEF4'',''PENDING'',''SUB.RSEF5'',''PENDING'',
''SUB.RSEF9'',''DECLINED'',''NSEF1'',''COMPLETE'',''NSEF2'',''SUSPENDED'',''NSEF3'',''PENDING'',''NSEF4'',''PENDING'',
''NSEF5'',''PENDING'',''NSEF9'',''DECLINED'',''SUB.NSEF1'',''COMPLETE'',''SUB.NSEF2'',''SUSPENDED'',
''SUB.NSEF3'',''PENDING'',''SUB.NSEF4'',''PENDING'',''SUB.NSEF5'',''PENDING'',''SUB.NSEF9'',''DECLINED'',
''TSEF1'',''COMPLETE'',''TSEF2'',''SUSPENDED'',''TSEF3'',''PENDING'',''TSEF4'',''PENDING'',
''TSEF5'',''PENDING'',''TSEF9'',''DECLINED'',''SUB.TSEF1'',''COMPLETE'',''SUB.TSEF2'',''SUSPENDED'',
''SUB.TSEF3'',''PENDING'',''SUB.TSEF4'',''PENDING'',''SUB.TSEF5'',''PENDING'',''SUB.TSEF9'',''DECLINED'') as trans_status,
i.curr_prod as product_type,to_char(i.write_date,''MM/DD/YYYY'') as update_date,
i.campgn_no||i.campgn_gic||i.campgn_cpd as campaign_num,i.plan_no as prod_accountnum
FROM <tables>
WHERE c.cust_id=h.cust_id
AND h.hst_id=i.hst_id
AND i.curr_prod in (''RSEF'',''SUB.RSEF'',''NSEF'',''SUB.NSEF'',''TSEF'',''SUB.TSEF'')
AND i.write_date >= to_date(sysdate - 13)
AND i.write_date <= to_date(sysdate - 9)
')
FROM dual;
exit;

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.