Re: IDs in IN clause

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 12 Jul 2006 12:00:52 +0200
Message-ID: <44b4c9cd$0$29125$9b4e6d93@newsread4.arcor-online.net>

sybrandb_at_yahoo.com schrieb:
> Robert Wehofer wrote:

>> Hello!
>>
>> Does anyone know, how many IDs are allowed in an Oracle IN clause (Oracle
>> version = 9i)?
>>
>> e.g: DELETE FROM Table WHERE ID IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,
>> ...)
>>
>> What is the best way for me to delete amounts of distinct IDs? Is there a
>> way without creating a temporary table to execute:
>>
>> DELETE FROM Table WHERE ID IN (Select ID FROM TemporaryTable)
>>
>> Regards,
>> Robert

>
> 255, documented in the Oracle Reference manual. Please avoid asking
> questions you could answer easily yourself by consulting the docs.
>

Actually it is 1000.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions14a.htm#1029285

> As you don't specify the source of those id's, no one can tell what the
> 'best' way is.
> If you think you really need to use a temporary table, just create a
> globabl temporary table.
> However, this 'solution' stinks of sqlserver, and should be avoided, as
> it is sqlserver malpractice ported to Oracle.
>

You could also consider the use of pipelined functions, to workaround the IN (expression_list) restriction.

http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#21299

Best regards

Maxim Received on Wed Jul 12 2006 - 05:00:52 CDT