During an upgrade of CA Service Desk Manager, the following error is received:
Update sql failed:
select COUNT(COLUMN_NAME) into RESPONSE442303553 from user_tab_columns where table_name='RESPONSE' and column_name='RESPONSE';
IF RESPONSE442303553 > 0
EXECUTE IMMEDIATE 'ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB';
ERROR at line 1:
ORA-22858: invalid alteration of datatype
ORA-06512: at line 7
The Response.XML for the MDB update is not correct.
Oracle does not allow the 'ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB'
Run these SQL commands against the MDB database as the 'mdbadmin' user:
ALTER TABLE RESPONSE ADD RESPONSE2 NVARCHAR2(2000);
UPDATE RESPONSE SET RESPONSE2 = RESPONSE;
UPDATE RESPONSE SET RESPONSE = null;
ALTER TABLE RESPONSE MODIFY RESPONSE LONG
ALTER TABLE RESPONSE MODIFY RESPONSE NCLOB
UPDATE RESPONSE SET RESPONSE = RESPONSE2;
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:
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.