CODEWITHSUNDEEP
sqloracle-databaseuuid
MikiNK
MikiNK

Reputation: 41

UUID data type in Oracle

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

Upvotes: 4

Views: 8860

Answers (1)

MT0
MT0

Reputation: 168096

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;

Upvotes: 8

Related Questions