Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SELECT: list too long

Re: SELECT: list too long

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Sat, 9 Apr 2005 10:54:36 -0700
Message-ID: <csqdnWezOeAvi8XfRVn-qw@comcast.com>

"eban" <n_at_TrouNoir.capubara.net> wrote in message news:42580b98$0$139$e4fe514c_at_news.xs4all.nl...
>
> Hi,
>
>
> I am writing a small script for a user which has a role for a database,
which do
> esnt give him rights to create a table.
> The user should be able to retrieve data for up to 2500 companies in the
databas
> e, which ones, is according to his request, and the query returns more
than one
> row for each of the companies.
> I have done this by way of a nested series of select statements, which end
in so
> mething like
> where companycode in ( 'cd1', 'cd2' ...)
>
> where the dots are the remaining codes of the companies the user is
requesting.
> I am getting this error
>
> ERROR at line 2413:
> ORA-01795: maximum number of expressions in a list is 1000
>
> Of course the problem is easily solved by running the script three times,
but th
> is is a bit clumsy.
>
> My conclusion is that this is not the way i am supposed to do this. I
think I am
> limited in using PLSQL, because the user gets his rights through a role,
and ha
> s no direct rights on tables.
> Doing a request for every single company would give a loop in the script
in the
> programme from which sqlplus is being called (either ox or R or an
NTscript), wh
> ich would make things too slow.
>
> How, then, should this be solved ?
>
> Thx for tips
>
> Armand
>
>

You could have 3 in lists
select * from .. where companycode in (:hv1,:hv2,:...,:hv1000) or companyCode in (:hv1001,:hv1002,...) etc. Or you create a global temporary table and have him insert into that table and (the codes) and join to that.
Jim Received on Sat Apr 09 2005 - 12:54:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US