This query just locked up PL SQL Developer (when I hit the beautifier button) in the middle of trying to figure out a production issue... Now I have to kill it and start all over....
SELECT /*+ parallel(a 4) */ A.CONTROL_PROJ_ID, A.RECORD_TYPE, A.PROJ_DESC, FY_CD, PD_NO, SUB_PD_NO, T.SEQUENCE, T.ALERT_GROUP, T.ALERT_CATEGORY, T.ALERT_TYPE, T.ALERT_COMMENT, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.ALERT_MESSAGE, ':data1', A.DATA1), ':data2', A.DATA2), ':data3', A.DATA3), ':data4', A.DATA4), ':data5', A.DATA5), ':data6', A.DATA6), ':data7', A.DATA7), ':data8', A.DATA8), ':data9', A.DATA9), ':data10', A.DATA10), ':data11', A.DATA11), ':data12', A.DATA12), ':data13', A.DATA13), ':data14', A.DATA14), ':data15', A.DATA15), ':data16', A.DATA16), ':data17', A.DATA17), ':data18', A.DATA18), ':data19', A.DATA19), ':data20', A.DATA20) MESSAGE FROM FDS_PCS.PCS_ALERTS_CURRENT A JOIN LST_PCS_ALERT_TYPES T ON T.ALERT_CATEGORY = A.ALERT_CATEGORY AND T.ALERT_TYPE = A.ALERT_TYPE AND T.ALERT_GROUP = A.ALERT_GROUP WHERE (:B4 IS NULL OR (:B4 IS NOT NULL AND CONTROL_PROJ_ID = :B5 AND PROJ_DESC = :B4 AND A.RECORD_TYPE = :B3 )) AND A.FY_CD = '0000' AND A.PD_NO = 0 AND A.SUB_PD_NO = 0 AND (:B2 IS NULL OR (:B2 IS NOT NULL AND A.ALERT_CATEGORY = :B2 )) AND (:B1 IS NULL OR (:B1 IS NOT NULL AND A.ALERT_TYPE = :B1 )) UNION ALL SELECT A.CONTROL_PROJ_ID, A.RECORD_TYPE, A.PROJ_DESC, FY_CD, PD_NO, SUB_PD_NO, T.SEQUENCE, T.ALERT_GROUP, T.ALERT_CATEGORY, T.ALERT_TYPE, T.ALERT_COMMENT, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.ALERT_MESSAGE, ':data1', A.DATA1), ':data2', A.DATA2), ':data3', A.DATA3), ':data4', A.DATA4), ':data5', A.DATA5), ':data6', A.DATA6), ':data7', A.DATA7), ':data8', A.DATA8), ':data9', A.DATA9), ':data10', A.DATA10), ':data11', A.DATA11), ':data12', A.DATA12), ':data13', A.DATA13), ':data14', A.DATA14), ':data15', A.DATA15), ':data16', A.DATA16), ':data17', A.DATA17), ':data18', A.DATA18), ':data19', A.DATA19), ':data20', A.DATA20) MESSAGE FROM FDS_PCS.PCS_ALERTS_REAL_TIME A JOIN LST_PCS_ALERT_TYPES T ON T.ALERT_CATEGORY = A.ALERT_CATEGORY AND T.ALERT_TYPE = A.ALERT_TYPE AND T.ALERT_GROUP = A.ALERT_GROUP AND :B6 IS NOT NULL AND A.MODIFIED_BY = UPPER(:B6 ) UNION ALL SELECT /*+ INDEX(a fds_pcs.pcs_alerts_hist_idx1) */ A.CONTROL_PROJ_ID, A.RECORD_TYPE, A.PROJ_DESC, FY_CD, PD_NO, SUB_PD_NO, T.SEQUENCE, T.ALERT_GROUP, T.ALERT_CATEGORY, T.ALERT_TYPE, T.ALERT_COMMENT, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.ALERT_MESSAGE, ':data1', A.DATA1), ':data2', A.DATA2), ':data3', A.DATA3), ':data4', A.DATA4), ':data5', A.DATA5), ':data6', A.DATA6), ':data7', A.DATA7), ':data8', A.DATA8), ':data9', A.DATA9), ':data10', A.DATA10), ':data11', A.DATA11), ':data12', A.DATA12), ':data13', A.DATA13), ':data14', A.DATA14), ':data15', A.DATA15), ':data16', A.DATA16), ':data17', A.DATA17), ':data18', A.DATA18), ':data19', A.DATA19), ':data20', A.DATA20) MESSAGE FROM FDS_PCS.PCS_ALERTS_HIST A JOIN LST_PCS_ALERT_TYPES T ON T.ALERT_CATEGORY = A.ALERT_CATEGORY AND T.ALERT_TYPE = A.ALERT_TYPE AND T.ALERT_GROUP = A.ALERT_GROUP WHERE (:B4 IS NULL OR (:B4 IS NOT NULL AND CONTROL_PROJ_ID = :B5 AND PROJ_DESC = :B4 AND A.RECORD_TYPE = :B3 )) AND (:B12 IS NULL AND A.FY_CD = :B13 OR (:B12 IS NOT NULL AND A.FY_CD = :B12 )) AND (:B10 IS NULL AND A.PD_NO = :B11 OR (:B10 IS NOT NULL AND A.PD_NO = :B10 )) AND ( ( T.ALERT_TYPE = 'AMOUNT_DISCREPANCY' AND ( (:B9 = 'PERIOD' AND A.SUB_PD_NO = 0) OR (:B9 != 'PERIOD' AND ( (:B7 IS NULL AND A.SUB_PD_NO = :B8 ) OR (:B7 IS NOT NULL AND A.SUB_PD_NO = :B7 ) ) ) ) ) OR ( T.ALERT_TYPE != 'AMOUNT_DISCREPANCY' AND ( (:B7 IS NULL AND A.SUB_PD_NO = :B8 ) OR (:B7 IS NOT NULL AND A.SUB_PD_NO = :B7 ) ) ) ) AND (:B2 IS NULL OR (:B2 IS NOT NULL AND A.ALERT_CATEGORY = :B2 )) AND (:B1 IS NULL OR (:B1 IS NOT NULL AND A.ALERT_TYPE = :B1 )) ORDER BY SEQUENCE
If it can't handle the query then fine, tell me so. Don't just lock up like that.
Last edited by TheOracle; 11/30/15 04:55 PM.
