1 2 Previous Next 22 Replies Latest reply on Jun 17, 2010 11:33 AM by 656751 Go to original post
      • 15. Re: ORA-01795: maximum number of expressions in a list is 1000 error
        584412
        Seems that JS1 is finally right
        That's a bit harsh.
        • 16. Re: ORA-01795: maximum number of expressions in a list is 1000 error
          673860
          what is wrong in this then?

          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>) 
          • 17. Re: ORA-01795: maximum number of expressions in a list is 1000 error
            ravikumar.sv
            JS1 wrote:
            (I would imagine also that you would use union all rather than union too ;)
            I think if we use a list in Oracle's in clause the list will be unioned first and filter is applied.
            SQL> select col1,col2 from testing;
            
            COL1                 COL2
            -------------------- -------------------------------------------------------------------------------
            1                    1
            2                    2
            3                    3
            
            SQL> explain plan for select col1,col2 from testing where col1 in (1,1,1,2);
            
            Explained.
            
            SQL> select * from table(dbms_xplan.display);
            
            PLAN_TABLE_OUTPUT
            ----------------------------------------------------------------------------------------------------
            Plan hash value: 533408839
            
            -----------------------------------------------------------------------------
            | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------
            |   0 | SELECT STATEMENT  |         |     5 |    20 |    18   (0)| 00:00:01 |
            |*  1 |  TABLE ACCESS FULL| TESTING |     5 |    20 |    18   (0)| 00:00:01 |
            -----------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
            PLAN_TABLE_OUTPUT
            ----------------------------------------------------------------------------------------------------
            
               1 - filter(TO_NUMBER("COL1")=1 OR TO_NUMBER("COL1")=2)
            
            13 rows selected.
            so in above filter it is actually taking distinct values..so I thought why not we send the unionized result to it... :-)
            If it does union again even after I sending a unionized result then...I did will be wrong...

            Ravi Kumar
            • 18. Re: ORA-01795: maximum number of expressions in a list is 1000 error
              MichaelS
              That's a bit harsh.
              Sorry: Never meant to be of course ;)

              Funny thing though: The sys.odcinumberlist collection can indeed cope with 32767 elements in plsql:
              SQL>  var cur refcursor
              
              SQL>  DECLARE
               n sys.odcinumberlist;
               l LONG
                 := '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';
              BEGIN
               execute immediate 'begin :l := sys.odcinumberlist(' || l || '); end;' using out n;
               open :cur for select empno, ename from emp where empno in (select * from table(n)); 
              END;
              /
              PL/SQL procedure successfully completed.
              
              SQL>  print cur
              
                   EMPNO ENAME     
              ---------- ----------
                    7788 SCOTT     
                    7499 ALLEN     
                    7521 WARD      
                    7876 ADAMS     
              
              4 rows selected.
              • 19. Re: ORA-01795: maximum number of expressions in a list is 1000 error
                kendenny
                Something I found out recently that is rather surprising. If your have more than one value in each element the limit is higher. I don't know how high but where I work we have one query with an in list of 1500.
                where (column,0) in ((1,0),(2,0),(3,0),(4,0), ... ,(1500,0))
                So take your list of values and do a global replace of ',' with ',0),(' You'll then need to manually insert a '(' at the beginning and a ')' at the end and you're good to go.
                • 20. Re: ORA-01795: maximum number of expressions in a list is 1000 error
                  William Robertson
                  Interesting - it seems it is a parsing restriction. It fails with "ORA-00939: too many arguments for function" even if you use a non-existent type.
                  • 21. Re: ORA-01795: maximum number of expressions in a list is 1000 error
                    465815
                    Although this thread was illuminating, if I was you I would have stick to the gtt idea.
                    Amiel
                    • 22. Re: ORA-01795: maximum number of expressions in a list is 1000 error
                      656751
                      Is there any way to increase the maximum number of expressions ?
                      1 2 Previous Next