Upgrade to iProcess Engine 11.5.0 upgrade can fail with error ORA-00942: table or view does not exist

Products	Versions
TIBCO iProcess Engine (Oracle)	-

Description

Even if using the updated uorai115_00_tok.sql file attached to Knowledge Base article 000022068 the upgrade to iProcess Engine 11.5.0 can fail with errors similar to:

uorai115_00-29500.tablelog

    for ai in (select * from TABLE_OWNER.attachment_old)
                                                *
ERROR at line 2:
ORA-06550: line 2, column 49:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 9:
PLS-00364: loop index variable 'AI' use is invalid
ORA-06550: line 6, column 12:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 36:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

The 11.5.0 database upgrade script failed on:

alter table TABLE_OWNER.attachment drop constraint FK_ATTACHMENT

with
DECLARE
*
ERROR at line 1:
ORA-02443: Cannot drop constraint - nonexistent constraint
ORA-06512: at line 11

The constraint FK_ATTACHMENT did not exist in the iProcess database schema. As a result, the ATTACHMENT table was not renamed to ATTACHMENT_OLD and the upgrade was unable to complete. By default, the iProcess ATTACHMENT database table should have the following constraints:

    PK_ATTACHMENT
    FK_ATTACHMENT

The FK_ATTACHMENT was missing for some reason. The uorai115_00_tok.sql dropped the constraints using:

execute immediate 'alter table TABLE_OWNER.attachment drop constraint PK_ATTACHMENT';
execute immediate 'alter table TABLE_OWNER.attachment drop constraint FK_ATTACHMENT';

Issue/Introduction

Environment

TIBCO iProcess Engine (Oracle) All platforms Upgrading from a pre- 11.5.0 version to iProcess 11.5.0 or above

Resolution

The uorai115_00_tok.sql script has been amended to check for the existence of the constraint and only drop the constraint if it existed in the iProcess database.

select count(*) into v_count from user_constraints where table_name = upper('attachment') and constraint_name = 'PK_ATTACHMENT';
   if vcount !=0 then
            execute immediate 'alter table TABLE_OWNER.attachment drop constraint PK_ATTACHMENT';
   end if;
select count(*) into v_count from user_constraints where table_name = upper('attachment') and constraint_name = 'FK_ATTACHMENT';
   if vcount !=0 then
            execute immediate 'alter table TABLE_OWNER.attachment drop constraint FK_ATTACHMENT';
   end if;

The updated SQL script is attached to this article. (Filename: uorai115_00_tok.sql).

To implement the script when performing a Linux/Unix upgrade use the steps:
1. Download the uorai115_00_tok.sql from this article.
2. Replace the uorai115_00_tok.sql file in the upgrade temporary installation directory with the uorai115_00_tok.sql from this article. Ensure ownership and permissions are the same as the others and the one it replaced.
3. Perform the upgrade.

To implement the script when performing a Windows upgrade use the steps:
1. Run the setup.exe to perform the custom installation for upgrading.
2. Respond to the prompts as appropriate.
3. Once you have selected the node to upgrade you should be prompted with a "Question" message about removing custom constraints, statistics and indexes etc. At this point DO NOT press the Yes button.
4. Identify the directory associated with the TEMP system variable for the user running the upgrade, for example use the 'set' command in a command window.
5. Using File Manager for example navigate to the directory associated with the TEMP system variable identified in point 4.
6. You should have a sub-directory with the name something like "_ISTMP1.DIR" which has a sub-directory something like "_ISTMP0.DIR". In this directory should be a number the extracted installation files including the database upgrade scripts.
7. Move the current uorai115_00_tok.sql file to a location outside the temporary installation directory and replace it with the updated one supplied with this article. Ensure ownership and permissions are the same as the others and the one it replaced.
8. Continue with the upgrade by responding to the prompts as appropriate.

This issue is referenced by defect IPE-3799 that has been corrected in iProcess Engine version 11.6.1.

Attachments

Upgrade to iProcess Engine 11.5.0 upgrade can fail with error ORA-00942: table or view does not exist get_app

Welcome to "KB Articles"