My Oracle Support Banner

E-PORTAL: Change Assistant Error When Creating View EOFD_SEARCH_VW - TO_CHAR Issue (Doc ID 2494586.1)

Last updated on NOVEMBER 22, 2019

Applies to:

PeopleSoft Enterprise PRTL Interaction Hub - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

On : 9.1 version, General

When attempting to install PUM Image# 6 through Change Assistant, the following error occurs in step Creating Views.
Database is on MS SQL Server.


ERROR
-----------------------
CP_delvw_crtvw.log shows:
Msg 195, Level 15, State 10, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 5
'TO_CHAR' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 13
Incorrect syntax near ')'.
Msg 195, Level 15, State 10, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 15
'TO_CHAR' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 22
'TO_CHAR' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 28
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Server XYZABC, Procedure PS_EOFD_SEARCH_VW, Line 30
Incorrect syntax near ')'.
CREATE VIEW PS_EOFD_SEARCH_VW (EOFD_MESSAGE_ID, EOFD_MSG_PRNT_ID,
OPRID, EOFD_CONTENT, CREATEDTTM, URL_ID, ATTACHSYSFILENAME,
ATTACHUSERFILE, EOFD_STATUS, EOFD_DSCNTYPE_DSCR, LASTUPDDTTM) AS (
SELECT A2.EOFD_MESSAGE_ID , A2.EOFD_MSG_PRNT_ID , A2.OPRID ,
TO_CHAR(A2.EOFD_CONTENT) , A2.CREATEDTTM , A2.URL_ID ,
A2.ATTACHSYSFILENAME , A2.ATTACHUSERFILE , A2.EOFD_STATUS ,
SUBSTR(CMP.DESCR ,1 ,30) ,A2.LASTUPDDTTM FROM PS_EOFD_DSCN_MSG A2 ,
PS_EOFD_DSCN_CNTXT CT2 , PSPNLGRPDEFN CMP WHERE A2.EOFD_MSG_PRNT_ID =
' ' AND A2.EOFD_MESSAGE_ID = CT2.EOFD_MESSAGE_ID AND CT2.EOFD_KEY =
'COMPONENT' AND CT2.EOFD_VALUE = CMP.PNLGRPNAME AND CMP.MARKET =
'GBL' AND EXISTS( SELECT 'X' FROM PS_EOFD_DSCN_CNTXT CT3 WHERE
CT3.EOFD_KEY = 'DSCN_TYPE_ID' AND CT3.EOFD_VALUE IN(' ' ,'DEFAULT')
AND A2.EOFD_MESSAGE_ID = CT3.EOFD_MESSAGE_ID)) UNION ( SELECT
A.EOFD_MESSAGE_ID , A.EOFD_MSG_PRNT_ID , A.OPRID ,
TO_CHAR(A.EOFD_CONTENT) , A.CREATEDTTM , A.URL_ID ,
A.ATTACHSYSFILENAME , A.ATTACHUSERFILE , A.EOFD_STATUS ,
SUBSTR(DT.DESCR100 ,1 ,30) ,A.LASTUPDDTTM FROM PS_EOFD_DSCN_MSG A ,
PS_EOFD_DSCN_CNTXT CT , PS_EOFD_DSCN_TYPE DT WHERE A.EOFD_MSG_PRNT_ID
= ' ' AND A.EOFD_MESSAGE_ID = CT.EOFD_MESSAGE_ID AND CT.EOFD_KEY =
'DSCN_TYPE_ID' AND CT.EOFD_VALUE NOT IN(' ' ,'DEFAULT') AND
CT.EOFD_VALUE = EOFD_DSCN_TYPE_ID ) UNION ( SELECT A3.EOFD_MESSAGE_ID
, A3.EOFD_MSG_PRNT_ID , A3.OPRID , TO_CHAR(A3.EOFD_CONTENT) ,
A3.CREATEDTTM , A3.URL_ID , A3.ATTACHSYSFILENAME , A3.ATTACHUSERFILE
, A3.EOFD_STATUS , 'Default' ,A3.LASTUPDDTTM FROM PS_EOFD_DSCN_MSG A3
WHERE A3.EOFD_MSG_PRNT_ID = ' ' AND EXISTS( SELECT 'X' FROM
PS_EOFD_DSCN_CNTXT CT4 WHERE CT4.EOFD_KEY = 'DSCN_TYPE_ID' AND
CT4.EOFD_VALUE IN(' ' ,'DEFAULT') AND A3.EOFD_MESSAGE_ID =
CT4.EOFD_MESSAGE_ID) AND NOT EXISTS( SELECT 'X' FROM
PS_EOFD_DSCN_CNTXT CT5 WHERE CT5.EOFD_KEY = 'COMPONENT' AND
A3.EOFD_MESSAGE_ID = CT5.EOFD_MESSAGE_ID))


The issue can be reproduced at will with the following steps:
1. Use Change Assistant to install PUM Image# 6
2. Run the job
3. It will fail on step Creating Views, for Record View EOFD_SEARCH_VW.



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


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