ORA-01795: maximum number of expressions in a l...

1. Re: ORA-01795: maximum number of expressions in a list is 1000 error

Hoek Sep 11, 2009 8:09 AM (in response to 708078)

Hi,

Maybe BETWEEN could do it?

select itemid from item where itemid between some_min_id and some_max_id;

2. Re: ORA-01795: maximum number of expressions in a list is 1000 error

673860 Sep 11, 2009 8:10 AM (in response to 708078)

one work around may be like this

make a temporary table and insert these values in it (1,2,3,...........) and then modify your statement as

sql>select itemid from item where itemid in (select <column-name> from <temp-table>)

3. Re: ORA-01795: maximum number of expressions in a list is 1000 error

584412 Sep 11, 2009 8:11 AM (in response to 708078)

How to over come this problem?

[Trying to keep straight face] Erm, don't exceed the 1000 expression limit in the IN clause? There are a variety of possibilities. You could load a table with tha values that you want to use and then use a subquery (this is not limited to 1000). You could write the predicate as a constant, value set, you could split the list of expressions into multiple sets, each less than 1000 in number and combine using an Or.

4. Re: ORA-01795: maximum number of expressions in a list is 1000 error

Anurag Tibrewal Sep 11, 2009 8:12 AM (in response to 708078)

Hi,

If you have more than 1000 entries in List then how about creating a global temporary table and inserting the entire list in global temporary table. After this you can join this global temporary table to your main table.

Regards
Anurag Tibrewal.

5. Re: ORA-01795: maximum number of expressions in a list is 1000 error

MichaelS Sep 11, 2009 8:49 AM (in response to 708078)

Or

 select itemid from item where itemid in (select * from table(sys.odcinumberlist(1,2,3,...........)))

6. Re: ORA-01795: maximum number of expressions in a list is 1000 error

584412 Sep 11, 2009 9:08 AM (in response to MichaelS)

Maybe I'm missing something, but it seems that the call to sys.odcinumberlist is limited to 999 arguments.

BANNER                                                           
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi 
PL/SQL Release 10.2.0.3.0 - Production                           
CORE     10.2.0.3.0     Production                                         
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio 
NLSRTL Version 10.2.0.3.0 - Production

7. Re: ORA-01795: maximum number of expressions in a list is 1000 error

MichaelS Sep 11, 2009 9:14 AM (in response to 584412)

but it seems that the call to sys.odcinumberlist is limited to 999 arguments.

No: You can have up to 32767 entries:

SQL> desc sys.odcinumberlist
 sys.odcinumberlist VARRAY(32767) OF NUMBER

8. Re: ORA-01795: maximum number of expressions in a list is 1000 error

584412 Sep 11, 2009 9:23 AM (in response to MichaelS)

Yeah, I just looked that up. Thanks Michael. I'm quite obviously being dim here. I'll play with this later (and thanks for yet another very cool solution to this almost daily asked question)

9. Re: ORA-01795: maximum number of expressions in a list is 1000 error

708078 Sep 11, 2009 9:43 AM (in response to MichaelS)

Hi michaels2 ,

when I executed the query as you told it is throwing following error

ORA-00939: too many arguments for function

I'm using Oracle11g.

thanks,
harry

Edited by: harrry on Sep 11, 2009 2:43 AM

Edited by: harrry on Sep 11, 2009 2:43 AM

10. Re: ORA-01795: maximum number of expressions in a list is 1000 error

MichaelS Sep 11, 2009 9:46 AM (in response to 708078)

What are you doing differently?:

SQL>  select ename
from emp
where empno in (select *
                from table (sys.odcinumberlist (7788,
                                                7900)))
/
ENAME     
----------
SCOTT     
JAMES     

2 rows selected.

11. Re: ORA-01795: maximum number of expressions in a list is 1000 error

708078 Sep 11, 2009 10:01 AM (in response to MichaelS)

Here is my query which consists 3325 item id's for 'IN' operator

