Oracle version details
=======================
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE 10.2.0.5.0 Production"
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
We have a remedy application inserting/updating a clob column(C1000000151) in a table(T792). The table has a trigger on it doing nothing just inserting a log in some other table.
We are getting error- OCI_INVALID_HANDLE when application team access some component.
The application is working fine when i disable the trigger.
Looking at the logs I could see that the application is emptying the clob field and then updating it.
I tried to search what causes OCI_INVALID_HANDLE error, but i did not get any thing.
Below are the logs that would helps.
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2498 */UPDATE T792 SET C1000000151 = EMPTY_CLOB() WHERE C1 = 'AWL000000408115'
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > 0: Set Fields
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > v7 Remote Sequence (800200352) = 9
<SQL > <TID: 0000000052> <RPC ID: 0000019543> <Queue: List > <Client-RPC: 390696 > <USER: Remedy Application Service > /* Mon Apr 29 2019 16:29:47.2527 */OK
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2536 */ <Filter Level:0 Number Of Filters:13> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetModifiedTimeDifference" (17)
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Failed qualification
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2571 */ <Filter Level:0 Number Of Filters:14> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetV7ReversibleFlag" (17)
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Disabled -- filter is ignored
<SQL > <TID: 0000000052> <RPC ID: 0000019544> <Queue: List > <Client-RPC: 390696 > <USER: Remedy Application Service > /* Mon Apr 29 2019 16:29:47.2572 */SELECT * FROM ( SELECT T146.C1,C400129200,C179,C400127400 FROM T146 WHERE ((1 = 1) AND (T146.C400079600 = 'BMC.CORE:BMC_ELEMENTLOCATION') AND (T146.C400127400 = 'TLS.CISYNC.INBOUND') AND (T146.C400129200 = '0')) ORDER BY 3 ASC, 1 ASC ) WHERE ROWNUM <= 20001
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2606 */ <Filter Level:0 Number Of Filters:15> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetV9ReversibleFlag" (17)
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Disabled -- filter is ignored
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2645 */ <Filter Level:0 Number Of Filters:16> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetSequenceLookup" (18)
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Passed -- perform actions
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > 0: Set Fields
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > Lookup_LocalSeq# (800200386) = 1
<SQL > <TID: 0000000052> <RPC ID: 0000019544> <Queue: List > <Client-RPC: 390696 > <USER: Remedy Application Service > /* Mon Apr 29 2019 16:29:47.2676 */OK
<SQL > <TID: 0000000052> <RPC ID: 0000019544> <Queue: List > <Client-RPC: 390696 > <USER: Remedy Application Service > /* Mon Apr 29 2019 16:29:47.2678 */SELECT COUNT(*) FROM T146 WHERE ((1 = 1) AND (T146.C400079600 = 'BMC.CORE:BMC_ELEMENTLOCATION') AND (T146.C400127400 = 'TLS.CISYNC.INBOUND') AND (T146.C400129200 = '0'))
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2689 */ <Filter Level:0 Number Of Filters:17> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetStatustoSkip" (25)
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Failed qualification
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2727 */OK
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2727 */ <Filter Level:0 Number Of Filters:18> Checking "TLS:ITAM:SMSyncTMS_TASK_SetStatustoSkip" (25)
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2729 */SELECT C1000000151 FROM T792 WHERE C1 = 'AWL000000408115' FOR UPDATE
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > --> Failed qualification
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2764 */OK
<SQL > <TID: 0000000052> <RPC ID: 0000019544> <Queue: List > <Client-RPC: 390696 > <USER: Remedy Application Service > /* Mon Apr 29 2019 16:29:47.2765 */OK
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2765 */Set LOB into the above row ...
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2767 */*** ERROR *** Error - OCI_INVALID_HANDLE
<SQL > <TID: 0000000075> <RPC ID: 0000019301> <Queue: Fast > <Client-RPC: 390620 > <USER: SMSYNC > /* Mon Apr 29 2019 16:29:47.2767 */ROLLBACK WORK
<FLTR> <TID: 0000000012> <RPC ID: 0000000970> <Queue: Escalation> <Client-RPC: 390603 > <USER: AR_ESCALATOR (Pool 5) > /* Mon Apr 29 2019 16:29:47.2767 */ <Filter Level:0 Number Of Filters:19> Checking "TLS:ITAM:SMSyncTMS_COMMON_SetStatustoHold" (28)