'ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB' fails on Oracle during MDB upgrade from CA Service Desk 12.6

Document ID : KB000006765
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

During an upgrade of CA Service Desk Manager, the following error is received:

Update sql failed:

DECLARE
RESPONSE442303553 number; 
BEGIN 
select COUNT(COLUMN_NAME) into RESPONSE442303553 from user_tab_columns where table_name='RESPONSE' and column_name='RESPONSE';
IF RESPONSE442303553 > 0
THEN
EXECUTE IMMEDIATE 'ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB';
END IF; 
END;

ERROR at line 1:
ORA-22858: invalid alteration of datatype
ORA-06512: at line 7

Environment:
Upgrade CA Service Desk Manager 12.6 using an MDB on Oracle DBMS to a later version of CA Service Desk Manager.
Cause:

The Response.XML for the MDB update is not correct.

Oracle does not allow the 'ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB'

Resolution:

Run these SQL commands against the MDB database as the 'mdbadmin' user:


ALTER TABLE RESPONSE ADD RESPONSE2 NVARCHAR2(2000);
UPDATE RESPONSE SET RESPONSE2 = RESPONSE;
COMMIT;
UPDATE RESPONSE SET RESPONSE = null;
COMMIT;
ALTER TABLE RESPONSE MODIFY RESPONSE LONG
ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB
UPDATE RESPONSE SET RESPONSE = RESPONSE2;
COMMIT;
ALTER TABLE RESPONSE DROP COLUMN RESPONSE2;

The effect is that the original data in the 'RESPONSE' columm of the 'RESPONSE' table is maintained and the data type of the column is changed from NVARCHAR2 with a length of 1000 to NCLOB.


Next, update the mdb_schema_information table to prevent the installation of the mdb upgrade/update from re-trying the same data type modification.  To do that, first confirm that the "mdb_schema_information" table in the MDB includes 1 row for which the value of the FileName column is 'Response.xml'.  For that row, you would need to update the value of the FileTimestamp column to match the timestamp in the response.xml in the mdb package that is being applied. 


The response.xml that is being processed should contain the following timestamp:
"2012-04-10T17:54:12+0000"

And so, the second step for resolving the problem is to manually update the FileTimestamp field for the row to match that value.


After making the change to the mdb_schema_information table, whenever you subsequently upgrade of the mdb, the upgrade process would not attempt to re-run the problematic ALTER TABLE command.