My Oracle Support Banner

Large Hierarchy Filter Does Not Allow Deselection of Nodes ORA-01795: maximum number of expressions in a list is 1000 Error (Doc ID 2786824.1)

Last updated on JULY 23, 2021

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 8.0.7.4.0 to 8.1.1.1 [Release 8 to 8.1]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
Oracle Financial Services Funds Transfer Pricing (FTP)
Oracle Financial Services Asset Liability Management (ALM)
Oracle Financial Services Enterprise Performance Management (EPM)

Symptoms

On OFSAAI 8.0.7.4.0, for one large org unit hierarchy filter that selects MANY nodes, deselection of nodes and save gives spin. The FUSIONSUBSYSTEMSService.log shows the following error:

ERROR

[08-01-21 16:58:09,997 GMT PM] [INFO ] [WEB] [AMHM] [NA] [AMHMINTERNALCLIENT] Query to filter deselect ancestor path :SELECT parent_id,child_depth_num FROM (SELECT rev_hier_b.child_id, rev_hier_b.parent_id,rev_hier_b.child_depth_num FROM DIM_ORG_UNIT_HIER rev_hier_b WHERE rev_hier_b.hierarchy_id = ? AND rev_hier_b.single_depth_flag = 'Y') hier_view START WITH hier_view.child_id = ? CONNECT BY NOCYCLE hier_view.child_id = PRIOR hier_view.parent_id AND child_id NOT IN ( 503092,503094,503096,503102,503104,503108,503110,503118,503124,503152,503156,503372,503380,503388,503800,503806,503812,503934,504410,504712,504994,505312,506792,507690,507692,507710,511176,512352,512618,512768,512776,512962,513030,513234,513870,514644,517764,518136,520836,522032,522522,522716,524568,525032,525072,525572,529364,529468,530912,531060,534786,536208,536592,539816,540898,543114,546306,546346,547270,547424,549182,549814,549990,551344,552424,553210,554190,556422,556512,558086,558830,565952,566780,567082,567264,567808,568284,568358,569862,571662,573090,574770,576752,577734,578574,582048,582776,582964,583764,584508,584878,586078,586866,588294,589478,589524,591092,593814,594030,595538,596308,598992,601006,601264,601952,602186,602966,603274,603526,604440,606764,608208,608872,610142,610480,611386,611886,616000,616196,618594,618894,620448,622106,625638,626904,627992,628186,630176,631164,635440,638382,642168,642646,642836,643032,644844,646336,646732,648942,650694,651278,651728,652106,653848,655418,659098,659138,659536,659870,659978,662294,662414,663628,664242,665006,666550,667338,667746,668058,668060,669076,670842,671722,675124,676024,676892,677896,678164,679388,680122,680260,681532,684128,685560,685788,685834,686458,690858,694316,695262,695296,695932,698254,699878,700532,701298,702184,703226,703914,705066,705738,705770,708010,708024,710904,711448,726394,728408,728586,728588,728590,728592,729496,729498,729616,729712,730196,732012,732124,732186,732248,732268,732300,732346,732420,732450,732514,732698,732816,732876,732946,742788,742896,748992,748994,748996,749054,757210,757246,757514,759070,759752,760170,762194,762200,762204,762206,762208,762210,762212,762248,762262,762264,762270,762330,762710,762716,762722,762774,762776,762780,762796,762800,762830,762838,762848,762852,764480,764482,764818,765256,766526,766944,768268,775574,775576,776506,776508,776510,776512,776518,776522,776526,776528,776532,776540,776542,776668,776670,776678,776680,776684,776710,776716,776720,776722,776730,776740,776752,776764,776768,776770,776772,776776,776780,776782,776784,776882,776942,776948,776968,777592,778934,778954,779008,779018,779040,781156,781162,781164,781190,781194,781196,784308,784618,784626,784630,784734,786214,790610,796928,797032,797040,797100,797102,797628,797632,797776,798040,798212,799694,800268,800794,800988,801310,801342,801348,803532,803534,803896,803952,803954,803966,806120,807960,807962,813608,815646,815654,815656,815658,815660,815662,815670,815674,815676,815680,815692,817090,817100,817102,817104,817106,817122,817124,817126,817128,824778,824784,824840,825002,825038,826532,826574,826626,826656,828448,828766,828880,828908,829254,829572,829852,835256,835266,835280,837404,837412,837432,837442,837532,837544,837554,837560,837578,837584,837602,837612,837616,837666,837672,837680,837690,837742,837762,837764,837784,837788,837804,838080,838412,838500,838526,838574,838590,838670,838732,838770,838898,838962,838964,839068,839286,839372,839652,839670,839772,839776,839880,840356,840364,841154,841384,841462,842024,842028,842050,842052,842056,842058,842060,842062,842064,842070,842074,842076,842078,842082,842084,842086,842088,842090,842098,842102,842106,842110,842116,842118,842120,842122,842128,842134,842138,842140,842144,842146,842236,842252,842304,842356,842676,842680,843708,843750,844194,844370,844810,845892,846248,846262,846310,846316,846328,846342,846352,846368,846382,846394,846412,846416,846432,846436,846440,846456,846462,846466,846470,846476,846478,846480,846502,846506,846516,846526,846544,846554,846558,846570,846578,846650,846814,846818,846868,846900,846906,846926,846936,846960,847162,847204,847826,847846,847848,848206,848248,849502,850112,852050,853502,853504,853506,853510,853516,853522,853586,853590,853602,853630,853648,853662,853666,865428,865590,865602,865614,865662,865668,865692,865714,865738,866310,866456,866916,866954,866980,868110,868114,868720,868732,868766,868772,869316,869326,869334,869340,869352,869358,870384,870772,871696,872074,872082,872556,872562,873070,873076,873078,873080,873084,873088,873090,873100,873110,873112,873134,873146,873150,873152,873180,873198,873926,873928,874234,874310,874314,874326,874482,875520,875574,875576,875616,875668,875700,875792,875822,875830,875868,875880,875904,876024,876032,876046,876086,876118,876120,876160,876170,876172,876188,876254,876266,876270,876298,876326,876340,876342,876402,876520,876558,876570,876578,876628,876636,876668,876672,876678,877386,878084,879034,879186,882264,882284,882286,882976,883166,883340,883504,883552,883678,883866,884294,884310,884398,885678,886474,892636,892680,892768,892782,892788,892796,892834,892872,892874,892876,892880,892882,892886,892894,892900,892902,892906,892910,892912,892914,892916,892924,892926,892928,892930,892934,892938,892940,892944,892950,892952,893024,893756,894644,898032,898034,898036,898038,898042,898056,898072,898074,898076,898080,898082,898088,898090,898094,898102,898104,898108,898114,898118,898122,898124,898128,898140,898142,898146,898156,898164,898166,898168,898178,898180,898184,898188,898190,898192,898194,898242,898260,898286,899902,899916,899920,899934,899938,899996,900000,900020,900812,901520,901978,903572,906532,907780,908134,908270,917278,917514,917750,922910,922916,922918,922920,922926,922930,922932,922934,922938,922942,922946,922950,922954,922956,922958,922964,922968,924378,924552,925476,925488,925496,925544,925546,925550,925558,925572,925580,925584,925592,925614,925622,925630,925650,925658,925686,925692,925776,925820,925870,925874,925888,925920,925970,926004,926018,926186,926234,926416,928104,928112,928124,928174,928658,928892,928930,928986,929290,929298,929300,929306,929308,929310,929312,929338,929342,929344,929350,929354,929358,929366,929370,929378,929380,929384,929404,929406,929410,929418,929424,929432,929434,929448,929450,929460,929462,929472,929474,929476,929478,929480,929530,929550,929554,929558,929566,929578,929618,930158,930836,931446,931454,931474,931482,931484,931486,931488,931496,931794,931798,932274,932610,932622,932702,932710,932714,933170,933172,933174,933176,933178,933180,933182,933184,933186,933188,933190,933192,933196,933198,933202,933204,933206,933208,933212,933214,933218,933220,933222,933224,933226,933228,933230,933234,933238,933242,933244,933246,933248,933250,933252,933256,933258,933264,933266,933270,933274,933276,933278,933280,933282,933284,933286,933288,933294,933296,933298,933300,933302,933304,933306,933308,933310,933882,933924,933958,934036,934070,934072,934074,934186,934356,934550,934752,934766,934788,934790,934792,934800,934808,934814,934818,934830,934832,934836,934838,934840,934842,934844,934848,934850,934856,934858,934864,934868,934870,934872,934876,934878,934880,934920,934924,935132,935134,936628,938970,938974,938976,938982,938984,938988,938990,938992,938996,939000,939004,939008,939010,939014,939016,939018,939020,939024,939026,939028,939032,939036,939038,939040,939042,939044,939046,939048,939050,939052,939056,939058,939060,939062,939064,939066,939070,939072,939074,939076,939078,939080,939086,939088,939090,939092,939094,939096,939098,939102,939104,939106,951124,951168,951242,951384,951412,951456,951680,951810,951820,951960,951972,951976,951982,952002,952024,952030,952060,952068,952102,952148,952150,952152,952172,952184,952210,952242,952256,952276,952284,952318,952326,952336,952342,952346,952382,952388,952424,952480,952506,952514,952548,952614,952618,952638,952654,952660,952662,952670,952688,952694,952738,952746,952750,952762,952766,952780,952786,952806,952828,952882,953066,953080,953084,953394,953438,953530,953580,953786,953984,954036,954252,954380,955020,955062,955724,956554,957234,958258,958260,979420,979470,981598,985184,985226,985228,985232,985234,985236,985238,985574,988386,988406,988410,988634,2588903,2588925,2590367,2590527,2950703,2950785,3969399,6465889,6470861,6568943,6568951,6568977,6568983,6568985,6568997,6569009,6569013,6569015,6569027,6569045,6569263,6644709,6650783,6654003,6656529,6671445,6689703,6807213,6832989,6832991,6874385,6880721,6889233,6889235,6889321,6889395 ) ORDER BY CHILD_DEPTH_NUM
[08-01-21 16:58:09,997 GMT PM] [INFO ] [WEB] [AMHM] [NA] [CONNECTIONS] Getting tomcat connection from pool
[08-01-21 16:58:09,998 GMT PM] [ERROR] [WEB] [AMHM] [NA] [AMHMINTERNALCLIENT] Exception occur while getting Hierarchy Members For Parent
[08-01-21 16:58:09,999 GMT PM] [ERROR] [WEB] [AMHM] [NA] [AMHMINTERNALCLIENT]
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)


The issue appears to be confined to a single hierarchy filter which attempts to select most orgs and just excludes a few.

The issue can be reproduced at will with the following steps:
1. Common Object Maintenance -> Filters -> Edit large org hierarchy filter

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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