IBM Support

A stored procedure error occurs after upgrading an Oracle database for IBM Business Process Manager (BPM) V7.5.1

Troubleshooting


Problem

A compile error for the LSW_ERASE_TASK stored procedure occurs within the Oracle database after you upgrade it from IBM Business Process Manager Version 7.5.0.x to Version 7.5.1

Symptom

In addition to the compilation error for the LSW_ERASE_TASK stored procedure, you see the following exception when you try to delete the process instances in the Process Inspector view:

java.sql.SQLException: ORA-06550: line 1,
column 7: PLS-00905: object PCS.LSW_BPD_INSTANCE_DELETE is invalid
ORA-06550: line 1, column 7: PL/SQL: Statement ignored

This problem does occur for an Oracle database that is created with new database scripts that are generated for new IBM Business Process Manager Version 7.5.1 profiles.

Cause

This problem has been identified as a product defect under APAR JR41733.

Resolving The Problem

There are two different processes for resolving this issue:

  • If you have upgraded the Oracle database to V7.5.1 using the upgrade_7x script , work with your Oracle administrator to edit the LSW_ERASE_TASK stored procedure. Remove the taskId NUMBER; and END; lines, which are marked in RED within the following screen shot:




  • If you have not upgraded the Process Server database, complete the following steps to avoid the problem:
    The wle_upgradeSchema_ProcessServer.sql file contains the LSW_ERASE_TASK stored procedure. This file is used with the upgrade_7x command to update the Process Server database. You can find the original file under the <BPM751-root>/dbscripts/ProcessServer/oracle/ directory. For example: C:\IBM\BPM751\dbscripts\ProcessServer\Oracle\wle_upgradeSchema_ProcessServer.sql
    1. Edit the wle_upgradeSchema_ProcessServer.sql file using a text editor.

    2. Search for the LSW_ERASE_TASK stored procedure procedure and delete the two lines marked *>>  taskId NUMBER; and *>> END; 


      Note: The *>> symbols will not be in the actual SQL file.


      See the following screen shot for an example.




    3. Run the BPMGenerateUpgradeSchemaScripts.sh command to regenerate the desired upgrade scripts. You can run this command multiple times; it updates the scripts under the <profile-root>/dbscripts directory.

    4. Ensure that the wle_upgradeSchema_ProcessServer.sql file, which was copied under the <profile-root>/dbscripts/ProcessServer/oracle/ directory, has the changes that you made earlier.

    5. Run the upgrade7x command to complete the ProcessServer database upgrade.

This problem is addressed with version 7.5.1 Fix Pack 1 and later.

See the product documentation links in the reference section for more detail on how to run BPMGenerateUpgradeSchemaScripts.sh and upgrade7x commands.

[{"Product":{"code":"SSFTDH","label":"IBM Business Process Manager Standard"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Migration","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.5.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSFTBX","label":"IBM Business Process Manager Express"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Migration","Platform":[{"code":"PF016","label":"Linux"},{"code":"","label":"Linux zSeries"},{"code":"PF033","label":"Windows"}],"Version":"7.5.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21578872

Page Feedback