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

0
votes

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:

2
sql-serveroracleoracle-sqldeveloper
stackoverflow.com/questions/41781/… – Hiten004
Who has a 256-character email address? (Unicode I can understand.) – William Robertson
@WilliamRobertson yep, i know that, is still a development database, when put into production, the size of the fields will be adjusted – Jhensen

2 Answers

2
votes
/*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.

1
votes

This:

CONSTRAINT PK_RDVE.AspNetUsers ,PRIMARY KEY( Id )

Replace on this:

CONSTRAINT PK_RDVE_AspNetUsers PRIMARY KEY( Id )

And remove set options.