OM03863;Medium;<DATABASE>;SQLIDs Changing After Software Upgrades
(Doc ID 2271992.1)
Last updated on SEPTEMBER 27, 2022
Applies to:
Oracle Utilities Customer Care and Billing - Version 2.3.1 and laterInformation in this document applies to any platform.
Goal
**Examples provided in this article do not represent real life personal/confidential information**
**Disclaimer:** This KM article may include the following abbreviations:
CCB - Oracle Utilities Customer Care and Billing
SQL - Standard Query Language
JDBC - Java Data Base Connectivity
After applying the following patches we notice SQL_IDs are changing due to an extra space being inserted between host variables in a where predicate
CCB Third Party (PRE-REQ) Software Upgrade includes the following:
1) java version 1.6.0_101
Java(TM) SE Runtime Environment (build 1.6.0_101-b14)
Java HotSpot(TM) 64-Bit Server VM (build 20.101-b01, mixed mode)
2) jrockit.version R28.0.1
java.vm.version = R28.3.7-10-167188-1.6.0_101-20150702-2210-linux-x86_64
3) WebLogic Server 10.3.6.0
WebLogic Server 10.3.6.0.12 PSU Patch for BUG20780171
Found the "same" SQL in CCBPROD. It has a different SQL_ID because the SQL running in POC somehow has additional space in between.
In POC, SQL_ID=bf2c6knwqgwnj
SELECT SASPFA.SA_SP_ID, SASPFA.SA_SP_FA_TYPE_FLG, SASP.SA_ID, SASP.SP_ID, SASP.START_DTTM FROM CI_SA_SP_FA SASPFA, CI_SA_SP SASP, CI_FA FA WHERE SASPFA.FA_ID = :1 AND SASPFA.SA_SP_FA_TYPE_FLG = :2 AND FA.FA_ID = SASPFA.FA_ID AND SASP.SA_SP_ID = SASPFA.SA_SP_ID UNION SELECT SASPFA.SA_SP_ID , SASPFA.SA_SP_FA_TYPE_FLG , SASP.SA_ID , SASP.SP_ID , SASP.STOP_DTTM FROM CI_SA_SP_FA SASPFA , CI_SA_SP SASP , CI_FA FA WHERE SASPFA.FA_ID = :3 AND SASPFA.SA_SP_FA_TYPE_FLG = :4 AND FA.FA_ID = SASPFA.FA_ID AND SASP.SA_SP_ID = SASPFA.SA_SP_ID ORDER BY 5
In CCBProd, SQL_ID=g14zw2779wsz1
SELECT SASPFA.SA_SP_ID, SASPFA.SA_SP_FA_TYPE_FLG, SASP.SA_ID, SASP.SP_ID, SASP.START_DTTM FROM CI_SA_SP_FA SASPFA, CI_SA_SP SASP, CI_FA FA WHERE SASPFA.FA_ID = :1 AND SASPFA.SA_SP_FA_TYPE_FLG = :2 AND FA.FA_ID = SASPFA.FA_ID AND SASP.SA_SP_ID = SASPFA.SA_SP_ID UNION SELECT SASPFA.SA_SP_ID , SASPFA.SA_SP_FA_TYPE_FLG , SASP.SA_ID , SASP.SP_ID , SASP.STOP_DTTM FROM CI_SA_SP_FA SASPFA , CI_SA_SP SASP , CI_FA FA WHERE SASPFA.FA_ID = :3 AND SASPFA.SA_SP_FA_TYPE_FLG = :4 AND FA.FA_ID = SASPFA.FA_ID AND SASP.SA_SP_ID = SASPFA.SA_SP_ID ORDER BY 5
The issue occurred after applying jdbc driver by a <Patch 17786094> - JDBC DRIVER UPDATE
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| References |