Skip to Main Content

SQL Developer

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!

Small issue with the formatter

themarmanSep 15 2014

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

]'

;

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 13 2014
Added on Sep 15 2014
0 comments
405 views