Oracle - change the type of a column without shuffling column ids around-CodePudding

I have a simple Oracle table with data already in it:

CRATE        NUMBER(10)                  
2V5          FLOAT(126)                  
tstamp       TIMESTAMP(6)¨

I am trying to convert it to this, including the data inside:

CRATE        NUMBER(10)                  
2V5          FLOAT(126)                  
tstamp       TIMESTAMP(6) WITH TIME ZONE

I have managed to do this with the following set of commands:

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" ADD( "temp_ts" TIMESTAMP WITH TIME ZONE );

UPDATE "TIMESTAMP_DEBUG_TABLE" SET "temp_ts" = "tstamp" AT TIME ZONE 'Europe/Zurich';

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" DROP COLUMN "tstamp";

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" RENAME COLUMN "temp_ts" TO "tstamp";

However, there is a problem. Every column has a column id in oracle, which is obtained by running: SELECT column_id FROM USER_TAB_COLUMNS WHERE table_name = 'A' AND column_name = 'B';

I use this column id in my software, and actually have another table in my database that considers it a sort of a foreign key. By running the above set of commands, the column ids become shuffled around and the referential integrity is destroyed.

How can I solve this problem?

Is there any way to retype this column without shuffling the column ids of the table around?
Is there a way to formalize the relationship of the column id? Can I add a FK constraint to my table which points to the column ids of the other tables? Then I could cascade.
Any other ideas/workarounds?

CodePudding user response：

Relying on the column_id in the first place seems a bit problematic. It would seem to make more sense to rely on the column name since that's what every other SQL statement in the world effectively uses as the key. But I assume that changing that is a non-starter.

It's more expensive in terms of I/O and time but you could create a new table, move the data over, and then rename it. This should produce an end state where the two tables have the same column_id values (assuming that timestamp_debug_table currently hasn't had any columns added or removed previously that would cause the column_id values to be out of order)

create table timestamp_debug_table2 (
  crate number(10),
  "2V5" float(126),
  "tstamp" timestamp(6) with time zone
);

insert into timestamp_debug_table2
  select crate, "2V5", "tstamp" at time zone 'Europe/Zurich'
    from timestamp_debug_table;
    
drop table timestamp_debug_table;

alter table timestamp_debug_table2 rename to timestamp_debug_table;

Assuming you are on 12c or later, you could also make the column invisible and visible again before dropping it which also changes the column_id values. This is more reason not to depend on something like the column_id because Oracle is free to monkey around with it in order to implement other bits of functionality. No one would be likely to guess that doing something like making a column invisible and visible again would cause your software to stop working correctly.

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" ADD( "temp_ts" TIMESTAMP WITH TIME ZONE );

UPDATE "TIMESTAMP_DEBUG_TABLE" SET "temp_ts" = "tstamp" AT TIME ZONE 'Europe/Zurich';

-- This causes the column_id in user_tab_columns to be set to NULL and "temp_ts"
-- will have a value of 3
alter table TIMESTAMP_DEBUG_TABLE
  column "tstamp" invisible;

-- Now the column_id in user_tab_columns will be 4 for "tstamp".  "temp_ts" will
-- still have a value of 3
alter table TIMESTAMP_DEBUG_TABLE
  column "tstamp" visible;

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" DROP COLUMN "tstamp";

ALTER TABLE "TIMESTAMP_DEBUG_TABLE" RENAME COLUMN "temp_ts" TO "tstamp";