12 Replies Latest reply: Mar 13, 2012 2:56 AM by fatkut RSS

    ORA-01795, limit on in clause

    405508
      hi guys,

      I have the following query,

      Select
      shogen_no,
      shogen_desc,
      disp_turn
      From
      shogen
      where
      shogen_no in ('one', 'two', 'three', ..........'more than thousand')

      When I try to run it, the ORACLE (8.1.6) gives me following error:
      ORA-01795 maximum number of expressions in a list is 1000

      Can anyone explain the situation?
      1) Cant we put more than 1000 values in 'in clause'.
      2) What is the other way of doing it.

      I am running this query in a PL/SQL procedure. First of all I gather the values and put them in a varchar2 (shogenList). Then I put that variable in the 'in clause' i.e. where shogen_no in ('||shogenList||')..

      Thanx for any help
      Omer
        • 1. Re: ORA-01795, limit on in clause
          JustinCave
          You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e.
          SELECT shogen_no,
                 shogen_desc,
                 disp_turn
            FROM shogen
          WHERE shogen_no IN ( SELECT shogen_no
                                  FROM <<some other table>> )
          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC
          • 2. Re: ORA-01795, limit on in clause
            137581
            You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.
            • 3. Re: ORA-01795, limit on in clause
              415189
              Just add some ORs . . .

              where
              shogen_no in ('one', 'two', 'three') or
              shogen_no in ('four', 'five', 'six') or ...
              • 4. Re: ORA-01795, limit on in clause
                405508
                adding 'OR' makes the query extremely slow..... :(
                • 5. Re: ORA-01795, limit on in clause
                  JustinCave
                  Presumably, if you can gather the literals in a PL/SQL stored procedure, you can gather them in a single SQL statement and just embed that in your IN clause though. Not only will it be faster to execute, you don't have to worry about doing all that string manipulation.

                  Justin
                  Distributed Database Consulting, Inc.
                  http://www.ddbcinc.com/askDDBC
                  • 6. Re: ORA-01795, limit on in clause
                    116618
                    I just responded to this same issue. Don't use the IN clause if at all possible when it is driven by a subquery. First you can return too many rows for IN clause itself (which is what you are seeing). Second, the performance of IN in this context is poor. Oracle doesn't handle the relationship well.

                    Select
                    shogen_no,
                    shogen_desc,
                    disp_turn
                    From
                    shogen
                    where
                    shogen_no in ('one', 'two', 'three', ..........'more than thousand')

                    Change to

                    select ...
                    from shogen
                    where
                    exists (select 1 from table used to build shogenList where shogenlist.value = shogen.value)

                    This example would be better if you had included the original query to build shogenlist. We have rewritten a ton of our queries using this syntax. Check out the explain when you are done.
                    • 7. Re: ORA-01795, limit on in clause
                      JustinCave
                      IN and EXISTS both have their uses. If shogen is a multi-million row table, IN will probably be faster. If shogen has a few hundred rows, EXISTS will probably be faster. It it is in the middle, it's a good idea to try both.

                      Additionally, EXISTS doesn't work particularly well when you have hard coded values.

                      Justin
                      Distributed Database Consulting, Inc.
                      http://www.ddbcinc.com/askDDBC
                      • 8. Re: ORA-01795, limit on in clause
                        Himanshu Kandpal
                        Hi,

                        this error means

                        ORA-01795 maximum number of expressions in a list is 1000
                        Cause: More than 254 columns or expressions were specified in a list.
                        Action: Remove some of the expressions from the list.

                        One way you can use a OR conditions like
                        shogen_no in (...)
                        or shogen_no in ().....


                        thanks
                        • 9. Re: ORA-01795, limit on in clause
                          BluShadow
                          Hi,

                          this error means

                          ORA-01795 maximum number of expressions in a list is
                          1000
                          Cause: More than 254 columns or expressions were
                          specified in a list.
                          Action: Remove some of the expressions from the list.


                          One way you can use a OR conditions like
                          shogen_no in (...)
                          or shogen_no in ().....


                          thanks
                          Firstly, well done for answering a thread that's 3.5 years old and secondly well done for answering it with an answer that's already been given and thirdly well done for answering it with an answer that does not offer the best solution to the problem as has already been explained.

                          Good to see you read the threads you are answering.
                          • 10. Re: ORA-01795, limit on in clause
                            416257
                            I stumbled upon this thread when I was searching for a solution to this issue. I was exploring if there are any alternatives to just replacing the string with a IN with a select. I hit on this solution, which was very interesting, it uses an array of table. Take a look:

                            http://www.orafaq.com/forum/t/41437/0/

                            PS: I know I am posting to an older thread, but only to keep the information in this thread in case it is useful to others. :)
                            • 11. Re: ORA-01795, limit on in clause
                              876218
                              Here are some failed solutions and a solution that works…

                              This problem is caused by the fact that in Oracle a comma-delimited list of expressions can contain no more than 1,000 expressions. Microsoft SQL Server Reporting Services (SSRS) lets you run a query that populates a check-box drop-down, and when the user hits “Select All” the values get passed as a single-quoted comma-separated list of values to the parameter used in the SQL of the report, triggering the Oracle error when it exceeds 1,000.

                              Changing SQL does not seem to be a solution. Defining a function that takes a string returning a TABLE TYPE or ARRAY, which then goes to a “in ( Select * from TABLE(CAST(” works for small sets but not when the literal string has more than 4,000 characters. Even literals in the form of Q’{…}’ (to hide the imbedded single quotes) can’t have more than 4,000 characters. The SQL-only method below converts the parameter to a table function fails when the literal string has more than 4,000 characters, which is typically the case when you have more than 1,000 items. For future reference here is that approach if you ever need it:

                              --execute one-time definitions outside of SSRS
                              CREATE OR REPLACE TYPE StringTable AS TABLE OF VARCHAR2( 250 );
                              /
                              CREATE OR REPLACE FUNCTION f_string_table ( p_list IN VARCHAR2 ) RETURN StringTable
                              IS
                              v_delimiter CHAR(3) := ''',''';
                              v_string LONG := SUBSTR( p_list || ',''', 2 );
                              v_pos pls_integer;
                              v_data StringTable := StringTable( );
                              BEGIN
                              LOOP
                              v_pos := instr( v_string, v_delimiter );
                              EXIT WHEN( NVL( v_pos, 0 ) = 0 );
                              v_data.extend;
                              v_data( v_data.count ) := trim( SUBSTR( v_string, 1, v_pos - 1 ) );
                              v_string := SUBSTR( v_string, v_pos + 3 );
                              END LOOP;
                              RETURN( v_data );
                              END f_string_table;
                              /
                              --test
                              SELECT * FROM TABLE( CAST( f_string_table( Q'{'aaaa','bbbb','c','xxxx'}' ) AS StringTable ) );

                              --in the SSRS SQL change this
                              --IN(:myparm)
                              --to this
                              --IN(select * from table(cast(f_string_table(Q'{:myparm}') as StringTable));

                              Converting the parm string to a multi-row Table Value Constructor does not work either, due to the same 4,000 character limitation, and due to the fact that Oracle, unlike SQL Server, does not allow multi-row Table Value Constructors in the form of:
                              SELECT * FROM (VALUES (101, 'Bikes'),(102, 'Accessories'),(103, 'Clothes')) AS Category(CategoryID, CategoryName);

                              So it appears you need to change both the user interface in SSRS and the Oracle SQL, by either, (1) adding check-boxes for when a user wants to select all the values in a domain, or (2) alter the query that populates the drop-downs such that it adds a “ SELECT ALL” choice to the values that the final query uses to control predicates.

                              Here is how to do (2): Add a new item at the head of the list with the literal value “ SELECT ALL” and make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting.

                              My assumption is that no user would actually want to take the time to manually select a subset with more than 1,000 items for a set with more than 1,000 items unless they wanted to select all items. The exception to this, which is not solved below, is the situation where a user wants to select all 1,000+ items except a few items.

                              In this solution the drop-down will show both “Select All” and “ SELECT ALL” but with “ SELECT ALL” checked by default. If the user ignores this and clicks on “Select All” (which actually causes all values to be passed to the query) no harm or error will be done unless the number of items exceeds 1,000, which will teach the user to put it back to “ SELECT ALL”. Users with a smaller domain of parameter values will not be impacted with either selection.

                              A secondary impact of this approach below may be that queries run a little faster with “ SELECT ALL”.

                              Find the parameters that exceed 1,000 items. For these parameters do the following:

                              Insert the following line above the ORDER BY clause that provides the drop-down values to the user:
                              UNION ALL SELECT ' SELECT ALL' FROM DUAL
                              The initial space in the first character of ' SELECT ALL' is for the purpose of causing it to sort to the top and to prevent it from matching a value found in the data.
                              (You may need to add a column with a -1 if your parameter returns an identifier and use -1 to signify the select all case.)

                              In the report’s query, wherever you see the parameter, such as:
                              AND M.MATERIALDESC IN (:MyParm)
                              Change it to:
                              AND (' SELECT ALL' IN( : MyParm) OR M.MATERIALDESC IN( : MyParm))
                              Note: keep the OR’d predicates in the order shown as Oracle processes OR left-to-right and eliminates checking the second predicate after the first TRUE. This helps performance.

                              In the SSRS parameter definition make “ SELECT ALL” the single default value for the parameter, instead of checking ‘Select All Values’ as the defined default setting.

                              Not pretty but it works.
                              • 12. Re: ORA-01795, limit on in clause
                                fatkut
                                Hi;
                                You can try doing this

                                Select
                                shogen_no,
                                shogen_desc,
                                disp_turn
                                From
                                shogen
                                where
                                shogen_no in ('one', 'two', 'three', ..........'one thousand')

                                UNION ALL

                                Select
                                shogen_no,
                                shogen_desc,
                                disp_turn
                                From
                                shogen
                                where
                                shogen_no in ('onethousandone', 'onethousandtwo', 'onethousandthree', ..........'two thousand')


                                Regards..