Home » SQL & PL/SQL » SQL & PL/SQL » Maximum allowed in IN Clause in Select Statement (Oracle 9i)
Maximum allowed in IN Clause in Select Statement [message #281859] Mon, 19 November 2007 11:16 Go to next message
bhuvan_oracle
Messages: 6
Registered: November 2007
Location: Chennai
Junior Member

Hi,

Please tell me what is the maximum allowed values in IN clause of SELECT statement where Clause.

Thank you
Bhuvan
Re: Maximum allowed in IN Clause in Select Statement [message #281861 is a reply to message #281859] Mon, 19 November 2007 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2000 (iirc)
Just try it.

Regards
Michel
Re: Maximum allowed in IN Clause in Select Statement [message #281863 is a reply to message #281861] Mon, 19 November 2007 11:55 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
1000 (iirc).
Re: Maximum allowed in IN Clause in Select Statement [message #281865 is a reply to message #281863] Mon, 19 November 2007 12:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    s varchar(32560);
  3    v pls_integer;
  4  begin
  5    s := 'select col from t where col in (';
  6    for i in 1..1001 loop
  7       s := s || i || ',';
  8    end loop;
  9    s := rtrim(s,',');
 10    s := s || ')';
 11    execute immediate s into v;
 12  end;
 13  /
declare
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at line 11

LittleFoot wins! ./fa/1940/0/

Regards
Michel
Previous Topic: Unique Cosntraint Without Unique Index
Next Topic: ORA-00937: not a single-group group function.
Goto Forum:
  


Current Time: Sun Dec 04 10:46:05 CST 2016

Total time taken to generate the page: 0.07167 seconds