I have some execute immediate code using quoted literal syntax of the form q'[xxxxxx]'. On formatting this statement SQLDeveloper splits the the q'[ string into a q on one line and '[ to start the next line rendering the statement un-executable. It seems to only do this when the the string reaches a certain length which I haven't been able to pin down.
Before formatting:
SQLSTMNT:=
q'[
After formatting:
SQLSTMNT:=q
'[
SQLSTMNT:=
q'[
CREATE MATERIALIZED VIEW "REVENUE_DM"."MV_PNC_PROMPT_PROGRAM_CC" ("TP_ID", "TP_LEGAL_NAME", "DOMICILE_DSC", "TP_ULT_PARENT_ID", "TP_ULT_PARENT_LEGAL_NAME", "PROFIT_CENTER_DIM_ID", "PROGRAM_SUM_REF", "PROGRAM_SUM_DSC", "PROGRAM_DSC", "PROGRAM_SEQUENCE_GROUP_ID", "CONTRACT_DIM_ID", "CONTRACT_REF", "CONTRACT_DSC", "CONTRACT_SEQUENCE_GROUP_ID", "CONTRACT_TYPE_DSC", "DD_APPLICATION_ID", "CLM_CLIENT", "CLM_MARKET", "PRM_CLIENT", "PRM_MARKET", "TP_ACCT_CODE_TYPE", "MULTI_CLIENT_IND", "LATEST_PROGRAM_DSC", "CONTRACT_KEY", "FAC_CONTRACT_IND", "MAX_EFF_DT", "CONTRACT_DIM_KEY", "CED_CONTRACT_KEY")
TABLESPACE "REVENUE_DM_TBS"
PARALLEL 4
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT t.TP_ID,
t.TP_LEGAL_NAME,
t.DOMICILE_DSC,
t.TP_ULT_PARENT_ID,
t.TP_ULT_PARENT_LEGAL_NAME,
p.PROFIT_CENTER_DIM_ID,
cd.PROGRAM_SUM_REF,
cd.PROGRAM_SUM_DSC,
cd.PROGRAM_SUM_REF
||'-'
||cd.PROGRAM_SUM_DSC program_dsc,
cd.program_sequence_group_id,
cd.contract_dim_id,
cd.CONTRACT_REF,
cd.CONTRACT_REF
||' / '
|| NVL(cd.CONTRAC_SUM_DSC,'No Description')
||' / '
||NVL(cd.IMPACT_CURR_CODE,'No Currency')
||' / '
||NVL(cd.CONTRACT_DSC,'No Description')
||' / '
||'Eff Date: '
||TO_CHAR(cd.CONTRACT_EFF_DT,'YYYY-MM-DD') contract_dsc,
cd.contract_sequence_group_id,
ccd.contract_type_dsc,
cd.dd_application_id,
'Y' AS Clm_Client,
'N' AS Clm_Market,
'N' AS Prm_Client,
'N' AS Prm_Market,
t.TP_ACCT_CODE_TYPE,
CASE
WHEN mcv.program_sum_ref IS NULL
THEN 'N'
ELSE 'Y'
END Multi_client_ind,
cd.program_sum_ref
||'-'
||first_value (cd.program_sum_dsc) over(partition BY cd.program_sum_ref order by cd.contract_eff_dt DESC) latest_program_dsc,
'~'
||cd.program_sum_ref
||'~'
||TO_CHAR( t.tp_id)
||'~' contract_key ,
CASE
WHEN ccd.CONTRACT_TYPE_CODE='00003'
THEN 'Y'
ELSE 'N'
END fac_contract_ind,
MAX(trunc(cd.CONTRACT_EFF_DT)) over(partition BY cd.program_sum_ref) max_eff_dt,
'~'
||TO_CHAR(cd.contract_dim_id)
||'~'
||TO_CHAR( t.tp_id)
||'~' contract_dim_key,
'~'
||TO_CHAR(cd.contract_dim_id)
||'~'
||TO_CHAR( t.tp_id)
||'~' ced_contract_key
FROM REVENUE_DM.TRADING_PARTNER_DIM t
JOIN REV_DM_STAGING.temp_clm_accum c
ON t.TP_DIM_ID = c.CEDENT_DIM_ID
JOIN REVENUE_DM.profit_center_dim p
ON p.PROFIT_CENTER_DIM_ID = c.PROFIT_CENTER_DIM_ID
JOIN REVENUE_DM.contract_dim cd
ON c.contract_dim_id=cd.contract_dim_id
JOIN REVENUE_DM.contract_classification_dim ccd
ON c.contract_classification_dim_id=ccd.contract_classification_dim_id
LEFT OUTER JOIN revenue_dm.MV_MULTI_CLIENT_PGM mcv
ON cd.program_sum_ref =mcv.program_sum_ref
WHERE c.REINSURER_DIM_ID = -2
AND (( cd.DD_APPLICATION_ID <> 13)
AND (cd.DD_APPLICATION_ID <> 10
OR cd.DD_GC_ENTITY_ID NOT IN ('100','101','102','103','104','105','106','107','108','109')))
]'
;