OM03863;Medium;<DATABASE>;SQLIDs Changing After Software Upgrades (Doc ID 2271992.1)

Last updated on JUNE 01, 2017

Applies to:

Oracle Utilities Customer Care and Billing - Version 2.3.1 and later
Information in this document applies to any platform.

Goal

After applying the following patches we notice SQLIDs 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms