Home » SQL & PL/SQL » SQL & PL/SQL » Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 (Oracle 7)
Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424637] Mon, 05 October 2009 01:01 Go to next message
NileshKS
Messages: 14
Registered: February 2009
Location: India
Junior Member

Hi,

The following exception is occuring on save button pressed event:

Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254.

Query under execution is something like
delete from worklocation where location in (Lo1,Lo2,...);

Can anyone suggest how many parameters can IN hold in Oracle7/8/9.
Any alternative to overcome this exception as we can not modify this query since this is embedded in the other language.

Thanks,
NKS

[Updated on: Mon, 05 October 2009 01:03]

Report message to a moderator

Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424638 is a reply to message #424637] Mon, 05 October 2009 01:08 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,


Maximum up to 1000 are allowed for IN.

Regards

Bala
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424641 is a reply to message #424637] Mon, 05 October 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have only 2 ways:
- upgrade to a newer version
- change the query
If you cannot do any of them, you are stuck to this limit.

Regards
Michel
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424642 is a reply to message #424641] Mon, 05 October 2009 01:43 Go to previous messageGo to next message
NileshKS
Messages: 14
Registered: February 2009
Location: India
Junior Member

Thanks,

What would be the limit to hold values in IN operator in higher versions of oracle 8/9 or 10g?
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424643 is a reply to message #424637] Mon, 05 October 2009 01:47 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member


Hi,

As i said already .. you have the same limit of 1000 values using IN in 8,9&10g.

Regards

Bala
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424644 is a reply to message #424643] Mon, 05 October 2009 01:51 Go to previous messageGo to next message
NileshKS
Messages: 14
Registered: February 2009
Location: India
Junior Member

Michel,

How would it help if we upgrade to higher version as Bala is saying in below Post reply.

Regards,
NKS
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424646 is a reply to message #424644] Mon, 05 October 2009 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you upgrade the limit goes from 254 to 1000.

Regards
Michel
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424689 is a reply to message #424637] Mon, 05 October 2009 06:41 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Hi,

Use UNION ALL, for every set of 254. That should solve the problem temporarily.

select * from table A
where cola in('a','b','c')
union all
select * from table A
where cola in('x','y','z') ;

Thanks,
Elaiyavel.
Re: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254 [message #424690 is a reply to message #424689] Mon, 05 October 2009 06:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you'd read the first post, you might have spotted this:Quote:
...as we can not modify this query ...
Previous Topic: Max function with line item details
Next Topic: compare two columns in same table
Goto Forum:
  


Current Time: Wed Dec 07 23:58:23 CST 2016

Total time taken to generate the page: 0.06447 seconds