E-CA: PeopleTools 8.54.10, 8.54.11, or 8.54.12 Maintenance Issue With Create Views Script "Failed SQL stmt: SET DEFINE OFF..." (Doc ID 2005070.1)

Last updated on FEBRUARY 18, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 and later
Information in this document applies to any platform.

Symptoms

On : 8.54.10, 8.54.11, or 8.54.12 version, Change Assistant

When attempting to run CreateViews.sql,
an error similar to the following is thrown.

ERROR
-----------------------
Message Set Number: 200
Message Number: 0
Message Reason: File: Data MoverSQL error. Stmt #: 0  Error Position: 4  Return: 922 - ORA-00922: missing or invalid option
Failed SQL stmt: SET DEFINE OFF CREATE VIEW PS_AGENCY_CODE_VW (SETID, ALC, ALC_NON_DISBRSD, AGENCY_NAME, DESCRSHORT, ALC_BUS_ACTIVITY) AS SELECT CD.SETID ,CD.ALC ,CD.ALC_NON_DISBRSD ,CD.AGENCY_NAME ,CD.DESCRSHORT ,CD.ALC_BUS_ACTIVITY FROM PS_AGENCY_LOC_CD CD WHERE (CD.ALC_NON_DISBRSD = 'Y' OR CD.ALC IN ( SELECT CD2.ALC FROM PS_AGENCY_LOC_CD2 CD2 WHERE CD2.SETID = CD.SETID AND CD2.ALC = C (200,0)
SQL Error. Error Position: 4  Return: 922 - ORA-00922: missing or invalid option

SET DEFINE OFF CREATE VIEW PS_AGENCY_CODE_VW (SETID, ALC, ALC_NON_DISBRSD, AGENCY_NAME, DESCRSHORT, ALC_BUS_ACTIVITY) AS SELECT CD.SETID ,CD.ALC ,CD.ALC_NON_DISBRSD ,CD.AGENCY_NAME ,CD.DESCRSHORT ,CD.ALC_BUS_ACTIVITY FROM PS_AGENCY_LOC_CD CD WHERE (CD.ALC_NON_DISBRSD = 'Y' OR CD.ALC IN ( SELECT CD2.ALC FROM PS_AGENCY_LOC_CD2 CD2 WHERE CD2.SETID = CD.SETID AND CD2.ALC = CD.ALC AND CD.ALC_BUS_ACTIVITY NOT IN ('00') AND CD2.GWA_REPORTER_CATG NOT IN ('00','01')))
Error: SQL execute error for SET DEFINE OFF CREATE VIEW PS_AGENCY_CODE_VW (SETID, ALC, ALC_NON_DISBRSD, AGENCY_NAME, DESCRSHORT, ALC_BUS_ACTIVITY) AS SELECT CD.SETID ,CD.ALC ,CD.ALC_NON_DISBRSD ,CD.AGENCY_NAME ,CD.DESCRSHORT ,CD.ALC_BUS_ACTIVITY FROM PS_AGENCY_LOC_CD CD WHERE (CD.ALC_NON_DISBRSD = 'Y' OR CD.ALC IN ( SELECT CD2.ALC FROM PS_AGENCY_LOC_CD2 CD2 WHERE CD2.SETID = CD.SETID AND CD2.ALC = CD.ALC AND CD.ALC_BUS_ACTIVITY NOT IN ('00') AND CD2.GWA_REPORTER_CATG NOT IN ('00','01')))

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Attempt to apply maintenance up to the CreateViews.sql step.

Cause

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