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: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 09 Apr 2005 12:31:39 -0700
Message-ID: <1113074876.717913@yasure>


eban wrote:
> 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

There are very few situations where hard coding is not a bad idea. Make it dynamic by using a table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Apr 09 2005 - 14:31:39 CDT

Original text of this message

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