Remedy - Server - Error:"ORA-01430: column being added already exists in table" trying to add a new field to a form

Knowledge Article

Article Number

000276866

Old Article Number

000130061

Article Type

Solutions to a Product Problem

Title

Remedy - Server - Error:"ORA-01430: column being added already exists in table" trying to add a new field to a form

Summary

Error:"ORA-01430: column being added already exists in table" trying to add a new field to a form

Product

Remedy AR System Server

Component

AR System

Applies to

Remedy AR System Server

Problem

Having a custom form and when trying to add a new field and saving the form the error is displayed:
...........
Failure during SQL operation to the database ORA-01430: column being added already exists in table
...........
The error is also observed if the field ID is copied from a different working environment and replacing it in the "affected" field stating the field ID is already in use.

Cause

The NumFields value in Arschema does not match with the existing amount of fields in the Field table for the affected form.

Solution

This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

The numfield count for the affected form needs to be checked and updated.

Connect to the Database and perform the following steps:

1- Run the query:
select schemaid from arschema where name = '{FORM NAME}';

Example:
select schemaid from arschema where name = 'CID:Ticket';
Output: 627

2- Write down the schemaid resulted from the above query and proceed to run the following query:
select count(*) from field where schemaid = '{OUTPUT OF 1ST COMMAND}';

Example:
select count(*) from field where schemaid = '627';
Output: 429
Write down this value.

3- Then proceed to run the following query:
select numfields from arschema where schemaid = '<OUTPUT OF 1ST COMMAND>';

Example:
select numfields from arschema where schemaid = '627';
Output: 423
Write down this value.

Following the example above for a custom form called CID:Ticket
   a) select schemaid from arschema where name = 'CID:Ticket'; -----  Output: 627
   b) select count(*) from field where schemaid = '627'; -----  Output: 429
   c) select numfields from arschema where schemaid = '627'; -----  Output: 423

The above shows that number of fields referenced in the main arschema table (423) does not match with the actual amount of existing fields (429) for the form as listed in the field table.
The column numfields of the table arschema needs to be updated to match the fields count of from the field table for the form in question.

4- For this run the following query:
update arschema set numfields = {output of the 2nd query} where schemaid = '{output of the 1st query}';
commit;

Example:
update arschema set numfields = 429 where schemaid = '627';
commit;

5- Restart the AR System Server application service to take this into effect.

NOTE: This article is also available in Spanish, use the link below or search by Article Number 000165065.
NOTA: Este artículo también está disponible en Español, usar el siguiente enlace o buscar por el Número de Artículo 000165065.
ORA-01430: La columna que se está agregando ya existe en la tabla.

Related article(s):
Remedy - Server - The form definition file field count does not match number of fields in the file.(ARERR 403) or The form definition field count does not match the number of fields in the database (ARERR 9912)
Remedy - Server - Error while importing the def file: ERROR *** ORA-01430: column being added already exists in table

Attachment(s):