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

Version 3
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    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:

    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


     


    Article Number:

    000130061


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles