Compilation Error ORA-00932 When Upgrading Common Components from 9.1 SP2 to 9.1 SP5 CP1

Products

• CA Strong Authentication
• CA Advanced Authentication
• CA Advanced Authentication - Risk Authentication (RiskMinder / RiskFort)
• CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort)
• CA Risk Authentication

Issue/Introduction

When upgrading Symantec Advanced Authentication Common Components from version 9.1 SP2 to 9.1 SP5 CP1 using the provided upgrade script, the procedure ARUDSORG_DELETE_SP fails to compile with Oracle error ORA-00932: inconsistent datatypes: expected DATE got NUMBER.

When running the upgrade script db-config-for-common-9.x-to-9.1.5.1.sql, the execution completes with a compilation warning:

SQL> @db-config-for-common-9.x-to-9.1.5.sql

Warning: Procedure created with compilation errors.

Reviewing the stored procedure ARUDSORG_DELETE_SP reveals the following compilation error:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

The error is triggered by the following statement within the procedure:

INSERT INTO arudsuser_archive SELECT * FROM arudsuser WHERE orgname = ORG;

Environment

• Product: Symantec Advanced Authentication (AdvAuth)
• Component: Common Components
• Affected Versions: 9.1 SP2 upgrading to 9.1 SP5 CP1 (9.1.5.1)
• Platform: Oracle Database

Cause

The upgrade script introduces a column datatype mismatch between the arudsuser source table and the arudsuser_archive target table. A column that is defined as DATE in arudsuser_archive corresponds to a NUMBER typed column in arudsuser (or vice versa), causing Oracle to reject the wildcard SELECT * insert with the ORA-00932 inconsistent datatypes error.

Resolution

Apply the following hotfix patch, which corrects the datatype inconsistency introduced by the upgrade script:

Patch: Symantec-AdvAuth-9.1.5.1-DE662049-HotFix

Steps

1. Download the patch Symantec-AdvAuth-9.1.5.1-DE662049-HotFix from the Broadcom Support KB article or the Broadcom Support Portal.
2. Follow the patch installation instructions included with the hotfix package.
3. After applying the patch, re-run the upgrade script or re-compile the ARUDSORG_DELETE_SP stored procedure to confirm the compilation error is resolved.
4. Verify that the procedure compiles successfully with no errors:

SELECT object_name, status

FROM user_objects

WHERE object_name = 'ARUDSORG_DELETE_SP';

Expected result: STATUS = VALID

---

Additional Information

• If you encounter this issue and the hotfix is not yet available in your environment, as a temporary workaround you can manually inspect and align the column datatypes between arudsuser and arudsuser_archive before re-running the upgrade script. However, the official hotfix should be applied to ensure full supportability.
• The patch download link is available within the associated KB article.