This content has been marked as final.
Show 22 replies
-
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 ....
using union...after 1000 parameters are exceededSELECT * 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 ))
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 ;) )