developers

    Forum Stats

  • 3,873,868 Users
  • 2,266,625 Discussions
  • 7,911,647 Comments

Discussions

ORA-01795, limit on in clause

2»

Comments

  • 876218
    876218 Member Posts: 1
    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.
  • fatkut
    fatkut Member Posts: 5
    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..
This discussion has been closed.
developers