Re: Adding columns AFTER a column of type LONG

In article <3339599A.6F5E_at_medicalert.org>, Tim Witort <trw_at_medicalert.org> wrote:
>There's a related post to this, but my question is slightly
>different. I have noticed some odd behavior on tables where
>there is a column of type LONG as the last column, then an
>ALTER TABLE is performed to add a column after the LONG-type
>column. Is this no-no documented anywhere? What is Oracle's
>problem with this? If the solution is to export the table
>and import it with the columns back in the right order, I'm
>prepared to do it if I know it will restore normal behavior
>to the table.

I believe that oracle tables can only contain one long column, and I think I have read that it is always the last column in a row. So if you add another column, Oracle would have to reorganize each row in the table where the long column was not null, which it probably doesn't.

I'm surprised that the dbms doesn't complain when you try to add a new column to a table that already has data in it. Maybe they figured you knew what you were doing.... I would imagine that each row where you have a non-null value in the long column, you have trouble storing/retrieving any values for your new column.

Just create a new table from the old, but I would certainly check a few of the rows where you were having trouble. The data probably will not come across correctly.

Good luck,
Steve Cosner Received on Thu Mar 27 1997 - 00:00:00 CST