SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Oracle DDL statments to SQL


Converting Oracle DDL statments to SQL

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 3075
I need to create a sql database which works the same way in oracle....

Basically, I have to mimic a database from Oracle to SQL server.

I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.

Thanks,
SueTons.

Regards,
SQLisAwe5oMe.
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75247 Visits: 40985
SQLCrazyCertified (11/21/2012)
I need to create a sql database which works the same way in oracle....

Basically, I have to mimic a database from Oracle to SQL server.

I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.

Thanks,
SueTons.


you'll have to be a LOT more specific. do you want to take an existing Oracle database,and create it's equivalent in SQL server?
There's a tool for that: SQL Server Migration Assistant for Oracle:
www.microsoft.com/en-us/download/details.aspx?id=28766

That will do the tables and views for you,creating the tables with teh best-matching data type equivalents.

once it's in SQL, it's trivial to script out the objects and data.

for all packages, procedures and functions, you will have to convert them manually...it's a +BIG+ job. some things have no equivilents, so you have to create functionalities that mimic whatever was being done instead.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 3075
Lowell (11/21/2012)
SQLCrazyCertified (11/21/2012)
I need to create a sql database which works the same way in oracle....

Basically, I have to mimic a database from Oracle to SQL server.

I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.

Thanks,
SueTons.


you'll have to be a LOT more specific. do you want to take an existing Oracle database,and create it's equivalent in SQL server?
There's a tool for that: SQL Server Migration Assistant for Oracle:
www.microsoft.com/en-us/download/details.aspx?id=28766

That will do the tables and views for you,creating the tables with teh best-matching data type equivalents.

once it's in SQL, it's trivial to script out the objects and data.

for all packages, procedures and functions, you will have to convert them manually...it's a +BIG+ job. some things have no equivilents, so you have to create functionalities that mimic whatever was being done instead.



Yes, Ttis is exactly what I am looking for. Thanks. Lowell

Regards,
SQLisAwe5oMe.
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75247 Visits: 40985
if you want to do it yourself, which is another big job let me tell you, this data type equivalents link can help:
http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm

I've done it the other direction: take a single table in SQL server for example, and generate the equivilent DDL command for Oracle, but that's based on personal assumptions and naming conventions for Sequences/triggers.

here's one example of my procs results:


CREATE TABLE [dbo].[TBCOUNTY] (
[COUNTYTBLKEY] INT IDENTITY(1,1) NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATE] VARCHAR(2) NOT NULL,
[DESCRIP] VARCHAR(30) NOT NULL,
[CODE] VARCHAR(3) NULL,
[STATETBLKEY] INT NULL,
[REGIONTBLKEY] INT NULL,
[EDREGIONTBLKEY] INT NULL,
[SPECDISTTBLKEY] INT NULL,
CONSTRAINT [PK__TBCOUNTY__009508B4] PRIMARY KEY CLUSTERED (COUNTYTBLKEY),
CONSTRAINT [FK__TBCOUNTY__INDEXT__68C86C1B] FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),
CONSTRAINT [FK__TBCOUNTY__STATET__0A5513C6] FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),
CONSTRAINT [FK__TBCOUNTY__REGION__3B6D4C6D] FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),
CONSTRAINT [FK__TBCOUNTY__EDREGI__3C6170A6] FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),
CONSTRAINT [FK_TBSPECDIST_SPECDISTTBLKEY] FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY))




--Oracle equivalent:

CREATE TABLE CHANGE_ME.TBCOUNTY (
COUNTYTBLKEY NUMBER(10) NOT NULL,
INDEXTBLKEY NUMBER(10) NOT NULL,
STATE VARCHAR2(2) NOT NULL,
DESCRIP VARCHAR2(30) NOT NULL,
CODE VARCHAR2(3) NULL,
STATETBLKEY NUMBER(10) NULL,
REGIONTBLKEY NUMBER(10) NULL,
EDREGIONTBLKEY NUMBER(10) NULL,
SPECDISTTBLKEY NUMBER(10) NULL,
CONSTRAINT PK__TBCOUNTY__009508B4 PRIMARY KEY (COUNTYTBLKEY),
CONSTRAINT FK__TBCOUNTY__INDEXT__68C86C1B FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),
CONSTRAINT FK__TBCOUNTY__STATET__0A5513C6 FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),
CONSTRAINT FK__TBCOUNTY__REGION__3B6D4C6D FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),
CONSTRAINT FK__TBCOUNTY__EDREGI__3C6170A6 FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),
CONSTRAINT FK_TBSPECDIST_SPECDISTTBLKEY FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY));

