My Oracle Support Banner

E-ES: Real Time Index Not Working for EP_AM_ASSET Search Definition-"ORA-00933: SQL command not properly ended" (Doc ID 3009360.1)

Last updated on MAY 03, 2024

Applies to:

PeopleSoft Enterprise FIN Asset Management - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms


With RTI (Real Time Indexing) enabled for EP_AM_ASSET in Elasticsearch 7.10, search does not work. (enable RTI in PT 8.60 for EP_AM_ASSET search definition and do an EP_AM_ASSET search transaction)

This is even after disabling attachment, reloading the trigger, and bouncing the Process Scheduler and clearing cache.

RTI transaction generates data in table PS_PTSF_RTISTG_S for failed transaction. With increased log fence to 5 in psprcs.cfg file on the Process Scheduler, the appsrv.log registers the following error-

PSRTISRV.2708387 (0) [2024-02-05T00:04:08.869] - - - (3) File: /vob/peopletools/src/psrtisrv/psrtisrv.hSQL error. Stmt #: 12414 Error Position: 770 Return: 933 - ORA-00933: SQL command not properly ended

Failed SQL stmt: SELECT A.BUSINESS_UNIT_PO, A.BUSINESS_UNIT_AP, A.CATEGORY, A.PO_ID, A.BUSINESS_UNIT_RECV, A.RECEIVER_ID, A.VOUCHER_ID, A.INVOICE_ID, A.VENDOR_ID, B.VENDOR_NAME_SHORT, B.NAME1, B.NAME2, C.DESCR, D.DESCR, E.DESCR, F.DESCR, B.SETID, B.VENDOR_ID, D.BUSINESS_UNIT, A.BUSINESS_UNIT, A.ASSET_ID, A.BUSINESS_UNIT, A.ASSET_ID, B.SETID, A.VENDOR_ID, A.CATEGORY FROM (((((PS_ASSET_SES_ACQVW A LEFT OUTER JOIN PS_VENDOR B ON B.VENDOR_ID=A.VENDOR_ID AND A.SETID_VENDOR=B.SETID)) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS C ON A.BUSINESS_UNIT_PO=C.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS D ON A.BUSINESS_UNIT_AP=D.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS E ON A.BUSINESS_UNIT_PC=E.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS F ON A.BUSINESS_UNIT_RECV=F.BUSINESS_UNIT) WHERE 1 = 2 AND 1 = 1
PSRTISRV.2708387 (0) [2024-02-05T00:04:08.869] - - - (1) GenMessageBox(200, 0, M): /vob/peopletools/src/psrtisrv/psrtisrv.h: File: /vob/peopletools/src/psrtisrv/psrtisrv.hSQL error. Stmt #: 12414 Error Position: 770 Return: 933 - ORA-00933: SQL command not properly ended
Failed SQL stmt: SELECT A.BUSINESS_UNIT_PO, A.BUSINESS_UNIT_AP, A.CATEGORY, A.PO_ID, A.BUSINESS_UNIT_RECV, A.RECEIVER_ID, A.VOUCHER_ID, A.INVOICE_ID, A.VENDOR_ID, B.VENDOR_NAME_SHORT, B.NAME1, B.NAME2, C.DESCR, D.DESCR, E.DESCR, F.DESCR, B.SETID, B.VENDOR_ID, D.BUSINESS_UNIT, A.BUSINESS_UNIT, A.ASSET_ID, A.BUSINESS_UNIT, A.ASSET_ID, B.SETID, A.VENDOR_ID, A.CATEGORY FROM (((((PS_ASSET_SES_ACQVW A LEFT OUTER JOIN PS_VENDOR B ON B.VENDOR_ID=A.VENDOR_ID AND A.SETID_VENDOR=B.SETID)) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS C ON A.BUSINESS_UNIT_PO=C.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS D ON A.BUSINESS_UNIT_AP=D.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS E ON A.BUSINESS_UNIT_PC=E.BUSINESS_UNIT) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS F ON A.BUSINESS_UNIT_RECV=F.BUSINESS_UNIT) WHERE 1 = 2 AND 1 = 1

 It also generates dump files for PSRTISRV process. The process_state.txt registers the following stack:

<signal handler called>

0x00000000004588e3 in std::vector<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> >, std::allocator<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> > > >::vector(std::vector<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> >, std::allocator<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> > > > const&) ()
0x000000000046045a in std::vector<std::vector<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> >, std::allocator<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> > > >, std::allocator<std::vector<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> >, std::allocator<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> > > > > >::push_back(std::vector<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> >, std::allocator<std::basic_string<wchar_t, std::pswchar_traits, std::allocator<wchar_t> > > > const&) ()

 

 

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
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.