E-ES: / E-OS: Getting Error "ORA-22922: nonexistent LOB value" When Attempting to Run the Full Build for a Search Definition
(Doc ID 3028929.1)
Last updated on JUNE 24, 2024
Applies to:
PeopleSoft Enterprise PT PeopleTools - Version 8.60 and laterInformation in this document applies to any platform.
Symptoms
In this particular case, the issue was observed when running the Full Index Build for the PTSF_RECENTMENU index.
After deleting the rows that had URL length too long, from the PS_PT_PORTAL_MRU table (that's the underlying table in the query for the PTSF_RECENTMENU index) in the backend, the Index Build for PTSF_RECENTMENU failed with the ORA-22922 - error.
Error observed in the index build process stdout file in Process Monitor (<PS_CFG_HOME>/appserv/prcs/<Domain Name>/log_output/AE_PTSF_GENFEED_<processInstance>.stdout):
Failed SQL stmt: SELECT A.OPRID, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.LANGUAGE_CD, A.PORTAL_LABEL, A.PORTAL_NAME, A.PT_PORTAL_MRU_ID, A.PORTAL_OBJNAME, A.PORTAL_URLTEXT, A.USECOUNT, '/f/[%A.OPRID%%B.PORTAL_BASE_URI%%A.PORTAL_NAME%/%B.NODE_ITEMNAME%/%A.PORTAL_URLTEXT%]', B.NODE_ITEMNAME, B.PORTAL_BASE_URI, C.PORTAL_PRNTLABEL, C.PORTAL_NAVPATH, B.DESCR254, C.PORTAL_SOURCE, A.PTSF_NAV_FROM, D.PORTAL_ATTR_VAL, B.PORTAL_LABEL FROM PS_PT_PORTAL_MRU A, PSPRSM_SRCH_VW B, PSPRSMNAVINFO C, PSPRSMHIDDEN D WHERE ( A.LASTUPDDTTM > TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF') AND A.PORTAL_NAME = B.PORTAL_NAME AND B.PORTAL_OBJNAME = A.PORTAL_OBJNAME AND A.PORTAL_NAME = C.PORTAL_NAME AND C.PORTAL_OBJNAME = A.PORTAL_OBJNAME AND A.PORTAL_NAME = D.PORTAL_NAME AND A.PORTAL_OBJNAME = D.PORTAL_OBJNAME AND A.PORTAL_LABEL <> ' ')
Database Driver Error. (30,11)
Total documents processed is 641400 and Total rows processed 7503871 (262,3097)
Base Language documents published (262,2009)
The issue was reproducible in the customer site with the following steps:
1. Using a Query tool, such as SQL Developer, delete rows from the PS_PT_PORTAL_MRU table to remove rows that have URL length too long.
2. Run the Index Build for the PTSF_RECENTMENU index.
3. Observe the error in the output files.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |