USM_SP_ARCHIVE_DATA Generates ORA-12704 : character set mismatch Error

Products

• CA Service Catalog

Issue/Introduction

When running the USM_SP_ARCHIVE_DATA stored procedure to archive older data, the following error may be generated:

Connecting to the database <DB_NAME>.
ORA-12704: character set mismatch
ORA-06512: at "MDBADMIN.USM_SP_ARCHIVE_DATA", line 23
ORA-06512: at "MDBADMIN.USM_SP_REQUEST_DATA_ARCHIVE", line 394
ORA-06512: at "MDBADMIN.USM_SP_REQUEST_DATA_ARCHIVE", line 59
ORA-06512: at "MDBADMIN.USM_SP_ARCHIVE_DATA", line 9
ORA-06512: at line 10
INFO:started archiving request object
INFO:Total no of request found:71
Error -12704: ORA-12704: character set mismatch
Error -12704: ORA-12704: character set mismatch
Process exited.
Disconnecting from the database <DB_NAME>.

Example of the procedure:

DECLARE
P_OBJECT_TYPE VARCHAR2(200);
P_DATE DATE;
P_BU VARCHAR2(200);
BEGIN
P_OBJECT_TYPE := 'request';
P_DATE := '31-DEC-2014';
P_BU := 'CA';

USM_SP_ARCHIVE_DATA(
P_OBJECT_TYPE => P_OBJECT_TYPE,
P_DATE => P_DATE,
P_BU => P_BU
);
END;

Environment

All versions of Service Catalog

Oracle Database

Cause

The order of the columns is different between usm_request_values and usm2request_values

This must match for the procedure to run as intended. 

Resolution

We must ensure the columns are listed in the same order for both the usm_request_values and usm2request_values tables.

Note: Perform the steps below after taking a full backup of the mdb schema and after ensuring the USM2REQUEST_VALUE table is empty.

1. Log as mdbadmin to the database instance

2. Run

DROP TABLE MDBADMIN.USM2REQUEST_VALUE;
COMMIT;

3. Create the table with the order of the usm_request_value:

CREATE TABLE MDBADMIN.USM2REQUEST_VALUE
(
REQUEST_ID NUMBER NOT NULL,
NAME NVARCHAR2 (50) NOT NULL,
TYPE NUMBER,
DATA_TYPE NUMBER,
TEXT_1 NVARCHAR2 (64),
VALUE NCLOB
)
TABLESPACE MDB_DATA
STORAGE (BUFFER_POOL DEFAULT);

4. Run the folllowing and confirm the order is the same:

SELECT * FROM USM2REQUEST_VALUE;

SELECT * FROM USM_REQUEST_VALUE;

Additional Information

Archive and Purge Historical Data