We are trying to use a DB Link to retrieve data from an external Oracle DB and use the retrieved fields in a PeopleSoft page in Campus Solutions (Oracle DB). We get the following SQL error message.
PSAPPSRV.1540194 (1114) [06/22/12 14:09:34 (IE 7.0; WINXP) ICPanel](3) File: /vob/peopletools/src/psppr/ramget.cppSQL error. Stmt #: 655 Error Position: 0 Return: 1858 - ORA-01858: a non-numeric character was found where a numeric was expected ORA-02063: preceding line from ISDS
Failed SQL stmt:SELECT DISTINCT UV_VENDOR_NUMBER, UV_VENDOR_NAME FROM PS_UV_SF_STU_BKACT WHERE UV_VENDOR_NUMBER LIKE 'SIS-ABC647259%' ORDER BY UV_VENDOR_NUMBER
A record-view was created in PeopleSoft to retrieve the fields from an external DB view using DB Link. This PeopleSoft view was to be used as the search record for a custom PeopleSoft component/page and display the external DB fields on that PeopleSoft page. There are 11 fields on this custom PeopleSoft view 2 of which are field type NUMBER and the rest of the fields are type VARCHAR2. The search field on this view is a VARCHAR2 field. We have validated the record field order with the view select statement field order to make sure the selected fields are inserted into the correct field type/length.
We eventually stripped down the PS view to the one VARCHAR2 field that is used as a search key (record.view definition) and still get the ORA error above. We are at a loss as to why PeopleSoft's search select statement is getting this error when there is only one VARCHAR2 field in the view and related SQL statement.
Has anyone had a similiar issue? or any suggestions? Again, both the PeopleSoft DB and the External DB are Oracle. The search field is defined as VARCHAR2 on both the PeopleSoft DB and the External DB.