0 Replies Latest reply on Sep 15, 2014 9:32 PM by themarman

    Small issue with the formatter

    themarman

      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')))

      ]'

      ;