SELECT ITEMID, Item_NAME
FROM ITEM
WHERE ITEMID IN
(SELECT * FROM table(sys.odcinumberlist(17755, 18047, 18080, 18556, 17754, 10419, 656, 668, 10525, 18509, 657, 658, 620, 623, 7783, 748, 747, 621, 624, 622, 625, 18558, 311, 312, 630, 548, 561, 813, 7223, 7221, 799, 10524, 549, 562, 575, 68, 10087, 814, 800, 550, 563, 74, 760, 69, 10088, 815, 794, 795, 796, 797, 798, 789, 790, 791, 792, 793, 801, 551, 564, 18482, 18543, 10089, 821, 552, 587, 565, 779, 823, 51, 816, 784, 785, 786, 787, 788, 780, 781, 782, 783, 421, 553, 566, 7562, 573, 576, 579, 582, 583, 554, 567, 18552, 18553, 18557, 39, 644, 647, 648, 649, 10520, 3851, 817, 3849, 803, 804, 580, 555, 568, 808, 807, 577, 581, 584, 556, 569, 818, 3850, 809, 805, 578, 585, 557, 570, 7257, 7259, 4529, 18054, 18055, 76, 558, 571, 819, 8631, 810, 806, 559, 560, 7331, 7329, 7325, 7323, 7241, 7239, 7247, 7245, 10527, 10291, 10172, 10171, 632, 7962, 10544, 7961, 10588, 7960, 7959, 10170, 631, 10522, 633, 10250, 10459, 653, 10223, 654, 629, 10458, 651, 1430, 7727, 7788, 10098, 10099, 7728, 7739, 7729, 7740
, 7741, 7742, 7743, 10505, 7761, 7730, 7744, 7745, 7746, 7747, 7763, 7732, 10568, 7748, 7749, 7750, 7751, 7764, 9538, 7731, 9541, 7752, 7753, 7754, 7755, 7765, 7733, 7756, 7757, 7758, 7759, 7766, 9539, 9542, 9540, 9543, 7734, 7735, 10543, 7736, 7737, 7738, 10506, 7760, 7769, 7771, 7773, 7774, 1280, 1424, 7726, 1219, 1230, 1245, 1256, 1265, 1281, 1216, 1220, 1247, 1257, 1266, 1427, 1198, 1213, 1217, 1221, 1232, 1248, 1258, 1267, 1273, 1304, 1214, 1218, 1222, 1249, 1259, 1268, 1432, 1199, 1215, 1433, 1223, 1234, 1250, 1260, 1269, 1274, 1200, 1299, 1292, 1224, 1251, 1261, 1270, 1201, 1225, 1236, 1252, 1262, 1271, 1300, 1226, 1253, 1202, 1435, 1434, 1227, 1238, 1254, 1263, 1272, 1276, 1203, 1204, 1519, 1305, 1229, 1243, 1297, 1264, 1306, 1160, 1172, 1177, 1161, 1298, 1162, 1173, 1178, 1163, 1155, 1164, 1174, 1179, 1165, 1156, 1166, 1175, 1180, 1167, 1157, 1168, 1176, 1181, 1169, 1279, 1170, 1182, 1171, 1183, 7767, 1158, 8230, 8229, 1159, 7776, 7777, 7778, 1205, 1207, 1209, 48, 18510,
7772, 1525, 1246, 1228, 1231, 1233, 1235, 1237, 1244, 1154, 1277, 1431, 1278, 1186, 1526, 1241, 7721, 1558, 9823, 1559, 9833, 1560, 9712, 8733, 8745, 8760, 1561, 9855, 9728, 1562, 9919, 1563, 9873, 1564, 9891, 1565, 9906, 17923, 18132, 1557, 9807, 7502, 9847, 9856, 9920, 9874, 9892, 9907, 18133, 7559, 9875, 8746, 8761, 7560, 7506, 7503, 7504, 7505, 17931, 1567, 9824, 1568, 9834, 1569, 9843, 9724, 8734, 9718, 8747, 8762, 1570, 9857, 9729, 1571, 9921, 9719, 1572, 9876, 1573, 9893, 1574, 9908, 17924, 18134, 1566, 9808, 9877, 9848, 9858, 9922, 9878, 9894, 9909, 18135, 1575, 8748, 8763, 7507, 7511, 7508, 7509, 7510, 17932, 9809, 1577, 9825, 1578, 9835, 1579, 9714, 8735, 8749, 8764, 1580, 9859, 9730, 1581, 9923, 1582, 9879, 1583, 9895, 1584, 9910, 17925, 18136, 1576, 9810, 9849, 9860, 9924, 9880, 9896, 9911, 18137, 7512, 8750, 8765, 7513, 7517, 7514, 7515, 7516, 17933, 1586, 9826, 1587, 9836, 1588, 9716, 8736, 9720, 8751, 8766, 1589, 9861, 9731, 9717, 9925, 9721, 1590, 9881, 1591, 9897
, 1592, 9912, 18138, 1585, 9811, 9882, 9850, 9862, 9926, 9883, 9898, 9913, 18139, 1594, 9827, 1595, 9837, 1596, 8737, 9722, 8752, 8767, 1597, 9863, 9732, 1598, 7518, 9723, 1599, 9884, 7519, 1600, 9899, 1601, 9914, 17926, 18140, 1593, 9812, 1603, 9828, 1604, 9838, 1605, 8738, 8753, 8768, 1606, 9864, 9733, 9927, 1607, 9885, 1608, 9900, 1609, 9915, 18141, 1602, 9813, 1611, 9829, 1612, 9839, 1613, 8739, 8754, 8769, 1614, 9865, 9734, 1615, 1616, 9886, 7520, 1617, 9901, 1618, 9916, 17927, 1610, 9814, 9830, 9840, 9842, 9851, 9866, 9928, 9887, 9902, 9917, 18142, 9815, 1619, 9816, 1620, 9817, 1621, 9818, 1622, 9819, 1527, 8729, 8740, 8755, 1528, 9725, 1529, 1530, 1531, 1532, 9867, 9841, 9844, 9852, 9868, 9888, 9903, 1534, 1535, 1536, 8730, 8741, 8756, 1537, 9726, 7492, 1538, 1539, 1540, 17928, 1533, 7493, 1541, 1542, 10117, 10118, 9845, 7495, 1543, 8728, 8731, 8742, 8757, 1544, 7496, 1545, 1546, 1547, 17929, 7494, 9869, 10115, 10116, 1549, 9820, 9821, 1550, 9831, 1551, 8732, 8743, 8758,
1552, 9853, 9727, 1553, 1554, 9870, 1555, 9889, 1556, 9904, 17922, 18130, 1548, 9805, 9871, 9822, 9832, 9846, 9854, 9918, 9872, 9890, 9905, 18131, 9806, 8744, 8759, 7497, 7501, 7498, 7499, 7500, 17930, 1750, 9941, 1751, 9951, 1752, 9759, 8775, 1753, 9972, 7527, 9775, 1754, 1755, 9992, 1756, 10010, 1757, 10025, 1749, 10039, 9973, 9993, 10011, 10026, 9994, 8776, 7528, 7529, 7530, 7531, 1759, 9942, 1760, 9952, 1761, 9959, 9771, 9765, 8777, 1762, 9974, 9776, 1763, 9766, 1764, 9995, 1765, 10012, 1766, 10027, 1758, 10040, 9996, 9965, 9975, 9997, 10013, 10028, 8778, 7532, 1767, 7533, 7534, 7535, 10041, 1769, 9943, 1770, 9953, 1771, 9761, 8779, 1772, 9976, 9777, 1773, 1774, 9998, 1775, 10014, 1776, 10029, 1768, 10042, 9966, 9977, 9999, 10015, 10030, 8780, 7536, 7537, 7538, 7539, 1778, 9944, 1779, 9954, 1780, 9763, 9767, 8781, 1781, 9978, 9778, 9764, 9768, 1782, 10000, 1783, 10016, 1784, 10031, 1777, 9929, 10001, 9967, 9979, 10002, 10017, 10032, 1786, 9945, 1787, 9955, 1788, 9769, 8782,
1789, 9980, 9779, 1790, 9770, 1791, 1792, 10018, 1793, 10033, 1785, 9930, 1795, 9946, 1796, 9956, 1797, 8783, 1798, 9981, 9780, 1799, 10004, 1800, 10019, 1801, 10034, 1794, 9931, 1803, 9947, 1804, 9957, 1805, 8784, 1806, 9781, 1807, 1808, 10005, 1809, 10020, 1810, 10035, 1802, 9932, 9948, 9958, 9968, 9983, 10006, 10021, 10036, 9933, 1811, 9934, 1812, 9935, 1813, 9936, 1814, 9937, 1719, 8770, 1720, 9772, 1721, 1722, 1723, 1724, 9986, 9961, 9969, 9987, 10007, 10022, 1726, 1727, 1728, 8771, 1729, 9773, 1730, 1731, 1732, 1725, 9960, 1733, 9985, 1734, 10119, 10120, 9962, 7522, 1735, 8772, 1736, 1737, 1738, 1739, 7521, 9988, 10113, 10114, 1741, 9938, 9939, 1742, 9949, 1743, 8773, 1744, 9970, 9774, 1745, 1746, 9989, 1747, 10008, 1748, 10023, 1740, 10037, 9990, 9940, 9950, 9963, 9971, 9991, 10009, 10024, 10038, 8774, 7523, 7524, 7525, 7526, 1, 2, 4, 5, 7, 8, 7903, 17508, 3, 6, 9, 7283, 7281, 17753, 771, 300, 7177, 586, 811, 7229, 7227, 75, 7209, 7207, 1464, 1465, 9689, 1466, 1467, 9705,
9690, 1468, 1469, 1470, 1462, 1463, 7476, 7477, 7478, 7479, 1473, 1474, 9701, 9695, 1475, 9706, 9696, 1476, 1477, 1478, 1471, 1472, 7480, 7481, 7482, 7483, 1481, 1482, 9691, 1483, 9707, 9692, 1484, 1485, 1486, 1479, 1480, 7484, 7485, 7486, 7487, 1489, 1490, 9693, 9697, 1491, 9708, 9694, 9698, 1492, 1493, 1494, 1487, 1488, 1497, 1498, 9699, 1499, 9709, 9700, 1500, 1501, 1502, 1495, 1496, 1505, 1506, 1507, 9710, 1508, 1509, 1510, 1503, 1504, 1513, 1514, 1515, 9711, 1516, 1517, 1518, 1511, 1512, 7488, 7489, 7490, 7491, 7465, 7466, 9702, 7467, 7468, 3692, 7469, 1441, 1442, 1443, 9703, 1444, 1445, 1446, 1439, 1440, 1447, 1448, 10123, 10124, 1449, 1450, 1451, 1452, 1453, 7470, 7471, 1456, 1457, 1458, 9704, 1459, 1460, 1461, 1454, 1455, 7472, 7473, 7474, 7475, 770, 766, 7963, 10536, 4352, 4354, 4356, 4358, 4360, 4362, 4364, 4366, 4368, 4370, 4372, 4374, 4376, 4378, 4380, 4382, 4384, 4386, 4388, 4390, 4392, 4394, 4396, 4398, 4400, 4402, 4404, 4406, 4408, 4410, 4412, 4414, 4416, 4418,
4420, 4422, 4424, 4426, 4428, 4430, 4432, 4434, 4436, 4438, 4440, 4442, 4444, 4446, 4448, 4450, 4452, 4454, 4456, 4458, 4460, 4462, 4464, 4466, 4468, 4470, 4472, 4474, 4476, 4478, 4480, 4482, 4484, 4486, 4488, 4490, 4492, 4494, 4496, 4498, 4500, 4502, 4504, 4506, 4508, 4510, 4512, 4514, 4516, 4518, 4520, 4522, 4526, 4528, 4595, 504, 3852, 73, 4536, 4538, 4540, 4542, 4544, 4546, 4548, 4550, 4552, 4554, 4556, 4558, 4560, 8651, 8652, 4563, 4565, 4567, 4569, 4571, 4573, 4575, 4577, 4579, 4581, 4583, 4585, 4587, 4589, 220, 221, 64, 18038, 758, 3565, 10456, 99, 212, 213, 225, 224, 761, 762, 92, 759, 18032, 94, 18599, 769, 18551, 102, 10442, 96, 43, 491, 440, 426, 470, 433, 477, 463, 456, 448, 18565, 172, 164, 492, 441, 471, 478, 464, 457, 449, 162, 170, 18566, 18567, 493, 442, 472, 479, 465, 458, 450, 165, 173, 146, 154, 169, 177, 7929, 7928, 18547, 208, 209, 18561, 4621, 4628, 4639, 4646, 4716, 4723, 4735, 4742, 4783, 4790, 4801, 4808, 4854, 4861, 4872, 4879, 4916, 4923, 4936, 4943,
4956, 4963, 5006, 5013, 5024, 5031, 5042, 5049, 5104, 5111, 5124, 5131, 5162, 5169, 5182, 5189, 5242, 5249, 8653, 8654, 5296, 5303, 5314, 5321, 5334, 5341, 5378, 5385, 5398, 5405, 5418, 5425, 5620, 5627, 5640, 5647, 7785, 103, 10443, 1017, 7876, 1018, 1033, 1042, 8638, 1019, 1043, 7877, 7883, 1289, 1020, 1035, 1044, 8639, 1021, 7875, 7878, 7884, 1290, 1022, 1037, 1046, 1023, 9061, 9062, 9063, 7879, 7885, 1429, 1024, 1039, 1047, 1025, 7880, 8025, 1026, 1040, 1048, 7881, 1028, 1049, 1520, 1030, 1050, 7882, 1032, 1051, 1066, 7873, 1015, 1016, 8227, 8226, 5828, 5830, 5832, 5834, 5836, 5838, 5840, 5842, 5844, 5846, 5848, 5850, 5852, 5854, 5856, 5858, 5860, 5862, 5864, 18576, 21, 9086, 10225, 5877, 5879, 5883, 5885, 5889, 5891, 5895, 5897, 5901, 5903, 5907, 5909, 5913, 5915, 5919, 5921, 5925, 5927, 5931, 5933, 5943, 5945, 5955, 5957, 5961, 5963, 5967, 5969, 5973, 5975, 5979, 5981, 5991, 5993, 5997, 5999, 6003, 6005, 6015, 6017, 6021, 6023, 6027, 6029, 6073, 6075, 6079, 6081, 6085, 6087
, 6091, 6093, 6109, 6111, 6115, 6117, 6127, 6129, 6133, 6135, 6139, 6141, 6145, 6147, 6157, 6159, 6163, 6165, 6169, 6171, 6181, 6183, 6187, 6189, 6193, 6195, 6199, 6201, 6205, 6207, 6211, 6213, 6217, 6219, 6229, 6231, 6235, 6237, 6247, 6249, 6253, 6255, 6265, 6267, 6271, 6273, 6283, 6285, 6289, 6291, 6295, 6297, 6301, 6303, 6307, 6309, 6313, 6315, 6319, 6321, 6331, 6333, 6337, 6339, 6343, 6345, 6349, 6351, 6355, 6357, 6361, 6363, 6367, 6369, 6373, 6375, 6379, 6381, 6391, 6393, 6397, 6399, 6403, 6405, 6409, 6411, 6415, 6417, 6421, 6423, 6427, 6429, 6433, 6435, 6475, 6477, 6481, 6483, 6499, 6501, 6505, 6507, 6511, 6513, 6517, 6519, 6523, 6525, 6529, 6531, 6535, 6537, 6553, 6555, 6559, 6561, 6565, 6567, 6571, 6573, 755, 40, 41, 7720, 192, 193, 194, 195, 217, 216, 70, 19, 20, 7923, 10226, 9085, 6590, 6593, 6597, 6600, 6610, 6613, 6617, 6620, 6624, 6627, 6631, 6634, 6644, 6647, 6651, 6654, 6668, 6671, 6675, 6678, 6682, 6685, 6689, 6692, 757, 3564, 91, 10427, 302, 7920, 93, 7967, 18481
, 7927, 6700, 6705, 6712, 6717, 6724, 6729, 6736, 6741, 6760, 6765, 6748, 6753, 6784, 6789, 6772, 6777, 6795, 6801, 6808, 6813, 6820, 6825, 6832, 6837, 6843, 6850, 6855, 6862, 6867, 6874, 6879, 6886, 6891, 6898, 6903, 6910, 6915, 6934, 6939, 98, 764, 18353, 10425, 777, 767, 18550, 97, 3859, 18511, 1286, 1077, 1091, 1291, 1107, 1138, 1126, 7924, 285, 18075, 18076, 286, 18492, 18073, 18074, 756, 7922, 1342, 1343, 1344, 9666, 1350, 1345, 9682, 1420, 1346, 1347, 1348, 1349, 1339, 1340, 1341, 3689, 7444, 10593, 7445, 7449, 7446, 7447, 7448, 17919, 1352, 1353, 1354, 9678, 1360, 1355, 9683, 1426, 9673, 1356, 1357, 1358, 1359, 1351, 7450, 7451, 7455, 7452, 7453, 7454, 17920, 1364, 1365, 1366, 9668, 1372, 1367, 9684, 1421, 1368, 1369, 1370, 1371, 1361, 1362, 1363, 7456, 7457, 7461, 7458, 7459, 7460, 17921, 1374, 1375, 1376, 9670, 1381, 1377, 9685, 9671, 9675, 1378, 1379, 1380, 1373, 1385, 1386, 1387, 10592, 1393, 1388, 9686, 1422, 7462, 9677, 1389, 7464, 1390, 1391, 1392, 1382, 1383, 1384
, 1395, 1396, 1397, 1402, 1398, 9687, 1399, 1400, 1401, 1394, 1405, 1406, 1407, 1408, 9688, 1423, 3690, 1409, 7463, 1410, 1411, 1412, 1403, 1404, 1413, 1414, 1416, 1417, 1418, 1419, 1307, 1308, 9679, 1309, 1310, 1311, 1312, 1315, 1316, 1317, 1318, 9680, 7434, 1319, 1320, 1321, 17916, 1313, 1314, 7435, 1322, 1323, 10121, 10122, 7437, 1324, 8727, 1325, 7438, 1326, 1327, 1328, 17917, 7436, 1330, 1331, 1332, 1338, 1333, 9681, 1425, 1334, 1335, 1336, 1337, 1329, 7439, 7443, 7440, 7441, 7442, 17918, 778, 768, 494, 443, 473, 480, 466, 459, 451, 18568, 167, 175, 18512, 763, 9084, 772, 6948, 6953, 6960, 6965, 6972, 6977, 6983, 6990, 6995, 7002, 7007, 7014, 7019, 7026, 7031, 7050, 7055, 188, 189, 3860, 495, 444, 430, 474, 437, 481, 467, 460, 452, 145, 153, 176, 18564, 168, 496, 431, 475, 438, 482, 468, 461, 18562, 446, 454, 171, 163, 18018, 17506, 17507, 301, 7921, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 776, 775, 7786, 3562, 7176, 7098, 7100, 7102, 7104, 7106, 7108, 7112, 7114,
7116, 7118, 7122, 7124, 7126, 7128, 7130, 7134, 7136, 7138, 7140, 18560, 10476, 10477, 10478, 31, 30, 7919, 226, 7722, 227, 228, 229, 7723, 230, 231, 10457, 18156, 17936, 18513, 18555, 282, 275, 86, 18113, 65, 7945, 10545, 537, 533, 535, 536, 534, 78, 273, 359, 298, 291, 63, 774, 62, 350, 18048, 7939, 270, 271, 295, 294, 293, 292, 7937, 7982, 18100, 10451, 10332, 349, 18526, 10452, 77, 10446, 10447, 10453, 10448, 10449, 10450, 10455, 17505, 10400, 7909, 7917, 351, 258, 7977, 7913, 305, 8133, 8131, 8132, 8124, 8122, 8123, 8157, 8154, 8156, 8151, 8148, 8150, 8145, 8142, 8144, 259, 7978, 7976, 10454, 17895, 362, 8121, 8119, 8120, 8016, 8017, 8018, 8011, 8012, 8014, 8015, 8136, 8134, 8135, 289, 290, 49, 314, 87, 313, 8076, 8082, 50, 8073, 297, 7940, 296, 529, 18469, 8155, 8152, 8153, 8149, 8146, 8147, 8143, 8140, 8141, 8127, 8125, 8126, 254, 8019, 8020, 8021, 8106, 8104, 8105, 338, 10083, 7985, 525, 7951, 250, 251, 252, 253, 7944, 7718, 7987, 240, 241, 242, 243, 239, 18527, 249, 246,
247, 248, 244, 8130, 8128, 8129, 277, 276, 279, 7725, 8802, 8812, 8823, 9782, 8912, 8924, 8939, 8836, 9798, 8900, 8854, 8871, 8886, 8788, 8855, 8925, 8940, 8837, 8901, 8856, 8872, 8887, 8803, 8813, 8824, 9794, 8913, 9788, 8926, 8941, 8838, 9799, 8902, 9789, 8857, 8873, 8888, 8789, 8927, 8942, 8839, 8903, 8858, 8874, 8889, 8804, 8814, 8825, 9784, 8914, 8928, 8943, 8840, 9800, 8904, 8859, 8875, 8890, 8790, 8860, 8929, 8944, 8841, 8905, 8861, 8876, 8891, 8805, 8815, 8826, 9786, 8915, 9790, 8930, 8945, 8842, 9801, 9787, 9791, 8862, 8877, 8892, 8791, 8806, 8816, 8827, 8916, 9792, 8931, 8946, 8843, 9802, 8906, 9793, 8863, 8878, 8893, 8792, 8807, 8817, 8828, 8917, 8932, 8947, 8844, 9803, 8864, 8879, 8894, 8793, 8808, 8818, 8829, 8918, 8933, 8948, 8845, 9804, 8907, 8865, 8880, 8895, 8794, 8795, 8796, 8797, 8798, 8819, 8908, 8919, 8934, 8831, 9795, 8847, 8866, 8881, 8848, 8799, 8809, 8820, 8909, 8920, 8935, 8832, 9796, 8896, 8849, 8867, 8882, 8785, 8830, 8846, 8850, 10125, 10126, 8800,
8810, 8821, 8910, 8921, 8936, 8833, 8897, 8851, 8868, 8883, 8786, 8801, 8811, 8822, 8911, 8922, 8937, 8834, 9797, 8898, 8852, 8869, 8884, 8787, 8923, 8938, 8835, 8899, 8853, 8870, 8885, 8966, 8976, 9735, 9736, 9049, 8988, 8998, 9752, 9737, 9004, 9019, 9034, 8952, 9050, 8989, 9005, 9020, 9035, 8967, 8977, 9748, 9742, 9051, 8990, 9753, 9743, 9006, 9021, 9036, 8953, 9052, 8991, 9007, 9022, 9037, 8968, 8978, 9738, 9053, 8992, 9754, 9739, 9008, 9023, 9038, 8954, 9054, 8993, 9009, 9024, 9039, 8969, 8979, 9740, 9744, 9055, 8994, 9755, 9741, 9745, 9010, 9025, 9040, 8955, 8970, 8980, 9746, 9056, 8995, 9756, 9747, 9011, 9026, 9041, 8956, 8971, 8981, 9057, 8996, 9757, 9012, 9027, 9042, 8957, 8972, 8982, 9058, 8997, 9758, 9013, 9028, 9043, 8958, 8959, 8960, 8961, 8962, 9044, 8983, 9749, 8999, 9014, 9029, 8963, 8973, 9045, 8984, 9750, 9000, 9015, 9030, 8949, 10131, 10127, 10128, 8964, 8974, 9046, 8985, 9001, 9016, 9031, 8950, 8965, 8975, 9047, 8986, 9751, 9002, 9017, 9032, 8951, 9048, 8987,
9003, 9018, 9033, 7724, 233, 8023, 8024, 8022, 8079, 18489, 18488, 335, 337, 10232, 336, 339, 18490, 342, 346, 345, 7981, 10084, 10246, 278, 7938, 255, 18115, 18114, 18023, 10363, 10401, 10364, 10402, 7979, 283, 281, 17722, 17721, 10245, 284, 18016, 7932, 280, 82, 18381, 304, 137, 18522, 7916, 18059, 309, 236, 321, 322, 7942, 17768, 17769, 17770, 17771, 17772, 17773, 382, 18003, 17774, 17775, 17776, 17777, 17778, 17779, 17780, 17781, 17782, 17783, 17784, 17785, 316, 306, 38, 42, 7914, 10373, 7912, 10533, 7918, 378, 7973, 514, 10241, 358, 7910, 111, 18358, 8547, 8546, 8499, 8549, 8550, 8548, 8551, 10108, 10109, 8614, 8536, 8475, 8479, 8489, 8500, 8510, 8520, 10511, 8570, 8571, 8589, 8572, 8490, 8552, 8553, 8537, 8595, 8573, 8574, 8575, 8576, 8596, 8590, 8554, 8555, 8476, 8480, 8491, 8501, 8511, 8521, 8530, 8557, 8466, 8473, 8477, 8481, 9570, 9571, 8492, 8556, 9574, 8502, 8512, 8522, 8534, 10564, 8577, 8578, 8579, 8580, 8597, 8558, 8581, 8582, 8583, 8584, 8598, 8467, 8474, 8539,
8482, 8493, 8503, 8513, 8523, 8543, 8559, 8468, 8531, 8544, 8483, 9572, 8494, 9575, 8504, 8514, 8524, 8528, 8585, 8586, 8587, 8588, 8599, 10493, 8469, 8538, 8545, 8484, 9573, 8495, 9576, 8505, 8515, 8525, 8470, 8566, 8567, 8485, 10540, 8496, 8506, 8516, 8526, 8535, 10489, 8560, 8561, 8568, 8486, 8497, 8507, 8517, 8562, 8471, 8563, 8569, 8487, 8498, 8508, 8518, 8527, 8529, 8591, 10512, 8592, 8593, 8594, 8564, 8478, 8488, 8565, 8509, 8519, 7974, 17997, 17786, 17787, 17788, 17789, 17790, 17791, 366, 17792, 7968, 17793, 17794, 17795, 17796, 17797, 7926, 7969, 17798, 17801, 17802, 17803, 17804, 17807, 17808, 17809, 17810, 17811, 17812, 17813, 17814, 17815, 18022, 10416, 523, 10365, 10403, 10339, 10335, 10342, 10344, 10345, 10347, 10349, 10352, 10354, 10356, 10355, 10343, 10346, 10348, 10350, 10351, 10353, 10357, 10358, 10359, 10360, 10361, 10362, 10313, 10327, 18598, 17816, 17817, 17818, 17819, 17820, 17821, 18004, 17822, 17823, 17824, 17825, 17826, 17827, 54, 10366, 180, 157, 178,
155, 181, 158, 185, 10404, 10367, 10405, 10406, 334, 325, 328, 10287, 88, 89, 17828, 17829, 17830, 17831, 17832, 17833, 9094, 10322, 10407, 121, 117, 113, 115, 119, 118, 120, 124, 123, 10325, 10368, 8008, 8009, 10235, 17834, 17837, 17838, 17839, 17840, 17841, 17842, 17843, 17844, 17845, 9095, 101, 79, 532, 377, 7971, 7991, 7992, 7994, 7993, 7965, 373, 17846, 17847, 17848, 17849, 17850, 17851, 320, 333, 18015, 57, 58, 138, 18111, 509, 10328, 10553, 519, 28, 7972, 520, 327, 367, 237, 107, 368, 7970, 81, 18041, 7984, 528, 527, 10460, 10314, 18001, 17852, 17853, 17854, 17855, 17856, 17857, 376, 17858, 17859, 17860, 17861, 17862, 17863, 18002, 17864, 17865, 17866, 17867, 17868, 17869, 521, 329, 18101, 318, 315, 379, 17870, 17871, 17872, 17873, 17874, 17875, 7966, 183, 160, 10369, 18478, 516, 515, 512, 128, 131, 129, 133, 130, 127, 8620, 10097, 8623, 256, 257, 18051, 232, 238, 7925, 29, 10233, 10370, 10408, 330, 8688, 15777, 18062, 7792, 2016, 7793, 18602, 8236, 2017, 2018, 2019, 2020,
2021, 2022, 2023, 2024, 10507, 2025, 2026, 2027, 2028, 9099, 2029, 2030, 2031, 2032, 2015, 2034, 10106, 10107, 7795, 7796, 18603, 7801, 2035, 2040, 2041, 2042, 2043, 2044, 2045, 2046, 9100, 2037, 2039, 2047, 2048, 2049, 2050, 2052, 2053, 7798, 7799, 18604, 2054, 2055, 2056, 2057, 2058, 2059, 2060, 2061, 2062, 2063, 2064, 2065, 2066, 2067, 2068, 2069, 2070, 2071, 2072, 9564, 9565, 9567, 18605, 10566, 2073, 2077, 2078, 2079, 2080, 2081, 2082, 2074, 9101, 2075, 2076, 2083, 2084, 2085, 2086, 2087, 2090, 2091, 18606, 2092, 2093, 2094, 2095, 2096, 2097, 2098, 9096, 2099, 2100, 2101, 2102, 2103, 2104, 2105, 2106, 2107, 2108, 2109, 9566, 9568, 18607, 9102, 10495, 2111, 2112, 2113, 2114, 8659, 2115, 2116, 9569, 2117, 2118, 2119, 18608, 2120, 2121, 2122, 2123, 2124, 10538, 2125, 2126, 9097, 2127, 2128, 2129, 2130, 18609, 10491, 2131, 9103, 2134, 2132, 2133, 2135, 2136, 2137, 2138, 2139, 7800, 18610, 2140, 2141, 2142, 2143, 2144, 9098, 2145, 2146, 2147, 2148, 7797, 18611, 2149, 10508, 2150,
2151, 2152, 18600, 2153, 2154, 2155, 2156, 2157, 7791, 7794, 18601, 2159, 2160, 2161, 2162, 2163, 10318, 2164, 2165, 2166, 2167, 2168, 2169, 2170, 2171, 2172, 2173, 2174, 8645, 2175, 2176, 2177, 2178, 2179, 2180, 2181, 2182, 2183, 2184, 2185, 2186, 2187, 2188, 2189, 2190, 2191, 2192, 2193, 2194, 2195, 2196, 2197, 10469, 2198, 2199, 2200, 2201, 2203, 2204, 2205, 2206, 2207, 2208, 2209, 2211, 2213, 2214, 8234, 8235, 17888, 2202, 2210, 2212, 17889, 17890, 10323, 184, 161, 7915, 10371, 363, 179, 156, 10372, 18049, 10382, 10388, 17750, 10374, 10380, 10384, 10392, 10423, 10378, 10546, 10412, 10413, 10409, 10411, 10548, 10549, 10389, 10398, 10414, 10547, 18069, 10550, 10394, 10397, 10375, 10410, 10285, 10424, 55, 18559, 7911, 7950, 287, 85, 18017, 7948, 17876, 17877, 17878, 17879, 17880, 17881, 526, 517, 510, 109, 112, 110, 108, 10239, 67, 361, 10247, 106, 126, 135, 134, 132, 136, 332, 524, 18021, 83, 522, 17882, 17883, 17884, 17885, 17886, 17887, 18013, 18386, 18011, 18012, 33, 308,
326, 319, 18385, 10096, 317, 18563, 18061, 18384)
))

