SQL Server to Oracle DB - Use a SQL Server create table script in Oracle DB

Question

I'm using the Oracle SQL Developer Editor Scratch to translate the scripts, but it's not working.

This is my original script create from SQL Server:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [RDVE].[AspNetUsers]
(
    [Id] [NVARCHAR](128) NOT NULL,
    [Email] [NVARCHAR](256) NULL,
    [EmailConfirmed] [BIT] NOT NULL,
    [PasswordHash] [NVARCHAR](MAX) NULL,
    [SecurityStamp] [NVARCHAR](MAX) NULL,
    [PhoneNumber] [NVARCHAR](MAX) NULL,
    [PhoneNumberConfirmed] [BIT] NOT NULL,
    [TwoFactorEnabled] [BIT] NOT NULL,
    [LockoutEndDateUtc] [DATETIME] NULL,
    [LockoutEnabled] [BIT] NOT NULL,
    [AccessFailedCount] [INT] NOT NULL,
    [UserName] [NVARCHAR](256) NOT NULL,
    [Setor] [NVARCHAR](50) NULL,

    CONSTRAINT [PK_dbo.AspNetUsers] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And This is the script generated by the oracle sql developer migration tool.

ALTER SESSION SET CURRENT_SCHEMA=RDVE;

/*TODO:SQLDEV*/ SET ANSI_NULLS ON /*END:SQLDEV*/

/*TODO:SQLDEV*/ SET QUOTED_IDENTIFIER ON /*END:SQLDEV*/

CREATE TABLE AspNetUsers
(
  Id NVARCHAR2(128) NOT NULL,
  Email NVARCHAR2(256) ,
  EmailConfirmed NUMBER(1,0) NOT NULL,
  PasswordHash NVARCHAR2(2000) ,
  SecurityStamp NVARCHAR2(2000) ,
  PhoneNumber NVARCHAR2(2000) ,
  PhoneNumberConfirmed NUMBER(1,0) NOT NULL,
  TwoFactorEnabled NUMBER(1,0) NOT NULL,
  LockoutEndDateUtc DATE ,
  LockoutEnabled NUMBER(1,0) NOT NULL,
  AccessFailedCount NUMBER(10,0) NOT NULL,
  UserName NVARCHAR2(256) NOT NULL,
  Setor NVARCHAR2(50) ,
  CONSTRAINT PK_RDVE.AspNetUsers ,PRIMARY KEY( Id )
);

Thats the output :

SP2-0158: Start of unknown SET option "ansi_nulls ..."
SP2-0158: Start of unknown SET option "quoted_ide ..."
Error from line: 7 on command -

If a remove the set options I get the following error

Error from line: 4 on command -
Relatório de erros -
ORA-00904: : identificador inválido
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Who has a 256-character email address? (Unicode I can understand.) — William Robertson
– William Robertson, Commented Nov 26, 2019 at 22:30
@WilliamRobertson yep, i know that, is still a development database, when put into production, the size of the fields will be adjusted — Jhensen
– Jhensen, Commented Nov 27, 2019 at 12:13

thatjeffsmith · Accepted Answer · 2019-11-26 19:49:06Z

2

/*TODO:SQLDEV*/ SET ANSI_NULLS ON /*END:SQLDEV*/

/*TODO:SQLDEV*/ SET QUOTED_IDENTIFIER ON /*END:SQLDEV*/

TODO - means our translator doesn't know what to do with this line of code. YOU need to figure out what if anything it needs to be changed to. Of course, it won't run as is.

answered Nov 26, 2019 at 19:49

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

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

Comments

Max Zolotenko · Accepted Answer · 2019-11-26 19:42:16Z

1

This:

CONSTRAINT PK_RDVE.AspNetUsers ,PRIMARY KEY( Id )

Replace on this:

CONSTRAINT PK_RDVE_AspNetUsers PRIMARY KEY( Id )

And remove set options.

answered Nov 26, 2019 at 19:42

Max Zolotenko

1,1427 silver badges15 bronze badges

1 Comment

Jhensen Over a year ago

Thx, the "dot" before the AspNetUsers and the comma, was the error, problem resolved.

Collectives™ on Stack Overflow

SQL Server to Oracle DB - Use a SQL Server create table script in Oracle DB

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related