How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used vi to add a comma at the end of each line. Then I added a query at the start of the data:

select * from m_number_detail

where mpo_reference in (

0002538404,

0002538505,

0001312900,

0001313408,

0006175302,

Etc

Etc

When I tried to run it, I saw the following message at the end of the SPOOL file:

Etc

Etc

17906  8815785700,

17907  8817723609,

17908  0072096508,

17909  0072096710,

17910  0925482304)

17911  /

0005567909,

*

ERROR at line 1003:

ORA-01795: maximum number of expressions in a list is 1000

 

SQL>