Home » SQL & PL/SQL » SQL & PL/SQL » how many number of elements passed to in operator
how many number of elements passed to in operator [message #305877] Wed, 12 March 2008 05:17 Go to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Hi,

How many number of arguments can be passed to in operator because when i am trying to pass more than 1000 arguments it is throwing exception can any body help me.

This is what the exception is

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


Thanks In advance,
Raj.
Re: how many number of elements passed to in operator [message #305879 is a reply to message #305877] Wed, 12 March 2008 05:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What don't you understand about "maximum number of expressions in a list is 1000"

Re: how many number of elements passed to in operator [message #305880 is a reply to message #305877] Wed, 12 March 2008 05:20 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.



regards,
Re: how many number of elements passed to in operator [message #305881 is a reply to message #305879] Wed, 12 March 2008 05:21 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
I understood what is the error is but how to handle this problem.

Thanks,
Raj.
Re: how many number of elements passed to in operator [message #305882 is a reply to message #305877] Wed, 12 March 2008 05:23 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Quote:
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.







regards,
Re: how many number of elements passed to in operator [message #305886 is a reply to message #305882] Wed, 12 March 2008 05:26 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
may be this should help you :
http://www.orafaq.com/forum/t/41437/0/

regards,
Re: how many number of elements passed to in operator [message #305888 is a reply to message #305881] Wed, 12 March 2008 05:26 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You'll have to rewrite your query ...

One possible solution could be:

 WHERE ( column IN ( a bunch of values )
  OR column in ( another bunch of values )
  OR ...
  )



Off course there are far more ways to solve your particular problem.
Re: how many number of elements passed to in operator [message #305889 is a reply to message #305882] Wed, 12 March 2008 05:26 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
@mshrkshl

i want to to place more than 1000 literals within "in" operator

Raj.
Re: how many number of elements passed to in operator [message #305892 is a reply to message #305889] Wed, 12 March 2008 05:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
mr.rajeshyadav wrote on Wed, 12 March 2008 11:26
@mshrkshl

i want to to place more than 1000 literals within "in" operator

Raj.


Well, you can't, it's that simple: Oracle won't allow it: 1000 is the limit.
Re: how many number of elements passed to in operator [message #305893 is a reply to message #305888] Wed, 12 March 2008 05:31 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
@MarcS

Thanks,
The number of literals may change depending on the results returned by the nested query so i can't decide how many number of bunches i have to place.

Thanks,
Raj.
Re: how many number of elements passed to in operator [message #305894 is a reply to message #305893] Wed, 12 March 2008 05:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Then populate all your literals in a table and do something like this.
select * from table_a where <column> in (select literal_value from literal_table

Did you even bother to check the link which @dhananjay has posted. It has given you many options and still you are asking us how to do it ?
Regards

Raj

[Updated on: Wed, 12 March 2008 05:37]

Report message to a moderator

Re: how many number of elements passed to in operator [message #305895 is a reply to message #305893] Wed, 12 March 2008 05:34 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
mr.rajeshyadav wrote on Wed, 12 March 2008 11:31
@MarcS

Thanks,
The number of literals may change depending on the results returned by the nested query so i can't decide how many number of bunches i have to place.

Thanks,
Raj.


depending on the results returned by the nested query Shocked

Why don't you try:

  SELECT select_list
  FROM table
  WHERE column IN
     (
      nested_query
     ) ;



And if you want good answers, your question needs to be complete.
You never mentioned that the "literals" you want to pass the IN-operator were the results of a nested query
Re: how many number of elements passed to in operator [message #305900 is a reply to message #305895] Wed, 12 March 2008 05:44 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
@MarcS

Sorry for for in-complete question i am using the query what ever you have given but the nested query may return any number of records depending on the conditions placed for the nested query so in that case the nested query may return more than 1000 also so in like those situations oracle is throwing Exception.

So how can i handle this situation.

Thanks,
Raj.
Re: how many number of elements passed to in operator [message #305902 is a reply to message #305900] Wed, 12 March 2008 05:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
mr.rajeshyadav wrote on Wed, 12 March 2008 11:44
@MarcS

Sorry for for in-complete question i am using the query what ever you have given but the nested query may return any number of records depending on the conditions placed for the nested query so in that case the nested query may return more than 1000 also so in like those situations oracle is throwing Exception.

So how can i handle this situation.

Thanks,
Raj.


Have you tried my sample with the nested query?
It'll even work when the nested query returns more than 1.000 records.
Re: how many number of elements passed to in operator [message #305903 is a reply to message #305900] Wed, 12 March 2008 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not a problem.
An IN clause can handle any number of results passed in via a sub-query. The 1000 limit applies only to hard coded literal values.
Re: how many number of elements passed to in operator [message #305904 is a reply to message #305902] Wed, 12 March 2008 05:49 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Let's stop and take a breath. @mr.rajeshyadav You would be much more likely to give us a more accurate idea of exactly what you are doing if you post the code that is generating the error that you are having problems with.
Previous Topic: Can't figure out the simplest, two-line proc!
Next Topic: zero padding decimal in oracle
Goto Forum:
  


Current Time: Sun Dec 04 10:47:49 CST 2016

Total time taken to generate the page: 0.06669 seconds