When I run following query on Oracle 19, it returns 28 record instead of 30, and I cannot understand why, as I am expecting 30 records:
select *
from json_table('{"values":[[1,120674124,200000000,838654366,564288,100000000,419327183,505596,8400000,335460,10063848,1300680,99335724,168528,0,0,0,0,0],
[2,126739164,210000000,897502964,544740,105000000,448751482,554376,3780000,358992,4480116,888180,112167972,184788,3780000,0,0,0,0],
[3,133130616,220500000,970973099,550812,110250000,485486549,622968,3969000,388380,4920396,788340,117714072,207648,3969000,0,0,0,0],
[4,139806252,231525000,1035145287,567648,115762500,517572643,668448,4167444,414048,5139432,827088,123631896,222804,4167444,0,0,0,0],
[5,146842056,243101250,1109915313,573948,121550625,554957656,735984,4375812,443964,5555376,691104,129844740,245316,4375812,0,0,0,0],
[6,154208088,255256312,1165411077,591588,127628156,582705538,790728,4594608,0,1886592,539184,140947212,263568,4594608,0,0,0,0],
[7,161931576,268019127,1223681630,621504,134009563,611840815,840084,4824336,0,1904796,563484,148073016,280020,4824336,0,0,0,0],
[8,170041944,281420083,1284865711,652920,140710041,642432855,892428,5065560,0,1871364,586812,155609832,297468,5065560,0,0,0,0],
[9,178558572,295491087,1349108995,685932,147745543,674554497,947952,5318832,0,1894632,613800,163462620,315972,5318832,0,0,0,0],
[10,187515948,310265641,1416564445,776052,155132820,708282222,1017432,5584776,0,1885812,643716,171684252,339132,5584776,0,0,0,0],
[11,196907940,325778923,1487392666,844440,162889461,743696333,1080444,3909336,0,1130172,558972,183160416,360144,5864016,0,0,0,0],
[12,206770416,342067869,1561762299,978972,171033934,780881149,1147272,4104804,0,867960,578052,192553728,382416,6157212,0,0,0,0],
[13,217159740,359171262,1639850412,1092876,179585631,819925206,1242744,4310052,0,592632,598344,202443780,414240,6465072,0,0,0,0],
[14,228045444,377129825,1721842931,1249656,188564912,860921465,1325664,4525548,0,303540,620256,212790564,441888,6788328,0,0,0,0],
[15,240046536,395986316,1807935075,1401864,197993158,903967537,1434276,4751832,0,0,666948,223643400,478092,7127748,197993158,903967537,406788,135588],
[16,252098868,415785631,1898331825,1585032,207892815,949165912,1543500,4989420,0,0,707208,234705588,514488,7484136,207892815,949165912,427128,142368],
[17,264796704,436574912,1993248413,1763736,218287456,996624206,1690344,5238888,0,0,750720,246333264,563436,7858344,218287456,996624206,448488,149484],
[18,278113452,458403657,2092910831,1977216,229201828,1046455415,1832544,5500836,0,0,796824,258516060,610848,8251260,229201828,1046455415,470904,156960],
[19,292133640,481323839,2197556370,2186376,240661919,1098778185,2010060,5775876,0,0,846204,271322028,670020,8663820,240661919,1098778185,494448,164808],
[20,306872916,505390030,2307434187,2389104,252695015,1153717093,2210004,6064680,0,0,898092,284785128,736668,9097020,252695015,1153717093,519168,173052],
[21,322356636,530659531,2422805893,2606952,265329765,1211402946,2425560,3183948,0,0,818148,302234808,808512,9551868,265329765,1211402946,545136,181704],
[22,338674188,557192507,2543946186,2866104,278596253,1271973093,2696628,3343152,0,0,872988,317203812,898872,10029456,278596253,1271973093,572388,190788],
[23,355846248,585052132,2671143492,3039396,292526066,1335571746,3010224,3510312,0,0,928008,333022632,1003404,10530936,292526066,1335571746,601008,200328],
[24,373890684,614304738,2804700666,3278532,307152369,1402350333,3349824,3685824,0,0,988788,349572228,1116600,11057484,307152369,1402350333,631056,210348],
[25,392897280,645019974,2944935696,3563520,322509987,1472467848,3751368,3870108,0,0,1056588,366911424,1250448,11610348,322509987,1472467848,662616,220860],
[26,412920252,677270972,3092182479,3807876,338635486,1546091239,4222512,4063620,0,0,1128252,385171968,1407504,12190872,338635486,1546091239,695736,231912],
[27,434000436,711134520,3246791602,4068012,355567260,1623395801,4759272,4266804,0,0,1206012,404339484,1586412,12800412,355567260,1623395801,730524,243504],
[28,456213816,746691246,3409131180,4344924,373345623,1704565590,5382252,4480140,0,0,1291188,424458072,1794072,13440432,373345623,1704565590,767052,255684],
[29,479662344,784025808,3579587737,4711032,392012904,1789793868,6129744,4704144,0,0,1389096,445498764,2043240,14112456,392012904,1789793868,805404,268464],
[30,504303660,823227098,3758567122,5028468,411613549,1879283561,6929868,4939356,0,0,1489920,467660448,2309952,14818080,411613549,1879283561,845676,281892]]}',
'$.values[*]'
columns(x1 number path '$[0]', x2 number path '$[1]', x3 number path '$[2]', x4 number path '$[3]', x5 number path '$[4]',
x6 number path '$[5]', x7 number path '$[6]', x8 number path '$[7]', x9 number path '$[8]', x10 number path '$[9]',
x11 number path '$[10]', x12 number path '$[11]', x13 number path '$[12]', x14 number path '$[13]', x15 number path '$[14]',
x16 number path '$[15]', x17 number path '$[16]', x18 number path '$[17]', x19 number path '$[18]'))
if I remove some of rows from the json then last rows (29, and 30) are returned in query result, and as you see all rows have exactly 19 numbers.
Can anyone help me with this?
Your string is too long.
Stripping all the white-space then:
Outputs:
Because the string is 4266 characters long and
VARCHAR2(and literals) are limited to 4000 bytes.Why your query does not throw that syntax error, I do not know; but it may be a bug.
If you do:
(Which, due to the white-spaces, is an even longer string but is created from two shorter literals concatenated to an empty CLOB.)
Then the output is all 30 rows:
fiddle