Avatar of cookiejar
cookiejar
Flag for United States of America asked on

Maximum string length for IN statement in ORACLE 11 g where clause

Is there a limit of the string length for the IN statement
For example this will be sent to the where clause in statement
IN ('1020202002','2020200202','02002020')
Crystal ReportsOracle Database

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cookiejar

ASKER
So if I have 1000 entries and each entry is 36 characters long, I will exceed the string length correct?  
The string is being sent to a stored procedure in the format '1001010','2020305','0202020','440404004'.  So if the maximum length is 4000, I can only have 111 entries?
SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

no.

Everything between single quotes is a single string. That is limited to 4000 characters EACH.

You can have 1000 entries each 4000 characters long.
Sean Stuber

>>>  I can only have 111 entries?

no

you can 1000 elements regardless of their size

but each element can only be 4000 characters long


('this is a string', 'this is a different string')

the entire IN clause is not a string,  only each element within it





I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
cookiejar

ASKER
So that I can cleary understand , in the stored procedure when I pass the  comma dimiliter values (1000 values 36 character long  to a defined variable  p_orglist  IN VARCHAR2, I will not encounter an error?
Mike McCracken

Where is the list?

Is this a Crystal selection formula or the where clause in a view?

mlmcc
cookiejar

ASKER
The list is being built in a formula from a multiple select value in Crystal.  Then the list is being passed through a parameter to an ORACLE stored procedure.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>> I will not encounter an error?

you will.  Passing in a single string is limited to 4000 characters.  That is different than an in-list to a stored procedure.
Sean Stuber

>>>  p_orglist  IN VARCHAR2


if a single parameter is your "LIST"  then it's not a list,  it's one string

if you need to turn you string into a list, then use something like str2tbl with the TABLE function as shown above


where yourcol in (select column_value from table(str2tbl(p_orglist)))


you can find my version of str2tbl definition here on EE or other versions, including Tom Kyte's with a simple google search.
schwertner

The maximum number of the entries in IN clause is 32000.

If you cross this limit you will run into the error
ORA-00600: internal error code, arguments: [kghsskins1], [], [], [], [], [], [], []
which is often disregarded by the DBAs which think this is a bug.

If you have more then 32000 entries use UNION.
Other workarounds are also possible, but need special actions.

I also think that the maximum length is not 4000 characters.
It is 4000 bytes - by some encodings 1 character ocupies 2 and more bytes.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sean Stuber

>>>> The maximum number of the entries in IN clause is 32000.

This is not correct.
The maximum entries in an IN list is 1000 as stated previously


http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/conditions013.htm#i1050801

workaround using OR as noted above, union would require multiple queries


>>> I also think that the maximum length is not 4000 characters.

yes, slightwv and I were both sloppy in saying "characters" vs "bytes"  for single byte characters, it's the same
but as schwertner pointed out, multi-byte characters have a lower limit
slightwv (䄆 Netminder)

>>The maximum number of the entries in IN clause is 32000.

Excerpt from 11gR2 docs: "You can specify up to 1000 expressions in expression_list."

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/conditions013.htm#SQLRF52169
schwertner

News from the Battle Field:

This week I have urgent Case send us from Tivoli  with 32,000 values with  Customer Oracle Server:

ORA-00600: internal error code, arguments: [kghsskins1], [], [], [], [], [], [], []

Find the meaning of  ORA-00600  kghsskins1 in Google:

Note 603689.1 (Internal) -> Ora-00600[Kghsskins1] for query with long in-list more than 32729 values

But also this has workaround:

Rewrite the query so that the in-list is shorter than 32729 values
or try workaround by try

alter session set "_optimizer_join_elimination_enabled"=false;
alter system flush shared_pool;
then run problem query within the same session.

or set "_optimizer_join_elimination_enabled"=false within init.ora or spfile globally then restart database

I recommended to use UNION and divide the IN list in smaller chunks, because we are not allowed easyly to restart production servers.

Please pay attention to the value 32729 !



 


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
schwertner

slightwv (䄆 Netminder)

>>News from the Battle Field:

The news is:  Battle lost...

Did you by change go read that doc and associated bug report?

It references a select like:

SELECT DISTINCT * from some_table
WHERE some_column NOT IN (  SELECT some_column from some_other_table  WHERE from_value=00501 OR from_value=00544 OR from_value=01001.....OR from_value=10466 OR from_value=10467 OR ....
)

NOT a hard-coded IN-LIST.


See the code below:
ORA-01795: maximum number of expressions in a list is 1000

SQL> 
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL> 
SQL> declare
  2  	     tmpStr varchar2(5000);
  3  	     retval number;
  4  
  5  begin
  6  	     for i in 1..1001 loop
  7  		     tmpStr := tmpStr || '''a'',';
  8  	     end loop;
  9  	     execute immediate 'select count(*) from dual where dummy in (' || rtrim(tmpStr,',') || ')' into retVal;
 10  
 11  	     dbms_output.put_line('Got: ' || retVal);
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at line 9


SQL> 
SQL> 
SQL> spool off

Open in new window

Sean Stuber

you can have many values in an IN list of derived values (such as a query returning many thousands of results)

for example,  this is ok even though the IN list has 50000 elements (which is greater than 32729)

select * from all_objects where object_id in(select level from dual connect by level <= 50000)


however you can't specify more than 1000 items in your list.

this is also easy to test....

this will fail with    ORA-01795: maximum number of expressions in a list is 1000

 
select * from all_objects where object_id in
(1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
46,
47,
48,
49,
50,
51,
52,
53,
54,
55,
56,
57,
58,
59,
60,
61,
62,
63,
64,
65,
66,
67,
68,
69,
70,
71,
72,
73,
74,
75,
76,
77,
78,
79,
80,
81,
82,
83,
84,
85,
86,
87,
88,
89,
90,
91,
92,
93,
94,
95,
96,
97,
98,
99,
100,
101,
102,
103,
104,
105,
106,
107,
108,
109,
110,
111,
112,
113,
114,
115,
116,
117,
118,
119,
120,
121,
122,
123,
124,
125,
126,
127,
128,
129,
130,
131,
132,
133,
134,
135,
136,
137,
138,
139,
140,
141,
142,
143,
144,
145,
146,
147,
148,
149,
150,
151,
152,
153,
154,
155,
156,
157,
158,
159,
160,
161,
162,
163,
164,
165,
166,
167,
168,
169,
170,
171,
172,
173,
174,
175,
176,
177,
178,
179,
180,
181,
182,
183,
184,
185,
186,
187,
188,
189,
190,
191,
192,
193,
194,
195,
196,
197,
198,
199,
200,
201,
202,
203,
204,
205,
206,
207,
208,
209,
210,
211,
212,
213,
214,
215,
216,
217,
218,
219,
220,
221,
222,
223,
224,
225,
226,
227,
228,
229,
230,
231,
232,
233,
234,
235,
236,
237,
238,
239,
240,
241,
242,
243,
244,
245,
246,
247,
248,
249,
250,
251,
252,
253,
254,
255,
256,
257,
258,
259,
260,
261,
262,
263,
264,
265,
266,
267,
268,
269,
270,
271,
272,
273,
274,
275,
276,
277,
278,
279,
280,
281,
282,
283,
284,
285,
286,
287,
288,
289,
290,
291,
292,
293,
294,
295,
296,
297,
298,
299,
300,
301,
302,
303,
304,
305,
306,
307,
308,
309,
310,
311,
312,
313,
314,
315,
316,
317,
318,
319,
320,
321,
322,
323,
324,
325,
326,
327,
328,
329,
330,
331,
332,
333,
334,
335,
336,
337,
338,
339,
340,
341,
342,
343,
344,
345,
346,
347,
348,
349,
350,
351,
352,
353,
354,
355,
356,
357,
358,
359,
360,
361,
362,
363,
364,
365,
366,
367,
368,
369,
370,
371,
372,
373,
374,
375,
376,
377,
378,
379,
380,
381,
382,
383,
384,
385,
386,
387,
388,
389,
390,
391,
392,
393,
394,
395,
396,
397,
398,
399,
400,
401,
402,
403,
404,
405,
406,
407,
408,
409,
410,
411,
412,
413,
414,
415,
416,
417,
418,
419,
420,
421,
422,
423,
424,
425,
426,
427,
428,
429,
430,
431,
432,
433,
434,
435,
436,
437,
438,
439,
440,
441,
442,
443,
444,
445,
446,
447,
448,
449,
450,
451,
452,
453,
454,
455,
456,
457,
458,
459,
460,
461,
462,
463,
464,
465,
466,
467,
468,
469,
470,
471,
472,
473,
474,
475,
476,
477,
478,
479,
480,
481,
482,
483,
484,
485,
486,
487,
488,
489,
490,
491,
492,
493,
494,
495,
496,
497,
498,
499,
500,
501,
502,
503,
504,
505,
506,
507,
508,
509,
510,
511,
512,
513,
514,
515,
516,
517,
518,
519,
520,
521,
522,
523,
524,
525,
526,
527,
528,
529,
530,
531,
532,
533,
534,
535,
536,
537,
538,
539,
540,
541,
542,
543,
544,
545,
546,
547,
548,
549,
550,
551,
552,
553,
554,
555,
556,
557,
558,
559,
560,
561,
562,
563,
564,
565,
566,
567,
568,
569,
570,
571,
572,
573,
574,
575,
576,
577,
578,
579,
580,
581,
582,
583,
584,
585,
586,
587,
588,
589,
590,
591,
592,
593,
594,
595,
596,
597,
598,
599,
600,
601,
602,
603,
604,
605,
606,
607,
608,
609,
610,
611,
612,
613,
614,
615,
616,
617,
618,
619,
620,
621,
622,
623,
624,
625,
626,
627,
628,
629,
630,
631,
632,
633,
634,
635,
636,
637,
638,
639,
640,
641,
642,
643,
644,
645,
646,
647,
648,
649,
650,
651,
652,
653,
654,
655,
656,
657,
658,
659,
660,
661,
662,
663,
664,
665,
666,
667,
668,
669,
670,
671,
672,
673,
674,
675,
676,
677,
678,
679,
680,
681,
682,
683,
684,
685,
686,
687,
688,
689,
690,
691,
692,
693,
694,
695,
696,
697,
698,
699,
700,
701,
702,
703,
704,
705,
706,
707,
708,
709,
710,
711,
712,
713,
714,
715,
716,
717,
718,
719,
720,
721,
722,
723,
724,
725,
726,
727,
728,
729,
730,
731,
732,
733,
734,
735,
736,
737,
738,
739,
740,
741,
742,
743,
744,
745,
746,
747,
748,
749,
750,
751,
752,
753,
754,
755,
756,
757,
758,
759,
760,
761,
762,
763,
764,
765,
766,
767,
768,
769,
770,
771,
772,
773,
774,
775,
776,
777,
778,
779,
780,
781,
782,
783,
784,
785,
786,
787,
788,
789,
790,
791,
792,
793,
794,
795,
796,
797,
798,
799,
800,
801,
802,
803,
804,
805,
806,
807,
808,
809,
810,
811,
812,
813,
814,
815,
816,
817,
818,
819,
820,
821,
822,
823,
824,
825,
826,
827,
828,
829,
830,
831,
832,
833,
834,
835,
836,
837,
838,
839,
840,
841,
842,
843,
844,
845,
846,
847,
848,
849,
850,
851,
852,
853,
854,
855,
856,
857,
858,
859,
860,
861,
862,
863,
864,
865,
866,
867,
868,
869,
870,
871,
872,
873,
874,
875,
876,
877,
878,
879,
880,
881,
882,
883,
884,
885,
886,
887,
888,
889,
890,
891,
892,
893,
894,
895,
896,
897,
898,
899,
900,
901,
902,
903,
904,
905,
906,
907,
908,
909,
910,
911,
912,
913,
914,
915,
916,
917,
918,
919,
920,
921,
922,
923,
924,
925,
926,
927,
928,
929,
930,
931,
932,
933,
934,
935,
936,
937,
938,
939,
940,
941,
942,
943,
944,
945,
946,
947,
948,
949,
950,
951,
952,
953,
954,
955,
956,
957,
958,
959,
960,
961,
962,
963,
964,
965,
966,
967,
968,
969,
970,
971,
972,
973,
974,
975,
976,
977,
978,
979,
980,
981,
982,
983,
984,
985,
986,
987,
988,
989,
990,
991,
992,
993,
994,
995,
996,
997,
998,
999,
1000,
1001)

Open in new window



I'm sorry your itpub link above times out.

If you have a test case that demonstrates a counter to either of my examples, please post it.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
schwertner

@ slightwv:

Saying "battle filed" I ment the Office I am working.
I am not so stupid or crazy to waste time ... in strange conversation.
So if I was not clear enough, please appologize.

The error I display you arises very often in the Warehouse DB of our big customers.
So I faced it many times and even this week I sent warning to the appropriate application team that this happens.

It definitelly to make applications with statements on the limit of Oracle editions is an nonconstructive idea.
Obviosly the 32,000 entries limition exists - there are more then enough postings in the net.
The worst thing is that they are masked under ORA-00600. The support engineers often say - this is an Oracle bug and they disregard it.
But the application fails ....
Sean Stuber

nobody is denying the bug exists.

it simply doesn't apply here though,  the problem in this question is the construction of a fixed list of values  (a,b,c)  not a derived list (select x from .....)

the fixed list limit is 1000


if however, the asker chooses to implement one of the variations above where an IN-sub-select is used then your bug might apply (but hopefully not)
cookiejar

ASKER
You wrote:
i

f you need to pass a very large list of values  you can ...

break your IN list into pieces and use  OR

(
yourcol in ('a','b','c'......)
or
yourcol in ('x','y','z'.....)
or
yourcol  in ('1','2','3'.....)
)

you could write your values to a temp table (or real table, but temp makes more sense for this type of operation)
where yourcol in (select value from yourtemptable)
you could use the TABLE function to return a collection that generates your list of values


The current process flow is as follows:
A JAVA interface allows the user to select multiple titles and stores the title_id in array
The titles ids are passed to multi-select parameter in Crystal Reports
Crystals uses a function to create a string that looks like this for example, '103','104','105','106' pass this string to the stored procedure.

What I would like is to use your process to create a temp table and use it in a subquery  I would like the temp table to be created in the jAVA interface.  Get the collection of ids and write them to a table.  You mentioned using  the TABLE function to create a list of values, could you give me an example, I am indeed a novist?  In the temp table I would need to include the creation datetime of the record, a random generated login_id (which is created in the Java interface), and the title_ids.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

http:#36337311 answers the direct question of hard limits
http:#36337382 suggests several alternates  (split "or" lists, temp table, table function) for generated lists
http:#36392968 follows up with specific function (str2tbl)
Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.