Convert SQL Server code to Oracle

Question

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

Add the version-specific SQL Server tag to your question.Your DDL is valid in SQL Server 2012 and later but not against older versions because of the CONCAT function. — Dan Guzman
– Dan Guzman, Commented Nov 2, 2017 at 16:41
This is not a code writing service. There are many differences between Oracle and sql server. identity being one that jumps off the page here. The syntax for computed columns is very different also. — Sean Lange
– Sean Lange, Commented Nov 2, 2017 at 16:42

Lukasz Szozda · Accepted Answer · 2017-11-02 16:55:55Z

1

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;

edited Nov 2, 2017 at 16:55

answered Nov 2, 2017 at 16:47

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert SQL Server code to Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related