We've all engaged in code generation of one sort or another. For certain problems it's just the right, or perhaps the only tool. But then your content management vendor starts to throw this sort of thing at your database...
OPEN p_cursor FOR
'SELECT content_id, content_status
FROM content
WHERE content_type_id = 319
AND content_id IN (
51055 , 45531 , 42208 , 42911 , 46494 , 52898 , 44262 , 44312 , 47474 , 42792 ,
45956 , 45109 , 53432 , 14936 , 29040 , 28779 , 53015 , 48366 , 53739 , 48565 ,
47188 , 46573 , 43038 , 53534 , 51999 , 49731 , 52847 , 43883 , 41522 , 50804 ,
49975 , 45729 , 53260 , 47658 , 41325 , 49454 , 41374 , 45328 , 51612 , 54347 ,
50092 , 48147 , 42416 , 42570 , 49533 , 41948 , 51740 , 52973 , 42648 , 44867 ,
48289 , 45943 , 49556 , 54550 , 46801 , 43628 , 40569 , 41576 , 46752 , 44982 ,
42309 , 45146 , 47198 , 44993 , 47768 , 47060 , 46889 , 45651 , 47045 , 45830 ,
41248 , 54370 , 43741 , 44183 , 28451 , 45094 , 54332 , 47030 , 42060 , 41293 ,
48287 , 48012 , 47740 , 45688 , 43639 , 48484 , 47583 , 45304 , 51478 , 42633 ,
40558 , 43793 , 41587 , 49407 , 28803 , 43272 , 46464 , 45602 , 43866 , 44521 ,
41200 , 48044 , 46927 , 29186 , 45774 , 43722 , 45128 , 43398 , 47397 , 41670 ,
51888 , 47534 , 29237 , 42486 , 53811 , 44704 , 46618 , 48994 , 44848 , 44573 ,
52956 , 44487 , 42435 , 48164 , 43451 , 52031 , 51300 , 52595 , 53141 , 44032 ,
50904 , 41477 , 42161 , 44622 , 52695 , 43838 , 44562 , 45373 , 44882 , 47247 ,
42367 , 50921 , 46265 , 41933 , 48960 , 43143 , 43345 , 28412 , 48868 , 49005 ,
43533 , 46953 , 52420 , 44017 , 49817 , 41490 , 41395 , 43027 , 47703 , 29064 ,
41689 , 50564 , 44112 , 28957 , 29544 , 45223 , 43252 , 49699 , 47356 , 43565 ,
53604 , 45478 , 29422 , 53041 , 45697 , 44054 , 42469 , 44035 , 48750 , 46667 ,
50060 , 46698 , 48306 , 45849 , 45563 , 42452 , 45143 , 46110 , 47800 , 43076 ,
41280 , 45862 , 41657 , 42114 , 47523 , 51841 , 45988 , 48023 , 47307 , 43203 ,
53464 , 44202 , 47984 , 46218 , 45255 , 51412 , 52409 , 47442 , 45932 , 28269 ,
43823 , 46835 , 47785 , 50968 , 46050 , 41916 , 47219 , 51772 , 41980 , 43377 ,
43681 , 50007 , 43072 , 47150 , 45900 , 53376 , 54338 , 45360 , 49086 , 46520 ,
47626 , 29529 , 50321 , 51982 , 53660 , 51644 , 42028 , 41901 , 53269 , 47995 ,
43754 , 49163 , 46069 , 51213 , 53566 , 52304 , 49588 , 51134 , 45167 , 46447 ,
42108 , 45570 , 29439 , 46091 , 44160 , 52227 , 47324 , 42256 , 51262 , 28854 ,
43220 , 41363 , 45272 , 42097 , 46790 , 51102 , 50338 , 44149 , 49849 , 49375 ,
41814 , 44738 , 54211 , 52712 , 54364 , 50283 , 54442 , 46863 , 48687 , 45178 ,
28464 , 44084 , 47615 , 44504 , 50895 , 46231 , 45723 , 47753 , 47230 , 29600 ,
43430 , 28764 , 41619 , 44217 , 43649 , 47502 , 41509 , 47425 , 42873 , 51814 ,
41438 , 44925 , 15364 , 41539 , 50336 , 42777 , 42127 , 44967 , 46942 , 43578 ,
46644 , 48887 , 52748 , 46904 , 42290 , 44000 , 45446 , 45967 , 46539 , 44361 ,
52990 , 45341 , 42326 , 47952 , 49746 , 29512 , 45806 , 50252 , 48121 , 46981 ,
29162 , 41702 , 48700 , 50692 , 49039 , 44689 , 51181 , 42988 , 41737 , 41782 ,
54283 , 44796 , 42667 , 48793 , 42597 , 49195 , 46018 , 48070 , 42975 , 49264 ,
43675 , 29478 , 51000 , 43287 , 49052 , 51589 , 42506 , 44301 , 44649 , 44908 ,
48072 , 50857 , 46020 , 44067 , 42013 , 42091 , 43464 , 48578 , 54015 , 41412 ,
51361 , 52509 , 47124 , 51316 , 44143 , 29724 , 52714 , 44638 , 46142 , 41459 ,
42586 , 43919 , 48945 , 44346 , 49390 , 44822 , 46631 , 43107 , 43591 , 42144 ,
29755 , 43662 , 47540 , 43808 , 45390 , 42223 , 42371 , 42551 , 47918 , 47337 ,
For brevity's sake I'll skip the next 11,000 elements of the IN list.
7 comments:
That type of thing's sadly common. Is this Mediasurface, perchance?
I notice in 10.1.0.4 the error "ORA-01795: maximum number of expressions in a list is 1000" appears to kick in at 10,001 expressions.
Tragically I saw the same on an (vendor) application at our site...when ultimately it hit the 1000-in-list limit (v9) and we raised a bug, what did they do to fix it ? wait for it....
yep, some smarts to break the list up, ie,
where col in (.... up to 1000 )
or col in (...1001 .. 2000)
etc etc
so very sad...
How ignorant of them! When they could have converted the complete IN-list to an OR-list and avoided any limits... except the shared pool size and therefore the number of users they could support, of course :-)
I have actually seen this "smarter" approach in a VB application using DAO (or similar microsoftism) some years ago. The VB developer claimed that DAO generated the code...
I often see a more advanced approach that at least uses bind variables instead of literals:
...
where (id=:a1 or id=:a2 or...)
I wonder where they get those "article numbers" from. Maybe their stored in a database table somewhere...
can u please tell me whether this same error occurs n 10G also? Please reply to jesperos_niguy@yahoo.com
Thanks!
Yes it does. Apparently this makes Oracle weird.
Post a Comment