ORA-12704 : character set mismatch when running USM_SP_ARCHIVE_DATA

Document ID : KB000073530
Last Modified Date : 05/06/2018
Show Technical Document Details
Issue:
When running the USM_SP_ARCHIVE_DATA we get a:

ORA-12704 : character set mismatch when running

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:
Service Catalog 12.9
Service Catalog 14.1
Cause:
The order of the columns is different between usm_request_values and usm2request_values
Resolution:
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: