Solution #1:
Separate the alter statement into multiple statements. Below is the way to do that for step C-15. Run it outside the script then re-run the UORAv570-580.sql.
-- ********************************************
DECLARE
AlterDone NUMBER;
BEGIN
SELECT COUNT(*) INTO AlterDone FROM USER_TAB_COLUMNS WHERE TABLE_NAME='MERC_PS_SWAT_EVENT' AND COLUMN_NAME='SYNC_PS_OBJ_FLAG2';
IF AlterDone < 1 THEN
execute immediate ('ALTER TABLE STAT.MERC_PS_SWAT_EVENT ADD SYNC_PS_OBJ_FLAG2 VARCHAR2(1 CHAR)');
execute immediate ('UPDATE STAT.MERC_PS_SWAT_EVENT SET SYNC_PS_OBJ_FLAG2 = ''N''');
DBMS_OUTPUT.put_line('* SYNC_PS_OBJ_FLAG2 - Updated');
execute immediate ('ALTER TABLE STAT.MERC_PS_SWAT_EVENT MODIFY(SYNC_PS_OBJ_FLAG2 DEFAULT ''N'' NOT NULL)');
DBMS_OUTPUT.put_line('* MERC_PS_SWAT_EVENT - DEFAULT added');
execute immediate ('ALTER TABLE STAT.MERC_PS_SWAT_EVENT ADD CONSTRAINT MERC_PS_SWAT_EVENT_CS1 CHECK (SYNC_PS_OBJ_FLAG2 IN (''Y'',''N'') AND SYNC_PS_OBJ_FLAG2 IS NOT NULL)');
DBMS_OUTPUT.put_line('* MERC_PS_SWAT_EVENT_CS1 - New constraint added');
DBMS_OUTPUT.put_line('* MERC_PS_SWAT_EVENT - New column SYNC_PS_OBJ_FLAG2 added');
END IF;
END;
/
PROMPT * Alter Table MERC_PS_SWAT_EVENT add column SYNC_PS_OBJ_FLAG2
-- ********************************************
Solution #2:
In case the Solution #1 doesn't solve, drop this table and rebuild it.
