GRC Upgrade To Version 8.6.6.13000 Fails with Error 'java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis' (Doc ID 2623942.1)

Last updated on NOVEMBER 13, 2023

Applies to:

Symptoms

GRC ( Governance Risk and Compliance) upgrade From 8.6.6.12002 to version 8.6.6.13000 fails with the following error. Weblogic version: 12.1.2.0 and Java version: 1.7.

ERROR
-----------------------
2019-12-12 05:15:14,364 DEBUG [Thread-39] DBConfigManager:1677 Executing view scripts file db/oracle/onpremise/views/grc_crview_perspective.txt
2019-12-12 05:15:15,558 ERROR [Thread-39] DBConfigUtil:1445 Executing the following SQL = CREATE OR REPLACE VIEW GRC_BUSINESS_ENTITY_ASMT_V AS select flex_template_module_id, flex_object_type_key, assessment_id, activity_code assessment_activity_code, to_char(DBMS_LOB.SUBSTR(result_summary, 1, 4000)) as assessment_result_summary, last_update_date ASSESSMENT_LAST_UPDATE_DATE, last_updated_by ASSESSMENT_LAST_UPDATED_BY, creation_date ASSESSMENT_CREATION_DATE, created_by ASSESSMENT_CREATED_BY, RESULT_ID, PROCESS_ID as ENTITY_ID, DECODE(RESPONSE_CODE,NULL,'NOT_STARTED',RESPONSE_CODE) RESPONSE_CODE, STATE_CODE ASMT_STATE_CODE, 'GRC_PROCESS' as COMPONENT_TYPE from grc_asmt_procrslt_v union select flex_template_module_id, flex_object_type_key, assessment_id, activity_code assessment_activity_code, to_char(DBMS_LOB.SUBSTR(result_summary, 1, 4000) as assessment_result_summary, last_update_date ASSESSMENT_LAST_UPDATE_DATE, last_updated_by ASSESSMENT_LAST_UPDATED_BY, creation_date ASSESSMENT_CREATION_DATE, created_by ASSESSMENT_CREATED_BY, RESULT_ID, risk_id as ENTITY_ID , DECODE(RESPONSE_CODE,NULL,'NOT_STARTED',RESPONSE_CODE) RESPONSE_CODE, STATE_CODE ASMT_STATE_CODE, 'GRC_RISK' as COMPONENT_TYPE from grc_asmt_riskrslt_v union select flex_template_module_id, flex_object_type_key, assessment_id, activity_code assessment_activity_code, to_char(DBMS_LOB.SUBSTR(result_summary, 1, 4000) as assessment_result_summary, last_update_date ASSESSMENT_LAST_UPDATE_DATE, last_updated_by ASSESSMENT_LAST_UPDATED_BY, creation_date ASSESSMENT_CREATION_DATE, created_by ASSESSMENT_CREATED_BY, RESULT_ID, control_id as ENTITY_ID , DECODE(RESPONSE_CODE,NULL,'NOT_STARTED',RESPONSE_CODE) RESPONSE_CODE, STATE_CODE ASMT_STATE_CODE, 'GRC_CONTROL' as COMPONENT_TYPE from grc_asmt_ctrlrslt_v union SELECT -1 AS flex_template_module_id, '-1' AS flex_object_type_key, assessment_id, activity_code assessment_activity_code, TO_CHAR(DBMS_LOB.SUBSTR(result_summary, 1, 4000) AS assessment_result_summary, last_update_date ASSESSMENT_LAST_UPDATE_DATE, last_updated_by ASSESSMENT_LAST_UPDATED_BY, creation_date ASSESSMENT_CREATION_DATE, created_by ASSESSMENT_CREATED_BY, RESULT_ID, PERSPECTIVE_ITEM_ID AS ENTITY_ID, DECODE(RESPONSE_CODE,NULL,'NOT_STARTED',RESPONSE_CODE) RESPONSE_CODE, STATE_CODE ASMT_STATE_CODE, 'GRC_PERSP_ITEM' AS COMPONENT_TYPE FROM grc_asmt_prsprslt_v
2019-12-12 05:15:15,559 ERROR [Thread-39] DBConfigUtil:1446 Sql exception =
java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis


2019-12-12 05:15:15,842 DEBUG [Thread-39] ConnectionPool:443 ##### Connection usage is 0% (Active: 0; Idle: 3; Max: 50) Pool Description: ConnectionPool #1, user:EGRCM, Url:jdbc:oracle:thin:@10.80.0.87:1521:FCGRCPRD, Pool creation date Thu Dec 12 05:14:41 CST 2019
2019-12-12 05:15:16,000 INFO [Thread-39] DBConfigManager:2783 *************************************
2019-12-12 05:15:16,000 INFO [Thread-39] DBConfigManager:2784 Overall summary of schema updates:
2019-12-12 05:15:16,000 INFO [Thread-39] DBConfigManager:2785 *************************************
2019-12-12 05:15:16,000 INFO [Thread-39] DBConfigManager:2796 Status of Patch Updates: Success
2019-12-12 05:15:16,001 INFO [Thread-39] DBConfigManager:2802 Status of Data Migration Updates: Success
2019-12-12 05:15:16,001 INFO [Thread-39] DBConfigManager:2808 Status of Views Update: Failed
2019-12-12 05:15:16,001 INFO [Thread-39] DBConfigManager:2823 *************************************
2019-12-12 05:15:16,001 INFO [Thread-39] DBConfigManager:2829 Overall Status of updates: FAILED
2019-12-12 05:15:16,001 INFO [Thread-39] DBConfigManager:2830 *************************************




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Apply <Patch:30471829> as per the installation guide of the patch.

2. Review the grc.log.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot upgrade to GRC 8.6.6.13000.

Cause

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