CODEWITHSUNDEEP
sqloracle-databasedatabase-migrationddl
user8875992
user8875992

Reputation:

Convert SQL Server code to Oracle

I've tried to convert SQL Server code to Oracle but failed.

Here is my code in SQL Server. Can anyone help?

CREATE TABLE [dbo].[DimCustomer]
(
     CustomerID INT IDENTITY(12,1) NOT NULL,
     CustomerAccountID VARCHAR(20),
     FirstName VARCHAR(50),
     LastName VARCHAR(50),
     FullName AS CONCAT(FirstName,' ',LastName),
     ContactNumber VARCHAR(20),
     EmailAddress VARCHAR(50),
     MemberShipType VARCHAR(50),
     MemberShipStartDatekey INT,
     MemberShipExpiryDateKey INT,

     CONSTRAINT pk_customer_id PRIMARY KEY (CustomerID)
)

Upvotes: 1

Views: 301

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

As simple as (Oracle 12c):

CREATE TABLE DimCustomer
(
CustomerID INT   GENERATED BY DEFAULT AS IDENTITY NOT NULL  --IDENTITY
,CustomerAccountID VARCHAR2(20)                             --VARCHAR2
,FirstName VARCHAR2(50)
,LastName VARCHAR2(50)
,FullName AS (FirstName || ' ' ||LastName)                  --calculated column
,ContactNumber VARCHAR2(20)
,EmailAddress VARCHAR2(50)
,MemberShipType VARCHAR2(50)
,MemeberShipStartDatekey INT
,MemberShipExpiryDateKey INT
,CONSTRAINT pk_customer_id PRIMARY KEY (CustomerID)         --comma
);

Alternative using sequence and trigger:

CREATE SEQUENCE DimCustomer_seq START WITH 12 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER DimCustomer_seq_tr
 BEFORE INSERT ON DimCustomer FOR EACH ROW
BEGIN
 SELECT DimCustomer_seq.NEXTVAL INTO :NEW.CustomerID FROM DUAL;
END;

Upvotes: 1

Related Questions