Re: Urgent: Information required about Error Code ORA-1795

What is your oracle version?

I remember oracle 7.3. had this limitation of 254 when using "in", but 8.0 seem to lift that. I don't know if there is any limit now or what the limit is.

Anyway, one way you can do is to use loop so that each time it will delete up to 254 rows until all the rows get deleted.

Hope this helps.

Guang

ramsunders_at_yahoo.com (R197509) wrote in message news:<1e562f83.0203150228.6eae439e_at_posting.google.com>...
> Hi,
> We have a query which does a delete based on a range of values.
> Delete table_x where pk in (1,2,3,4,5);
> This list is created dynamically and the number of elements varies
> depending on the records in the database.
> While testing, we had around 550 values in the list and the
> execution of this statement gave us the ORA-1795 error.
> The Oracle documentation gives the following information on this
> error code:
> ORA-01795 maximum number of expressions in a list is 1000
> Cause: More than 254 columns or expressions were specified in a list.
> Action: Remove some of the expressions from the list.
> I have a couple of doubts on this.
> Does this error code mean to say that the number of elements in the
> list should be less than 254?
> Does it imply that the total length of the expression (between the
> brackets) should be less than 1000?
> If the above two are indeed the right interpretation, how do we go
> about handling such a SQL statement wherein the number of elements in
> the list is dynamic?
> Also, if we use a sub-query instead of a literal list, then is
> there some kind of limitation on the number of records that the
> subquery is allowed to return. For E.g. would the following also give
> ORA-1795?
> Delete table_x where pk in (select pk from table_x where rownum <
> 100000);
>
> Any help is appreciated.
> Regards,
> Ramsunder.
Received on Sat Mar 16 2002 - 23:14:28 CET