In a report I've created using a catalog picklist prompt, I get the error "ORA-01795 Maximum number of expressions in a list is 1000". I've changed my impromptu.ini file settings for a picklist prompt to contain up to 10,000 values. The picklist in question only has a total of 8,000 rows. Is there something I'm missing here?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ORA-01795 Error
- Thread starter KellyK
- Start date
quatermass
Technical User
Hi Kelly,
The ORA at the start of the error message indicates that it is a limit within oracle itself.
If you look up error 01795 on oracle you will find out what the error is and (hopefully) how to rectify it.
As I normally use SQL Server, the only times I encounter this problem is on client sites. It's at times like those that I call on the oracle dba.
Hope it is of some help
Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************
The ORA at the start of the error message indicates that it is a limit within oracle itself.
If you look up error 01795 on oracle you will find out what the error is and (hopefully) how to rectify it.
As I normally use SQL Server, the only times I encounter this problem is on client sites. It's at times like those that I call on the oracle dba.
Hope it is of some help
Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************
Kelly,
The text for the Oracle error mentioned is:
ORA-01795 maximum number of expressions in a list is 1000
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.
I'm not sure why creating a large catalog picklist would cause this, as I've had Oracle picklists of this type well over 1000 rows without seeing this error. Unless the user really needs to scroll all those rows in the picklist, why not create a set of cascading report picklists (i.e. actual picklist in the final report uses a report picklist, which in turn uses a different report picklist, each asking specific questions via prompts that shows only the elements in the set specified in the prior report filter.) that traverse the user's needs and results in a small final picklist?
Hope this helps,
Dave Griffin
The text for the Oracle error mentioned is:
ORA-01795 maximum number of expressions in a list is 1000
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.
I'm not sure why creating a large catalog picklist would cause this, as I've had Oracle picklists of this type well over 1000 rows without seeing this error. Unless the user really needs to scroll all those rows in the picklist, why not create a set of cascading report picklists (i.e. actual picklist in the final report uses a report picklist, which in turn uses a different report picklist, each asking specific questions via prompts that shows only the elements in the set specified in the prior report filter.) that traverse the user's needs and results in a small final picklist?
Hope this helps,
Dave Griffin
- Status
Similar threads
- Locked
- Question