My Oracle Support Banner

Error: "PL/SQL: ORA-00904: "SEARCH_CONDITION_VC": invalid identifier" When Migrating P6 EPPM to P6 PPM Hosted In An Oracle 11g Database (Doc ID 2466572.1)

Last updated on JULY 25, 2019

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 15.2.18.0 to 15.2.18.2 [Release 15.2]
Information in this document applies to any platform.

Symptoms

When migrating the current EPPM Database R15.2.8.0 to PPM hosted in an Oracle 11g database, the following error occurs during step 'Core PM to PPM Schema Migration':

ERROR
-----------------------
Error in Run SQL Script orpm_ppm_migrate.sql : DatabaseToolException
(ORA-06550: line 15, column 123:
PL/SQL: ORA-00904: "SEARCH_CONDITION_VC": invalid identifier
ORA-06550: line 15, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 165:
PL/SQL: ORA-00904: "SEARCH_CONDITION_VC": invalid identifier
ORA-06550: line 19, column 2:
PL/SQL: SQL Statement ignored
)for query::declare
is_not_null integer:=0;
v_con_exist integer:=0;
v_stat_trigg integer:=0;
v_con_exist_not_valid integer;
v_constraint_name varchar2(30);
begin
select count(*) into v_stat_trigg from USER_TRIGGERS where
TABLE_NAME='ITERATION' and STATUS ='ENABLED';
IF v_stat_trigg >=1 THEN
execute immediate 'ALTER TABLE ITERATION DISABLE ALL TRIGGERS';
END IF;
UPDATE ITERATION SET RELEASE_ID=1610;
COMMIT;
select 1 into is_not_null from user_tab_columns where
table_name=upper('ITERATION') and nullable='Y' and column_name
=upper('RELEASE_ID');
select count(*) into v_con_exist from user_constraints where
table_name=upper('ITERATION') and constraint_type='C' and SEARCH_CONDITION_VC
like '%RELEASE_ID%NOT NULL';
if (is_not_null = 1 and v_con_exist = 0 )then
EXECUTE IMMEDIATE 'ALTER TABLE ITERATION modify RELEASE_ID NOT NULL';
elsif (is_not_null = 1 and v_con_exist > 0 ) then
select constraint_name into v_constraint_name from user_constraints where
table_name=upper('ITERATION') and constraint_type='C' and validated='NOT
VALIDATED' and search_condition_vc like '%RELEASE_ID%NOT NULL';
execute immediate 'ALTER TABLE ITERATION modify constraint
'||v_constraint_name||' validate';
end if;
exception when NO_DATA_FOUND then
null;
end;


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Locate to P6 EPPM R15.2.18 media pack to launch migrate utility
2. During step 'Core PM to PPM Schema Migration', the error prompted

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete the migration.

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.