2 Replies Latest reply: Dec 11, 2010 1:05 PM by Pedro Lourenço RSS

    ORA-01438 caused by a change in field precision on versioned table

    Pedro Lourenço
      Hello,

      I've changed the percision of a column that i have in a versioned table.
      Don't know if it's relevant, but the field i'm talking about is the primary key of the table.
      I changed from number(5) to number(10) (using _LTS inside beginddl, commitdll) and everything worked as expected.. no errors.

      However, i'm having a problem merging rows created in that table: When i try to merge, i get the error:
      "ORA-01438: value larger than specified precision allowed for this column".

      The value i'm trying to merge has just 6 digits and the _LT table has the correct percision - 10.
      Trying to find another explanation, i found that the _AUX table continues with the old precision (5).
      Is this the cause of the problem? If so, what can i do to solve it?

      If anyone can help, I appreciate.
      Regards,

      Pedro Lourenço
        • 1. Re: ORA-01438 caused by a change in field precision on versioned table
          Ben Speckhard-Oracle
          Hi,

          The problem most likely does lie within the AUX table definition.  The primary key columns are not meant to be modifiable, but it appears that changing the precision on a number column gets applied to the LT table. Will continue looking at this, but I would recommend filing a SR if you need an immediate fix.

          Regards,
          Ben
          • 2. Re: ORA-01438 caused by a change in field precision on versioned table
            Pedro Lourenço
            Hello,

            Yes, after some experiencies, namely after trying to drop the primary key, i got the error:

            WM_ERROR_199 primary key columns cannot be
            added/dropped/*modified*/reordered for version enabled tables

            ... and i found out that primary keys are not meant to be modifiable.
            I solved my problem making a workspace-level export of the table, disableVersioning, alter column, enableVersioning and import.
            Don't know if it was the best solution, but the problem was solved.

            It's maybe relevant adding:

            - protection (raise exception) when trying to alter primary key columns.
            - more info in the documentation. I read Oracle® Database Workspace Manager Developer's Guide 11g Release 2 (11.2) and i couldn't find anything about this limitation (just in Workspace Manager Error Messages - WM_ERROR_199). Maybe add something in section 1.8 or 4.beginDDL.

            Thank you for your help.
            Best regards,

            Pedro Lourenço