Results 1 to 6 of 6

Thread: IN operator

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: IN operator

    Does anyone know if there is a limit on the amounts of items in the IN operator in an sql statement

    eg.
    where a in ('a','ad',......................)

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What I've found on the Oracle Metalink:
    </8.1.7 Docu>
    An expression list can contain up to 1000 expressions.
    </8.1.7 Docu>

    In addition to this, there might be other limitations to be respected, e.g. SQL*PLUS has documented limits like "command-line length" (=2500 characters) and "number of lines per SQL command" (=500 (assuming 80 characters per line)).

    Hope this helps,

    Christoph Pfrommer
    Oracle Consulting Germany
    I also hope this helps

  3. #3
    Join Date
    Feb 2004
    Posts
    6

    Smile

    Thank you. :-)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you are adding so many values to your IN that the limit concerns you, maybe you should be considering a different approach anyway? Like perhaps storing the values in a global temporary table which you then join to the query.

  5. #5
    Join Date
    Feb 2004
    Posts
    6
    I am calling a pl/sql function in powerbuilder to get this list and passing it back to a powerbuilder event. Im not sure the powerbuilder session will be able to see the values created in the table by the pl/sql function as it will be a different session.
    There will probably be from 1 - 80 values in the IN. Is 80 an acceptable number of values for an IN?

    Thanks for your help.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well it isn't as high as I feared it might be. Hopefully you will be using bind variables? Even with bind variables, there will still be up to 80 variants of the SQL statement parsed, but that's better than one per execution if the query is used a lot.

Posting Permissions

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