UUID data type in Oracle-CodePudding

I need to change specification of the following table:

CREATE TABLE STAGE.STG_AAA_PROFILES
(
  SUBSCRIBER         INTEGER,
  USERNAME           VARCHAR2(50 BYTE),
  GROUP_ID           INTEGER,
  PROFILE_ID         INTEGER,
  STATUS             INTEGER,
  PASSWORD_TYPE      INTEGER,
  EXPIRATION         DATE
)

I have to make it look like:

CREATE TABLE STAGE.STG_AAA_PROFILES
(
  SUBSCRIBER         UID,
  USERNAME           VARCHAR2(50 BYTE),
  GROUP_ID           INTEGER,
  PROFILE_ID         UID,
  STATUS             INTEGER,
  PASSWORD_TYPE      INTEGER,
  EXPIRATION         DATE
)

How to alter table to replace integer with UID data type???

CodePudding user response：

Oracle does not have a UID data type.

Either use:

VARCHAR2(36) to store the UUID as a formatted hexadecimal string (32 hexadecimal characters and 4 dashes); or
RAW(16) to store the UUID as 16 bytes (128 bits).

How to alter table to replace integer with UID data type?

ALTER TABLE STAGE.STG_AAA_PROFILES ADD subscriber_uuid VARCHAR2(36);

Then convert the existing subscriber column from integer to a UUID or generate UUIDs for the rows in the table. Finally:

ALTER TABLE STAGE.STG_AAA_PROFILES DROP COLUMN subscriber;
ALTER TABLE STAGE.STG_AAA_PROFILES RENAME COLUMN subscriber_uuid TO subscriber;

CodePudding user response：

Create a new column of the desired type and copy the current

Column data to the new type using the appropriate type
       constructor.