-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
7.7.x, 7.8.0-alpha3
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries.
Workaround would be to split the IN Expression and combine them with an or OR use a
https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is
Or to delete in batches.
The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances.
This bug exist also for normal activity instances, case instances etc.
Executing:
<delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete>
Results in ORA-01795: maximum number of expressions in a list is 1000.
Maybe it is possible to avoid the IN expressions in general.
Note: We have to fix all deleteHistoric*By*Ids statements which contains IN expressions
This is the controller panel for Smart Panels app
[CAM-8149] On oracle it is not possible to cascade delete deployment with more than 1000 historic instances
| Summary | Original: On oracle it is not possible to cascade delete deployment with more than 1000 historic instances | New: On oracle it is not possible to cascade delete deployment with more than 1000 historic decision instances |
| Fix Version/s | New: 7.5.x [ 14200 ] | |
| Fix Version/s | New: 7.6.x [ 14597 ] | |
| Fix Version/s | New: 7.7.x [ 14699 ] |
| Fix Version/s | New: 7.4.x [ 14196 ] |
| Affects Version/s | New: 7.4.x [ 14196 ] |
| Summary | Original: On oracle it is not possible to cascade delete deployment with more than 1000 historic decision instances | New: On oracle it is not possible to cascade delete deployment with more than 1000 historic instances |
| Description |
Original:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains a decision definition with more than 1000 historic decision instances. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Note: check if there are more expressions like this and fix them too |
New:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances. This bug exist also for normal activity instances, case instances etc. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Note: check if there are more expressions like this and fix them too |
| Description |
Original:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances. This bug exist also for normal activity instances, case instances etc. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Note: check if there are more expressions like this and fix them too |
New:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances. This bug exist also for normal activity instances, case instances etc. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Maybe it is possible to avoid the IN expressions in general. Note: check if there are more expressions like this and fix them too |
| Description |
Original:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances. This bug exist also for normal activity instances, case instances etc. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Maybe it is possible to avoid the IN expressions in general. Note: check if there are more expressions like this and fix them too |
New:
On oracle there is a limitation of maximum number of expressions in a list,
which means it is not possible to have a IN expression with more then 1000 entries. Workaround would be to split the IN Expression and combine them with an or OR use a https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is Or to delete in batches. The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances. This bug exist also for normal activity instances, case instances etc. Executing: {code:xml} <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds"> delete from ${prefix}ACT_GE_BYTEARRAY where ID_ in (select distinct BYTEARRAY_ID_ from ${prefix}ACT_HI_DEC_IN where DEC_INST_ID_ in <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")"> #{decisionInstanceId} </foreach> and BYTEARRAY_ID_ is not NULL) </delete> {code} Results in {{ORA-01795: maximum number of expressions in a list is 1000}}. Maybe it is possible to avoid the IN expressions in general. Note: We have to fix all deleteHistoric*By*Ids statements which contains IN expressions |
| Link | New: This issue is depended on by SUPPORT-3585 [ SUPPORT-3585 ] |