Fatal SQL Error Received when over 1000 UNSPC Category Codes are Loaded and the Catalog is Searched in the eProcurement Requisition
Last updated on JUNE 04, 2018
Applies to:PeopleSoft Enterprise SCM eProcurement - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
A FATAL SQL error is received when accessing the Catalog tree in eProcurement.
This error occurrs when over 1000 UNSPC Category Codes are loaded and catalog is searched in the eProcurement Requisition.
Error: A SQL error occurred. Please consult your system log for details.
A fatal PeopleCode SQL error occurred. Please consult your system log for details.
Fatal SQL error occurred. (2,125) PV_NEWSRCH_WRK.PV_ADD_PB.FieldFormula Name:FillResultsRowset PCPC:174515 Statement:2745
Called from:PV_NEWSRCH_WRK.PV_ADD_PB.FieldFormula Name:CatBrowseSQLSearch Statement:2886
Called from:PV_REQ_CATBRW_RES.Activate Statement:26
A SQL error has occurred, review the SQL to determine the cause of the error.
Review the program for errors. If necessary, run a PeopleCode trace to determine the program that failed.
App Server Log Error Message: Error Position: 8540
Return: 1795 - ORA-01795: maximum number of expressions in a list is 1000
Issue can be recreated with the following steps:
- Login as Catalog Administrator. Navigate to Main Menu > Catalog Management > Catalogs > Maintain Catalogs
- Take an Existing Catalog and Navigate to Enterprise Manager tab
- Add the designated user say VP1 as Manager
- Navigate to Categories Tab and Add UNSPC as catalog Hierarchy
- Login as VP1
- Add a Linked Supplier of type Catalog Management Supplier for the Catalog chosen. Enter the Trading Partner ID as provided in Partners tab in Maintain Catalogs
- Create a new run control for CUP process with source Catalog Management. Select the designated catalog in the catalog ID field and add UNSPC as Hierarchy ID
- Add new value to the Tree name field
- Populate the account and currency fields in the Item Defaults tab and save the run control. Yes to message asking to create a new tree
- Run the PV_CP_LOAD process to success and posted
- When complete check that the UNSPC hierarchy from Catalog Management was loaded into tree manager. It can be verified at Tree Manager
- Navigate to eProcurement > Requisition and click on the new catalog from the list
- Error is received
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms