SELECT: list too long

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 Received on Sat Apr 09 2005 - 12:06:33 CDT