November 21, 2012 at 12:28 pm
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.
November 21, 2012 at 12:37 pm
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
November 21, 2012 at 12:42 pm
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.
November 21, 2012 at 12:43 pm
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,
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
November 21, 2012 at 12:46 pm
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,
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply