Enhancement Request - Improve Upgrade Process by Preserving Custom Code in the Custom Stored Procedures. Two Examples: SP_WM_CUSTOM_VALIDATION and SP_PSR_CUSTOM_VALIDATION (Doc ID 3046872.1)

Last updated on SEPTEMBER 10, 2024

Applies to:

Symptoms

Enhancement Request for the software upgrade process of applying the stored procedures. MetaSolv Solution provides several "empty" stored procedures (listed below) for the purposes of custom validation.  However, upgrading the database by "running master.sql" aka "running PROCS" overwrites the contents because of the REPLACE statement. Because of this, the contents must be reapplied after each upgrade. This is a manual process which can easily be overlooked.

• SP_NL_CUSTOM_VALIDATION
• SP_PSR_CUSTOM_PRE_VALIDATION
• SP_PSR_CUSTOM_SUPP_CANCEL
• SP_PSR_CUSTOM_VALIDATION
• SP_PSR_MSAG_CUSTOM_VALIDATION
• SP_WM_CUSTOM_VALIDATION

A possible solution would be creating an IF function to prevent replacing custom objects such as SP_WM_CUSTOM_VALIDATION. If custom code exists, only update the versioning of the custom stored procedure (line 2 - example image below).  Whatever method is implemented, the preservation of custom code in the custom stored procedures should be the main goal.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.