UUID data type in Oracle

Question

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???

Answer 1

Oracle does not have a UUID 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;