--CHANGE_ME.TBCOUNTY_SEQUENCE
CREATE SEQUENCE CHANGE_ME.TBCOUNTY_SEQUENCE
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

--CHANGE_ME.TBCOUNTY_IDENTITY
--
CREATE OR REPLACE TRIGGER CHANGE_ME.TBCOUNTY_IDENTITY BEFORE INSERT ON CHANGE_ME.TBCOUNTY FOR EACH ROW
WHEN (
NEW.COUNTYTBLKEY IS NULL
)
DECLARE DUMMY NUMBER;
BEGIN
SELECT TBCOUNTY_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL;
:NEW.COUNTYTBLKEY:= DUMMY;
END;
/




Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
SQLisAwE5OmE
SQLisAwE5OmE
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 3075
Lowell (11/21/2012)
if you want to do it yourself, which is another big job let me tell you, this data type equivalents link can help:
http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm

I've done it the other direction: take a single table in SQL server for example, and generate the equivilent DDL command for Oracle, but that's based on personal assumptions and naming conventions for Sequences/triggers.

here's one example of my procs results:


CREATE TABLE [dbo].[TBCOUNTY] (
[COUNTYTBLKEY] INT IDENTITY(1,1) NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATE] VARCHAR(2) NOT NULL,
[DESCRIP] VARCHAR(30) NOT NULL,
[CODE] VARCHAR(3) NULL,
[STATETBLKEY] INT NULL,
[REGIONTBLKEY] INT NULL,
[EDREGIONTBLKEY] INT NULL,
[SPECDISTTBLKEY] INT NULL,
CONSTRAINT [PK__TBCOUNTY__009508B4] PRIMARY KEY CLUSTERED (COUNTYTBLKEY),
CONSTRAINT [FK__TBCOUNTY__INDEXT__68C86C1B] FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),
CONSTRAINT [FK__TBCOUNTY__STATET__0A5513C6] FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),
CONSTRAINT [FK__TBCOUNTY__REGION__3B6D4C6D] FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),
CONSTRAINT [FK__TBCOUNTY__EDREGI__3C6170A6] FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),
CONSTRAINT [FK_TBSPECDIST_SPECDISTTBLKEY] FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY))




--Oracle equivalent:

CREATE TABLE CHANGE_ME.TBCOUNTY (
COUNTYTBLKEY NUMBER(10) NOT NULL,
INDEXTBLKEY NUMBER(10) NOT NULL,
STATE VARCHAR2(2) NOT NULL,
DESCRIP VARCHAR2(30) NOT NULL,
CODE VARCHAR2(3) NULL,
STATETBLKEY NUMBER(10) NULL,
REGIONTBLKEY NUMBER(10) NULL,
EDREGIONTBLKEY NUMBER(10) NULL,
SPECDISTTBLKEY NUMBER(10) NULL,
CONSTRAINT PK__TBCOUNTY__009508B4 PRIMARY KEY (COUNTYTBLKEY),
CONSTRAINT FK__TBCOUNTY__INDEXT__68C86C1B FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),
CONSTRAINT FK__TBCOUNTY__STATET__0A5513C6 FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),
CONSTRAINT FK__TBCOUNTY__REGION__3B6D4C6D FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),
CONSTRAINT FK__TBCOUNTY__EDREGI__3C6170A6 FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),
CONSTRAINT FK_TBSPECDIST_SPECDISTTBLKEY FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY));

--CHANGE_ME.TBCOUNTY_SEQUENCE
CREATE SEQUENCE CHANGE_ME.TBCOUNTY_SEQUENCE
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

--CHANGE_ME.TBCOUNTY_IDENTITY
--
CREATE OR REPLACE TRIGGER CHANGE_ME.TBCOUNTY_IDENTITY BEFORE INSERT ON CHANGE_ME.TBCOUNTY FOR EACH ROW
WHEN (
NEW.COUNTYTBLKEY IS NULL
)
DECLARE DUMMY NUMBER;
BEGIN
SELECT TBCOUNTY_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL;
:NEW.COUNTYTBLKEY:= DUMMY;
END;
/





Thanks Lowell for your quick reply.

SueTons.

Regards,
SQLisAwe5oMe.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search