12. Re: ORA-01795: maximum number of expressions in a list is 1000 error

MichaelS Sep 11, 2009 10:10 AM (in response to 708078)

Seems that JS1 is finally right that you can't have more than 999 entries in sys.odcinumberlist as well :(, which somehow astonishes me ..

Sorry for leading in wrong direction ....

13. Re: ORA-01795: maximum number of expressions in a list is 1000 error

ravikumar.sv Sep 11, 2009 10:18 AM (in response to 708078)

one option would be to use like ....

 SELECT       *
     FROM TABLE(sys.odcinumberlist(17755, 18047, 18080, 18556, 17754, 10419, 656, 668, 10525, 18509, 657, 658, 620, 623, 7783, 748, 747, 621, 624, 622, 625, 18558, 311, 312, 630, 548, 561, 813, 7223, 7221, 799, 10524, 549, 562, 575, 68, 10087, 814, 800, 550, 563, 74, 760, 69, 10088, 815, 794, 795, 796, 797, 798, 789, 790, 791, 792, 793, 801, 551, 564, 18482, 18543, 10089, 821, 552, 587, 565, 779, 823, 51, 816, 784, 785, 786, 787, 788, 780, 781, 782, 783, 421, 553, 566, 7562, 573, 576, 579, 582, 583, 554, 567, 18552, 18553, 18557, 39, 644, 647, 648, 649, 10520, 3851, 817, 3849, 803, 804, 580, 555, 568, 808, 807, 577, 581, 584, 556, 569, 818, 3850, 809, 805, 578, 585, 557, 570, 7257, 7259, 4529, 18054, 18055, 76, 558, 571, 819, 8631, 810, 806, 559, 560, 7331, 7329, 7325, 7323, 7241, 7239, 7247, 7245, 10527, 10291, 10172, 10171, 632, 7962, 10544, 7961, 10588, 7960, 7959, 10170, 631, 10522, 633, 10250, 10459, 653, 10223, 654, 629, 10458, 651, 1430, 7727, 7788, 10098, 10099, 7728, 7739,
    7729, 7740 , 7741, 7742, 7743, 10505, 7761, 7730, 7744, 7745, 7746, 7747, 7763, 7732, 10568, 7748, 7749, 7750, 7751, 7764, 9538, 7731, 9541, 7752, 7753, 7754, 7755, 7765, 7733, 7756, 7757, 7758, 7759, 7766, 9539, 9542, 9540, 9543, 7734, 7735, 10543, 7736, 7737, 7738, 10506, 7760, 7769, 7771, 7773, 7774, 1280, 1424, 7726, 1219, 1230, 1245, 1256, 1265, 1281, 1216, 1220, 1247, 1257, 1266, 1427, 1198, 1213, 1217, 1221, 1232, 1248, 1258, 1267, 1273, 1304, 1214, 1218, 1222, 1249, 1259, 1268, 1432, 1199, 1215, 1433, 1223, 1234, 1250, 1260, 1269, 1274, 1200, 1299, 1292, 1224, 1251, 1261, 1270, 1201, 1225, 1236, 1252, 1262, 1271, 1300, 1226, 1253, 1202, 1435, 1434, 1227, 1238, 1254, 1263, 1272, 1276, 1203, 1204, 1519, 1305, 1229, 1243, 1297, 1264, 1306, 1160, 1172, 1177, 1161, 1298, 1162, 1173, 1178, 1163, 1155, 1164, 1174, 1179, 1165, 1156, 1166, 1175, 1180, 1167, 1157, 1168, 1176, 1181, 1169, 1279, 1170, 1182, 1171, 1183, 7767, 1158, 8230, 8229, 1159, 7776, 7777, 7778, 1205, 1207, 1209,
    48, 18510, 7772, 1525, 1246, 1228, 1231, 1233, 1235, 1237, 1244, 1154, 1277, 1431, 1278, 1186, 1526, 1241, 7721, 1558, 9823, 1559, 9833, 1560, 9712, 8733, 8745, 8760, 1561, 9855, 9728, 1562, 9919, 1563, 9873, 1564, 9891, 1565, 9906, 17923, 18132, 1557, 9807, 7502, 9847, 9856, 9920, 9874, 9892, 9907, 18133, 7559, 9875, 8746, 8761, 7560, 7506, 7503, 7504, 7505, 17931, 1567, 9824, 1568, 9834, 1569, 9843, 9724, 8734, 9718, 8747, 8762, 1570, 9857, 9729, 1571, 9921, 9719, 1572, 9876, 1573, 9893, 1574, 9908, 17924, 18134, 1566, 9808, 9877, 9848, 9858, 9922, 9878, 9894, 9909, 18135, 1575, 8748, 8763, 7507, 7511, 7508, 7509, 7510, 17932, 9809, 1577, 9825, 1578, 9835, 1579, 9714, 8735, 8749, 8764, 1580, 9859, 9730, 1581, 9923, 1582, 9879, 1583, 9895, 1584, 9910, 17925, 18136, 1576, 9810, 9849, 9860, 9924, 9880, 9896, 9911, 18137, 7512, 8750, 8765, 7513, 7517, 7514, 7515, 7516, 17933, 1586, 9826, 1587, 9836, 1588, 9716, 8736, 9720, 8751, 8766, 1589, 9861, 9731, 9717, 9925, 9721, 1590, 9881,
    1591, 9897 , 1592, 9912, 18138, 1585, 9811, 9882, 9850, 9862, 9926, 9883, 9898, 9913, 18139, 1594, 9827, 1595, 9837, 1596, 8737, 9722, 8752, 8767, 1597, 9863, 9732, 1598, 7518, 9723, 1599, 9884, 7519, 1600, 9899, 1601, 9914, 17926, 18140, 1593, 9812, 1603, 9828, 1604, 9838, 1605, 8738, 8753, 8768, 1606, 9864, 9733, 9927, 1607, 9885, 1608, 9900, 1609, 9915, 18141, 1602, 9813, 1611, 9829, 1612, 9839, 1613, 8739, 8754, 8769, 1614, 9865, 9734, 1615, 1616, 9886, 7520, 1617, 9901, 1618, 9916, 17927, 1610, 9814, 9830, 9840, 9842, 9851, 9866, 9928, 9887, 9902, 9917, 18142, 9815, 1619, 9816, 1620, 9817, 1621, 9818, 1622, 9819, 1527, 8729, 8740, 8755, 1528, 9725, 1529, 1530, 1531, 1532, 9867, 9841, 9844, 9852, 9868, 9888, 9903, 1534, 1535, 1536, 8730, 8741, 8756, 1537, 9726, 7492, 1538, 1539, 1540, 17928, 1533, 7493, 1541, 1542, 10117, 10118, 9845, 7495, 1543, 8728, 8731, 8742, 8757, 1544, 7496, 1545, 1546, 1547, 17929, 7494, 9869, 10115, 10116, 1549, 9820, 9821, 1550, 9831, 1551, 8732,
    8743, 8758, 1552, 9853, 9727, 1553, 1554, 9870, 1555, 9889, 1556, 9904, 17922, 18130, 1548, 9805, 9871, 9822, 9832, 9846, 9854, 9918, 9872, 9890, 9905, 18131, 9806, 8744, 8759, 7497, 7501, 7498, 7499, 7500, 17930, 1750, 9941, 1751, 9951, 1752, 9759, 8775, 1753, 9972, 7527, 9775, 1754, 1755, 9992, 1756, 10010, 1757, 10025, 1749, 10039, 9973, 9993, 10011, 10026, 9994, 8776, 7528, 7529, 7530, 7531, 1759, 9942, 1760, 9952, 1761, 9959, 9771, 9765, 8777, 1762, 9974, 9776, 1763, 9766, 1764, 9995, 1765, 10012, 1766, 10027, 1758, 10040, 9996, 9965, 9975, 9997, 10013, 10028, 8778, 7532, 1767, 7533, 7534, 7535, 10041, 1769, 9943, 1770, 9953, 1771, 9761, 8779, 1772, 9976, 9777, 1773, 1774, 9998, 1775, 10014, 1776, 10029, 1768, 10042, 9966, 9977, 9999, 10015, 10030, 8780, 7536, 7537, 7538, 7539, 1778, 9944, 1779, 9954, 1780, 9763, 9767, 8781, 1781, 9978, 9778, 9764, 9768, 1782, 10000, 1783, 10016, 1784, 10031, 1777, 9929, 10001, 9967, 9979, 10002, 10017, 10032, 1786, 9945, 1787, 9955, 1788,
    9769, 8782, 1789, 9980) )
    
    union 
    select * from table(sys.odcinumberlist( 9779, 1790, 9770, 1791, 1792, 10018, 1793, 10033, 1785, 9930, 1795, 9946, 1796, 9956, 1797, 8783, 1798, 9981, 9780, 1799, 10004, 1800, 10019, 1801, 10034, 1794, 9931, 1803, 9947, 1804, 9957, 1805, 8784, 1806, 9781, 1807, 1808, 10005, 1809, 10020, 1810, 10035, 1802, 9932, 9948, 9958, 9968, 9983, 10006, 10021, 10036, 9933, 1811, 9934, 1812, 9935, 1813, 9936, 1814, 9937, 1719, 8770, 1720, 9772, 1721, 1722, 1723, 1724, 9986, 9961, 9969, 9987, 10007, 10022, 1726, 1727, 1728, 8771, 1729, 9773, 1730, 1731, 1732, 1725, 9960, 1733, 9985, 1734, 10119, 10120, 9962, 7522, 1735, 8772, 1736, 1737, 1738, 1739, 7521, 9988, 10113, 10114, 1741, 9938, 9939, 1742, 9949, 1743, 8773, 1744, 9970, 9774, 1745, 1746, 9989, 1747, 10008, 1748, 10023, 1740, 10037, 9990, 9940, 9950, 9963, 9971, 9991, 10009, 10024, 10038, 8774, 7523, 7524, 7525, 7526, 1, 2, 4, 5, 7, 8, 7903, 17508, 3, 6, 9, 7283, 7281, 17753, 771, 300, 7177, 586, 811, 7229, 7227, 75, 7209, 7207, 1464, 1465, 9689, 1466,
    1467, 9705, 9690, 1468, 1469, 1470, 1462, 1463, 7476, 7477, 7478, 7479, 1473, 1474, 9701, 9695, 1475, 9706, 9696, 1476, 1477, 1478, 1471, 1472, 7480, 7481, 7482, 7483, 1481, 1482, 9691, 1483, 9707, 9692, 1484, 1485, 1486, 1479, 1480, 7484, 7485, 7486, 7487, 1489, 1490, 9693, 9697, 1491, 9708, 9694, 9698, 1492, 1493, 1494, 1487, 1488, 1497, 1498, 9699, 1499, 9709, 9700, 1500, 1501, 1502, 1495, 1496, 1505, 1506, 1507, 9710, 1508, 1509, 1510, 1503, 1504, 1513, 1514, 1515, 9711, 1516, 1517, 1518, 1511, 1512, 7488, 7489, 7490, 7491, 7465, 7466, 9702, 7467, 7468, 3692, 7469, 1441, 1442, 1443, 9703, 1444, 1445, 1446, 1439, 1440, 1447, 1448, 10123, 10124, 1449, 1450, 1451, 1452, 1453, 7470, 7471, 1456, 1457, 1458, 9704, 1459, 1460, 1461, 1454, 1455, 7472, 7473, 7474, 7475, 770, 766, 7963, 10536, 4352, 4354, 4356, 4358, 4360, 4362, 4364, 4366, 4368, 4370, 4372, 4374, 4376, 4378, 4380, 4382, 4384, 4386, 4388, 4390, 4392, 4394, 4396, 4398, 4400, 4402, 4404, 4406, 4408, 4410, 4412, 4414,
    4416, 4418, 4420, 4422, 4424, 4426, 4428, 4430, 4432, 4434, 4436, 4438, 4440, 4442, 4444, 4446, 4448, 4450, 4452, 4454, 4456, 4458, 4460, 4462, 4464, 4466, 4468, 4470, 4472, 4474, 4476, 4478, 4480, 4482, 4484, 4486, 4488, 4490, 4492, 4494, 4496, 4498, 4500, 4502, 4504, 4506, 4508, 4510, 4512, 4514, 4516, 4518, 4520, 4522, 4526, 4528, 4595, 504, 3852, 73, 4536, 4538, 4540, 4542, 4544, 4546, 4548, 4550, 4552, 4554, 4556, 4558, 4560, 8651, 8652, 4563, 4565, 4567, 4569, 4571, 4573, 4575, 4577, 4579, 4581, 4583, 4585, 4587, 4589, 220, 221, 64, 18038, 758, 3565, 10456, 99, 212, 213, 225, 224, 761, 762, 92, 759, 18032, 94, 18599, 769, 18551, 102, 10442, 96, 43, 491, 440, 426, 470, 433, 477, 463, 456, 448, 18565, 172, 164, 492, 441, 471, 478, 464, 457, 449, 162, 170, 18566, 18567, 493, 442, 472, 479, 465, 458, 450, 165, 173, 146, 154, 169, 177, 7929, 7928, 18547, 208, 209, 18561, 4621, 4628, 4639, 4646, 4716, 4723, 4735, 4742, 4783, 4790, 4801, 4808, 4854, 4861, 4872, 4879, 4916, 4923,
    4936, 4943, 4956, 4963, 5006, 5013, 5024, 5031, 5042, 5049, 5104, 5111, 5124, 5131, 5162, 5169, 5182, 5189, 5242, 5249, 8653, 8654, 5296, 5303, 5314, 5321, 5334, 5341, 5378, 5385, 5398, 5405, 5418, 5425, 5620, 5627, 5640, 5647, 7785, 103, 10443, 1017, 7876, 1018, 1033, 1042, 8638, 1019, 1043, 7877, 7883, 1289, 1020, 1035, 1044, 8639, 1021, 7875, 7878, 7884, 1290, 1022, 1037, 1046, 1023, 9061, 9062, 9063, 7879, 7885, 1429, 1024, 1039, 1047, 1025, 7880, 8025, 1026, 1040, 1048, 7881, 1028, 1049, 1520, 1030, 1050, 7882, 1032, 1051, 1066, 7873, 1015, 1016, 8227, 8226, 5828, 5830, 5832, 5834, 5836, 5838, 5840, 5842, 5844, 5846, 5848, 5850, 5852, 5854, 5856, 5858, 5860, 5862, 5864, 18576, 21, 9086, 10225, 5877, 5879, 5883, 5885, 5889, 5891, 5895, 5897, 5901, 5903, 5907, 5909, 5913, 5915, 5919, 5921, 5925, 5927, 5931, 5933, 5943, 5945, 5955, 5957, 5961, 5963, 5967, 5969, 5973, 5975, 5979, 5981, 5991, 5993, 5997, 5999, 6003, 6005, 6015, 6017, 6021, 6023, 6027, 6029, 6073, 6075, 6079, 6081
    , 6085, 6087 , 6091, 6093, 6109, 6111, 6115, 6117, 6127, 6129, 6133, 6135, 6139, 6141, 6145, 6147, 6157, 6159, 6163, 6165, 6169, 6171, 6181, 6183, 6187, 6189, 6193, 6195, 6199, 6201, 6205, 6207, 6211, 6213, 6217, 6219, 6229, 6231, 6235, 6237, 6247, 6249, 6253, 6255, 6265, 6267, 6271, 6273, 6283, 6285, 6289, 6291, 6295, 6297, 6301, 6303, 6307, 6309, 6313, 6315, 6319, 6321, 6331, 6333, 6337, 6339, 6343, 6345, 6349, 6351, 6355, 6357, 6361, 6363, 6367, 6369, 6373, 6375, 6379, 6381, 6391, 6393, 6397, 6399, 6403, 6405, 6409, 6411, 6415, 6417, 6421, 6423, 6427, 6429, 6433, 6435, 6475, 6477, 6481, 6483, 6499, 6501, 6505, 6507, 6511, 6513, 6517, 6519, 6523, 6525, 6529, 6531, 6535, 6537, 6553, 6555, 6559, 6561, 6565, 6567, 6571, 6573, 755, 40, 41, 7720, 192, 193, 194, 195, 217, 216, 70, 19, 20, 7923, 10226, 9085, 6590, 6593, 6597, 6600, 6610, 6613, 6617, 6620, 6624, 6627, 6631, 6634, 6644, 6647, 6651, 6654, 6668, 6671, 6675, 6678, 6682, 6685, 6689, 6692, 757, 3564, 91, 10427, 302, 7920, 93
    , 7967, 18481 ))

using union...after 1000 parameters are exceeded
But I prefer temp table approach as others said initially...

Ravi Kumar

14. Re: ORA-01795: maximum number of expressions in a list is 1000 error

584412 Sep 11, 2009 10:25 AM (in response to ravikumar.sv)

True, but if you're going to do that, then you are just as well to use multiple IN operators split down into groups of 1000 or less. (I would imagine also that you would use union all rather than union too ;) )

ORA-01795: maximum number of expressions in a list is 1000 error