Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Oracle DDL statments to SQL Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 12:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 399, Visits: 2,455
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.
Post #1387581
Posted Wednesday, November 21, 2012 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1387583
Posted Wednesday, November 21, 2012 12:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 399, Visits: 2,455
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.
Post #1387586
Posted Wednesday, November 21, 2012 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1387587
Posted Wednesday, November 21, 2012 12:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 399, Visits: 2,455
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.
Post #1387590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse