CODEWITHSUNDEEP
sql-serveroracle-databaseoracle-sqldeveloper
Jhensen
Jhensen

Reputation: 81

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

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:

Upvotes: 0

Views: 269

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22467

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

Upvotes: 2

Max Zolotenko
Max Zolotenko

Reputation: 1132

This:

CONSTRAINT PK_RDVE.AspNetUsers ,PRIMARY KEY( Id )

Replace on this:

CONSTRAINT PK_RDVE_AspNetUsers PRIMARY KEY( Id )

And remove set options.

Upvotes: 1

Related Questions