Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Tokyo
    Posts
    10

    Question Unanswered: ORA-01795 probelm with 'in clause'

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Try putting your list values into either a table (if its always the same ) or a global temp table (if its different every time) and then joining to it. It will probably be more efficient aswell.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •